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
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
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.