Re: Speed of query on tables containing blob depends on filesystem cache

[Rick James]

Please provide
SELECT COUNT(*) FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 100;
SELECT COUNT(*) FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
SHOW TABLE STATUS LIKE 'completed_tests'\G
SHOW VARIABLES LIKE '%buffer%';
How much RAM do you have?

Consider moving the deleted and failed rows to another table.

Did you change the caches when you changed the engine? See
http://mysql.rjweb.org/doc.php/memory

`failed` and `deleted` are not in the CREATE TABLE you provided.

Do you have other SELECTs you would like to optimize at the same time? (Speeding up one may mess up another.)

Learn about "compound indexes", and add
INDEX(status, failed, score)

Consider compressing the blob.

> without removing the blob column from the table
So, you don't want me to suggest "vertical partitioning".

In MyISAM, especially, your table scan has to step over 117996 cow paddies; this takes time.