GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Query slow first time, then fast

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query slow first time, then fast

    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?

    Comment


    • #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

      Comment


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

        Comment


        • #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.

          Comment


          • #6
            ... I got it working - thank you! )

            Comment

            Working...
            X