SELECT id, filename FROM pictures WHERE cat = 14 ORDER BY id DESC LIMIT 7938, 42;
begs for this compound index:
INDEX (cat, id)
Hmmm... In InnoDB, the PRIMARY KEY is effectively part of any secondary key, so this is equivalent to INDEX(cat).
This may do even better because it can be "Using index":
INDEX (cat, id, filename)
Note: Rows_examined: 27889
This means that it had to step over records id did not need.
This is awful: DESC LIMIT 7938, 42;
It has to step over 7938 rows before it can start getting the 42 you want.
Is this "pagination"? If so, you can make it _much_ faster by remembering where you left off (the last 'id'), and getting to the "Next" page by doing
SELECT id, filename FROM pictures
WHERE cat = 14
AND id < $left_off
ORDER BY id DESC
LIMIT 42;
If you don't have much need for the qc, turn it off:
query_cache_type = 0
SHOW TABLE STATUS LIKE 'pictures'\G
I'm curious about the size.
Is your 1GB swapping at all? If so, you should _shrink_ the buffer_pool (or other things) to avoid swapping. Is your web server Apache? If so, shrink MaxClients to avoid chewing up too much space there.
If you are not using MyISAM, change
key_buffer_size = 256M
to
key_buffer_size = 20M
thread_cache_size = 256
Change that to 3.
If you have other significant entries in the slowlog, let's see them -- queries do interfere with each other; perhaps the other ones are indirectly causing this one to be slow. (Example: but hogging the buffer_pool.)