Query performance + limit

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

Query performance + limit

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Query performance + limit

Post 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.
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

Re: Query performance + limit

Post by hame22 »

Thanks, great response! reinforces what I thought

Much appreicated
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Query performance + limit

Post by califdon »

Yeah, a beautiful explanation, onion2k.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Query performance + limit

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Query performance + limit

Post 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
Post Reply