Very Simple "SELECT * FROM" Times Out (InnoDB)

Question

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.

Answer 1

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

Answer 2

Don't hide an indexed column inside a function (LOWER). In this case, have the collation be anything with ..._ci, meaning case insensitive. Then you can say simply say WHERE image_name = 'img_0209.jpg' and have INDEX(image_name). Now you don't have to search the entire table for that one row.

What will you do with the BLOB after you fetch it? (There may be other optimizations there.) About how many rows in the table? (This may factor into the equation.)

Specific suggestions

ALTER TABLE `image`
    MODIFY COLUMN `image_name`
        varchar(80) CHARACTER SET ascii COLLATE ascii_general_ci
        DEFAULT NULL';

and remove the use of LOWER():

WHERE image_name = 'img_0209.jpg'

Your existing index can stay unchanged (except that it will be rebuilt to handle the change in collation):

 UNIQUE KEY `image_name` (`image_name`)

Caveat: The ALTER is likely to take a long time.

Answer 3

Don't select a longblob when your interface is a terminal. The bytes of eg. an image may contain characters that are control characters for your interface or connection.