MySQL Forums
Forum List  »  Optimizer & Parser

Re: Slow Queries : Using Temporary , Using Filesort
Posted by: Rick James
Date: October 26, 2010 10:32PM

Query 1:

select  SQL_CALC_FOUND_ROWS id, keyword
    from  keyword
    where  keyword like 'p%'
      AND  count IN (2,3,4)
    ORDER BY  rate DESC, time DESC
    LIMIT  88800,6;

Temporary and Filesort are not your problems. The problem is you need to scan most (all?) of the table.

LIMIT 88800,6 -- That requires collecting 88800+6 rows, then tossing 88800 of them.
SQL_CALC_FOUND_ROWS requires going through the rest -- probably a total of 330510 rows.

FULLTEXT KEY `keyword` (`keyword_ft`) -- looks like a typo; you have no field called keyword_ft.

LIKE 'p%' -- This does NOT use FULLTEXT. But it did do a "range" scan of the words beginning with 'p';

Is this "pagination"? If so there are much better ways of doing it.

Also (if pagination), why do SQL_CALC_FOUND_ROWS every time? That is part of what is killing performance. No, I'll take that back "ORDER BY rate DESC, time DESC" forces you to look through all the 'p%' words anyway.

Options: ReplyQuote

Written By
Re: Slow Queries : Using Temporary , Using Filesort
October 26, 2010 10:32PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.