Page 1 of 1

Query performance + limit

Posted: Wed Sep 24, 2008 7:31 am
by hame22
Hi,

this may be a bit of a stupid question - does the limit clause included in a query improve the queries performance? If so is this by a great factor?

Thanks in advance

Re: Query performance + limit

Posted: Wed Sep 24, 2008 8:49 am
by onion2k
It can improve things dramatically, but it really depends on the query. For example, if you select 10 rows from a table without specifying the order MySQL will stop searching the table once it's got ten records ... in a big table that will make a huge difference. If you specify the order though MySQL has to fetch all the records before it can reorder then and select 10, so the limit won't make a big difference in the time it takes.

Where LIMIT does make a difference is in memory use. A buffered query (which is almost certainly what you're using) will generate a recordset in MySQL's memory that can be massive on a big query. A LIMIT will make a big difference. If you're doing lots of queries then that will speed things up loads because the server won't have to page anything out to the disk when it runs low on RAM.

If you know you don't need to access all the records from a query then using LIMIT is a good practise.

PS: It's not a stupid question. It's a very sensible question. Knowing this sort of thing (how MySQL works) makes a real difference to the quality of your software.

Re: Query performance + limit

Posted: Wed Sep 24, 2008 9:38 am
by hame22
Thanks, great response! reinforces what I thought

Much appreicated

Re: Query performance + limit

Posted: Wed Sep 24, 2008 12:53 pm
by califdon
Yeah, a beautiful explanation, onion2k.

Re: Query performance + limit

Posted: Wed Sep 24, 2008 6:49 pm
by VladSun
http://www.mysqlperformanceblog.com/200 ... imization/
Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, so it is important for it to use index - in this case index range scan will be started and query execution stopped as soon as soon as required amount of rows generated.

Re: Query performance + limit

Posted: Wed Sep 24, 2008 9:30 pm
by josh
you can benchmark it and if you have the order pre-calculated you can use 'where position between' syntax which will greatly optimize your query