Re: Horrible performance on BLOB's when using WHERE or ORDER BY

[Rick James]

There are lots of possible explanations...

If it is INT SIGNED, there are no such values. If it is INT UNSIGNED, such is possible.

Do you have an index on random?

SELECT without WHERE scan the table in the most convenient order (disk order of the data).

SELECT with a WHERE that can use an INDEX will bounce between the index (which may be cached int RAM) and the data. This is likely to involve random reads into the data file. And it is likely to fetch block(s) that contain the row, but waste the bandwidth for fetching other rows that are in the same blocks. (The blocks are cached, so it may not be a total waste.)

SELECT with a WHERE clause that is "random" will lead to random disk I/O. Disks tend to be faster when reading sequentially. (However, 40:1 seems extreme, so I don't think this is the whole answer.)

SELECT with a WHERE that cannot use an INDEX will be like SELECT without WHERE, except that it will read, then skip over, any rows that do not match the WHERE.

(That is a simplified discussion of WHEREs and INDEXes.)

There are many other things that could be going on; to get further analysis, please provide...
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

To make your program run faster (perhaps), do the UNCOMPRESS in your application. This would shrink the amount of data that needs to be transferred. (Perhaps this is insignificant if you are connecting via "localhost".)

Different engines use different techniques for BLOB -- After specifying the engine via SHOW CREATE TABLE, please specify which version of the "plugin" you are using if it is InnoDB.

If you have the $$, consider RAID with striping. That will give you more disk bandwidth. Or, with $$$$, SSD drives.

Another thing that could impact the answer -- after a row is inserted, is it updated? Is it deleted? MyISAM's handling of 'holes' could be a factor.