Very Simple "SELECT * FROM" Times Out (InnoDB)
First of all, here are a few things you can try to get more info about the query in your case:


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

SHOW FULL PROCESSLIST

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 &lt; 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 &lt; 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 &lt; 500;




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


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
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';

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