Mysql – Very Simple “SELECT * FROM” Times Out (InnoDB)

mariadbMySQLperformancequery-performance

Hello everyone and sorry for what may be a silly question, but I'm completely dazzled.

First thing is that I'm not sure that it's related to InnoDB, but I've tried this on MariaDB and on MySQL and results are the same.

Let's cut to the chase:

I have huge performance issues in the most unpredictable places. To illustrate it best, here is an example:

I have a table called 'image' which stores images as BLOBs as well as the images metadata like image_name or DPI. It's relatively small (~14000 rows) and most images are 250-1000 KB. In the example I will use LOWER() to get rid of index that I have on image_name column.

SELECT id FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

This is super fast, at worst couple milliseconds, we got result id = 1.

SELECT * FROM image WHERE id = '1'

This is fast again, most time spent transferring BLOB to me, no surprise (in the end, it's PRIMARY key).

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

Now this thing is just refuses to execute. And I mean it. In 95% of the cases I will receive a timeout and occasionally it will execute after 10 minutes or so.

EXPLAIN SELECT obviously tells me that it's simple SELECT. And I cannot run SHOW PROFILES because the request is never completed, so I cannot even think of where I can start debugging it.

Best Answer

First of all, here are a few things you can try to get more info about the query in your case:

  1. After starting the query, you can check its status by running

    SHOW FULL PROCESSLIST

  2. Also, you can try running the query on a smaller set of rows by adding another where predicate (covered by an index), for example:

    SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg'

As for why the query may be slow, I believe that what happens with the third query

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

is that since the index can't be used, the engine does a full table scan and reads all the data for each row (including full BLOB data) and only then tries to filter them. So it probably reads a few GBs of data with that query.

Looking at the amount of data read from disc during the query may give some hints. You can try using the innodb_data_read service status variable:

SET @read_metric = 'innodb_data_read';
SET @read_before = (
  SELECT variable_value
  FROM information_schema.session_status
  WHERE variable_name = @read_metric
);

SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg';

SET @read_after = (
  SELECT variable_value
  FROM information_schema.session_status
  WHERE variable_name = @read_metric
);

SELECT (@read_after - @read_before) / 1024 / 1024;

It should be similar to the size of data in BLOBs:

SELECT sum(length(image_data)) / 1024 / 1024 FROM image WHERE id < 500;

To speed up the query you need to avoid accesing BLOBs as much as possible. A few suggestions:

  1. in your specific case changing the collation to an ignore-case one should help, so the image_name index may be used to find the single row you need

  2. try using a self join to separate image_name and BLOBs access

    SELECT image_data.image_data
FROM image
JOIN image as image_data on image_data.id = image.id
WHERE LOWER(image.image_name) = 'img_0209.jpg';

  3. as described in the Optimizing for BLOB Types manual, move the BLOBs to a separate table with just two colums: id and data

Related Question