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

[Rick James]

> because the actual query is more complicated
Speeding up one query does not necessarily speed up another.

> | innodb_buffer_pool_size | 8388608 |
> | key_buffer_size | 16777216 |
These are critical to performance, read this and adjust accordingly:
http://mysql.rjweb.org/doc.php/memory

> Avg_row_length: 3880
You have a big BLOB and/or TEXT field. When scanning the data, it has to fetch that from disk, so it adds to the burden.

code tags in BBCode use [], not <>

SELECT  ct.archive, ct.status, ct.score, ct.users_LOGIN,
        t.lessons_ID, t.content_ID, t.keep_best
    FROM  completed_tests ct,tests t
    WHERE  ct.status != 'deleted'
      and  ct.status != 'incomplete'
      and  t.id=ct.tests_ID
      and  t.lessons_ID=10;

Oh, you are JOINing two tables. That opens up another set of issues that did not exist in your simplified SELECT.

t:
INDEX(lessions_ID)
ct:
INDEX(id) (unless `id` is already the PRIMARY KEY)

Please provide SHOW CREATE TABLE for both tables.

> even if I have remove the "users_LOGIN" column from the result set which is a varchar(255)
Removing something from the resultset does not help unless it makes it so all the fields in the SELECT are in a single INDEX.

Bottom line: Increase the appropriate cache size, add index(es) as indicated, time it, and let's discuss it again.