MYSQL and the LIMIT clause

问题:

I was wondering if adding a LIMIT 1 to a query would speed up the processing?

For example...

I have a query that will most of the time return 1 result, but will occasionaly return 10's, 100's or even 1000's of records. But I will only ever want the first record.

Would the limit 1 speed things up or make no difference?

I know I could use GROUP BY to return 1 result but that would just add more computation.

Any thoughts gladly accepted!

Thanks

回答1:

It depends if you have an ORDER BY. An ORDER BY needs the entire result set anyway, so it can be ordered.

If you don't have any ORDER BY it should run faster.

It will in all cases run at least a bit faster since the entire result set needn't be sent of course.

回答2:

Yep! it will!

But to be sure, it should only take a sec to add 'Limit 1' to the end of your sql statement so why not give a shot and see

回答3:

It all depends on the query itself. If you're doing an indexed lookup (Where indexedColumn = somevalue)or a sort on an indexed column (with no Where clause), then limit 1 will really speed it up. If you have joins or multiple where/order clauses, then things get really complicated really quickly. But the major thing to take away, using "LIMIT 1" will NEVER slow down a query. It will sometimes speed it up, but it will never slow it down.

Now, there is another issue when dealing with PHP. By default, PHP will buffer the entire result set before returning from the query (mysql_query or mysqli->query will only return after all the records are downloaded). So while the query time may be altered little by the limit 1, the time and memory that PHP uses to buffer the result are significant. Imagine each row has 200 bytes of data. Now, your query returns 10,000 rows. That means PHP has to allocate an additional 2mb of memory (actually closer to 10mb with the overhead of php's variable structure) that you'll never use. Allocating memory can be very expensive, so the general rule is only ever allocate what you need (or think you will need). Downloading 10,000 rows when you only want 1 is just wasteful.

Combine these two effects, and you can see why if you want only 1 row, you should ALWAYS use "LIMIT 1".

回答4:

Here is some relevant documentation on the subject from the MySQL site.

It seems it can speed things up in different ways, depending on the other parts of the query. I'm not sure if it helps when you have no ORDER or GROUP or HAVING clauses, aside from being able to stop immediately rather than give back every single result row (which may be a big enough speed up if you are getting back 100,000 records).

回答5:

This is the fundamental purpose of the LIMIT clause actually :P If you know how many results you want, you should ALWAYS specify that number as the LIMIT not only because it is faster (unless you are doing an ORDER BY), but to be more efficient with memory in your PHP script.

Note: I'm assuming you're using MySQL with PHP since you added PHP to the tags. If you're just selecting from MySQL directly (outside of a scripting language) the advantage to using LIMIT when also using ORDER BY is purely to make the results easier to manage.