MYSQL and the LIMIT clause
I was wondering if LIMIT 1 would add a query to speed up processing?
For instance...
I have a query that will return a result of 1 in most cases, but will sometimes return 10, 100, or even 1000 records. But I just need the first entry.
Will constraint 1 speed things up or not make any difference?
I know I can use GROUP BY to return 1 result, but that will just add more computation.
Any thoughts are welcomed!
thanks
a source to share
It all depends on the request itself. If you are doing an indexed search (where indexedColumn = somevalue) or sorting on an indexed column (no Where clause) then constraint 1 will really speed it up. If you have joins or multiple where / order clauses, things get very complicated very quickly. But the main thing to remove using "LIMIT 1" will NEVER slow down the query. Sometimes it speeds it up, but it never slows it down.
Now there is a problem when working with PHP. By default PHP will buffer the entire result set before returning from the query (mysql_query or mysqli-> the query will only return after all records have been loaded). So, while the request time can be little changed over the 1 limit, the time and memory that PHP uses to buffer the result is significant. Imagine each line has 200 bytes of data. Now your query is returning 10,000 rows. This means PHP has to allocate an additional 2MB of memory (actually closer to 10MB with the php variable structure overhead) that you will never use. Allocating memory can be very expensive, so the general rule of thumb is to only ever allocate what you need (or consider what you need). Loading 10,000 lines,when you only need 1 is just wasteful.
Combine the two and you can see why, if you only want 1 row, you should ALWAYS use "LIMIT 1".
a source to share
Below is the relevant documentation on the matter from the MySQL site.
It seems it can speed things up in different ways, depending on other parts of the query. I'm not sure if this helps when you have no suggestions ORDER
or GROUP
or HAVING
other than the ability to immediately stop rather than return each row of results (which can be big enough if you are returning 100,000 records).
a source to share
This is the main purpose of the LIMIT clause: P If you know how many results you want, you should ALWAYS specify this number as LIMIT, not only because it is faster (unless you do ORDER BY), but to be more memory efficient in your PHP script.
Note. I am assuming you are using MySQL with PHP since you added PHP to your tags. If you are just selecting from MySQL directly (outside of the scripting language), then the advantage of using LIMIT when using ORDER BY is to make it easier to manage the results.
a source to share