Query slow first time, then fast

MySQL & MariaDB Other MySQL® Questions
1

Hello )

I have this query:
SELECT SQL_CALC_FOUND_ROWS
m.subject,
c.club_id,
c.name,
ml.link_id,
ml.status, ml.to
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.from
WHERE
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?

2

Could you please provide us with the results of EXPLAIN command for your query?

3

Here it is:

1 SIMPLE ml ref to to 3 const 1219 Using where; Using temporary; Using filesort1 SIMPLE c eq_ref PRIMARY PRIMARY 3 vman.ml.from 1 1 SIMPLE m eq_ref PRIMARY PRIMARY 3 vman.ml.mail_id 1

4
  1. Try to use ml.mail_id in order by clause
  2. Add key (to, directory, mail_id) to mail_links table.
5

Ony typical mistake about SQL_NO_CACHE is thinking it fixes all caches in fact it does not. It only bypasses query cache and there is innodb buffer pool OS caches etc.

6

… I got it working - thank you! )