MySQL Forums
Forum List  »  Performance

Re: Long execution time despite LIMIT 3
Posted by: Rick James
Date: June 21, 2011 07:23AM

* The new query is not the same as the one you asked about. But you are well on your way to understanding the difference.

* How many rows have (m.thread = 817549.001266066) _without_ the rest of the WHERE? I ask this because this _could_ be how many rows it has to scan to get the answer, even with the LIMIT. The EXPLAIN implies that it might be about 5880; does that sound correct?

* Your reformulation using UNION and extra indexes is likely to be efficient because each SELECT can probably run fast.

* Still, if each of the SELECTs in the UNION returned thousands of rows, there would be little savings. Often, something like this is the cure:
( SELECT ... ORDER BY ... LIMIT 3 )
UNION
( SELECT ... ORDER BY ... LIMIT 3 )
ORDER BY ... LIMIT 3
(The pattern gets messy if you also use OFFSET.)

* The reformulation _may_ not help, depending on various things.

* innodb_buffer_pool_size, 2147483648 -- Good; so cache size was not part of the problem. Instead, the rows to scan were probably not currently in cache, and had to be read from disk.

* `content` blob NOT NULL, -- How big is this? That could be helping it be not very cacheable.

* On a typical disk system, 5880 reads will take about 60 seconds. So, if the records were sufficiently scattered, and the cache were cold, that would explain why it took so long. Probably if you ran the same query again, it would run much faster.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Long execution time despite LIMIT 3
1065
June 21, 2011 07:23AM


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.