Skip navigation links

MySQL Forums :: Performance :: Long execution time despite LIMIT 3


Advanced Search

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
Long execution time despite LIMIT 3 1169 Joachim Berger 06/19/2011 12:47PM
Re: Long execution time despite LIMIT 3 490 Joachim Berger 06/19/2011 12:54PM
Re: Long execution time despite LIMIT 3 471 Rick James 06/20/2011 06:33PM
Re: Long execution time despite LIMIT 3 459 Joachim Berger 06/20/2011 11:44PM
Re: Long execution time despite LIMIT 3 480 Joachim Berger 06/21/2011 02:06AM
Re: Long execution time despite LIMIT 3 486 Rick James 06/21/2011 07:23AM
Re: Long execution time despite LIMIT 3 421 Joachim Berger 06/21/2011 07:28AM
Re: Long execution time despite LIMIT 3 507 Joachim Berger 06/21/2011 10:33AM
Re: Long execution time despite LIMIT 3 561 Rick James 06/21/2011 10:08PM
Re: Long execution time despite LIMIT 3 665 Joachim Berger 06/22/2011 12:33AM
Re: Long execution time despite LIMIT 3 512 Rick James 06/22/2011 07:26PM
Re: Long execution time despite LIMIT 3 461 Joachim Berger 06/22/2011 11:45PM
Re: Long execution time despite LIMIT 3 425 Rick James 06/23/2011 08:53AM
Re: Long execution time despite LIMIT 3 533 Joachim Berger 06/24/2011 02:51AM


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.