开发者

Sorting by SUM very slow

I have the following query:

  SELECT SUM(s.count) as count, a.name, s.author_id as id
    FROM twitter_author_daily_stats s
    JOIN twitter_author a ON s.author_id = a.id
   WHERE s.`date` >= '2011-01-07'
     AND s.`date` <= '2011-09-21'
     AND s.profile_twitter_search_id IN (263)
GROUP BY s.author_id
   LIMIT 30;

It uses an index (author_i开发者_Go百科d, profile_twitter_search_id, date); it's fast (~1s); and it returns ~2500 rows.

However, when I add ORDER BY count, the query runs for minutes (I didn't bother waiting for it to finish).

Shouldn't it just take the ~2500 rows from original query and sort by count column? Why does it take so long?

Can someone who has better MySQL knowledge explain?


Even better: get MySQL to explain it, with the aptly-named EXPLAIN keyword.

Optimisations with indexes can only be performed in certain situations, and changing ordering/grouping/conditions is a good way to alter the landscape quite considerably.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜