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


Advanced Search

Long execution time despite LIMIT 3
Posted by: Joachim Berger ()
Date: June 19, 2011 12:47PM

Hi,

it sounds stunningly basic, but I don't get it. We have a table containing some million messages (InnoDB), and an index on a field called 'thread'. Now, especially under heavy traffic, a simple

SELECT .. FROM table WHERE thread='foo' ORDER BY id DESC LIMIT 3

might run for more than 60 seconds(!!!!) when the result would return a large number of rows. But there's a LIMIT! So why does it take that long?

Regards,
Ben

Update: There's an index on (thread,id) being used, by the way.



Edited 1 time(s). Last edit at 06/19/2011 12:55PM by Joachim Berger.

Options: ReplyQuote


Subject Views Written By Posted
Long execution time despite LIMIT 3 1737 Joachim Berger 06/19/2011 12:47PM
Re: Long execution time despite LIMIT 3 713 Joachim Berger 06/19/2011 12:54PM
Re: Long execution time despite LIMIT 3 669 Rick James 06/20/2011 06:33PM
Re: Long execution time despite LIMIT 3 679 Joachim Berger 06/20/2011 11:44PM
Re: Long execution time despite LIMIT 3 743 Joachim Berger 06/21/2011 02:06AM
Re: Long execution time despite LIMIT 3 737 Rick James 06/21/2011 07:23AM
Re: Long execution time despite LIMIT 3 632 Joachim Berger 06/21/2011 07:28AM
Re: Long execution time despite LIMIT 3 761 Joachim Berger 06/21/2011 10:33AM
Re: Long execution time despite LIMIT 3 777 Rick James 06/21/2011 10:08PM
Re: Long execution time despite LIMIT 3 1057 Joachim Berger 06/22/2011 12:33AM
Re: Long execution time despite LIMIT 3 666 Rick James 06/22/2011 07:26PM
Re: Long execution time despite LIMIT 3 685 Joachim Berger 06/22/2011 11:45PM
Re: Long execution time despite LIMIT 3 675 Rick James 06/23/2011 08:53AM
Re: Long execution time despite LIMIT 3 753 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.