I have this query:
FROM mails_links AS ml
JOIN mails AS m ON m.mail_id = ml.mail_id
LEFT JOIN clubs AS c ON c.club_id = ml.
to = ‘139986’ AND
ml.directory = ‘INBOX’
ORDER BY m.mail_id DESC
LIMIT 0, 20
The query runs in 1,2-1,5s first time it is run. After this the query runs in 0,01s even though SQL_NO_CACHE is used.
If I wait some time, say an hour, the query is slow again. I’m pretty sure a COUNT(*) on mail_links would be better (as Peter writes here), but in order to be sure I need to know why the above is slow en the first and the gets fast… and slow again.
My best guess is that the index data needed to process the query is purged out of memory once in a while. It is fast when the index data is in memory and slow when it is not. However, this explanation seems a little weird (but still plausible as a lot of tables are fighting for the memory) as the entire index in only around ~80-90mb.
Tables “mail_links”, “mails” are MyISAM and “clubs” are InnoDB. I’m running mysql 5.0.45 on debian.
What do you think? Why is it slow the first time?