109

When I add LIMIT 1 to a MySQL query, does it stop the search after it finds 1 result (thus making it faster) or does it still fetch all of the results and truncate at the end?

1

5 Answers 5

91

Depending on the query, adding a limit clause can have a huge effect on performance. If you want only one row (or know for a fact that only one row can satisfy the query), and are not sure about how the internal optimizer will execute it (for example, WHERE clause not hitting an index and so forth), then you should definitely add a LIMIT clause.

As for optimized queries (using indexes on small tables) it probably won't matter much in performance, but again - if you are only interested in one row than add a LIMIT clause regardless.

9
  • 34
    For antibugging purposes you may want to consider sending LIMIT 2 and then complain or bomb out if your single-row assumption does not hold. Nov 30, 2009 at 21:35
  • 6
    @JeffreyHantin If there really can be only one you should prefer adding a unique constraint to the database. Much cleaner then messing up your code with sanity checks. Jul 31, 2013 at 21:39
  • @CristianVrabie If it can be expressed as a unique constraint, sure, but that's an assertion about the data in the tables. Sending LIMIT 2 and checking for 1 is assertion about the query itself, which may very well contain a bug such as an inadequately specified join condition. Aug 1, 2013 at 1:30
  • 1
    @JeffreyHantin Fair enough, but except if you code for the Mars Rover you write tests for this rather then pollute your code. Aug 1, 2013 at 13:53
  • 9
    @JeffreyHantin If the assumption that there is only one is correct, wouldn't adding LIMIT 2 kill any optimization since it would search all the rows? Adding LIMIT 2 in that case is no better (optimization-wise) than adding nothing at all. Aug 19, 2014 at 22:27
23

Limit can affect the performance of the query (see comments and the link below) and it also reduces the result set that is output by MySQL. For a query in which you expect a single result there is benefits.

Moreover, limiting the result set can in fact speed the total query time as transferring large result sets use memory and potentially create temporary tables on disk. I mention this as I recently saw a application that did not use limit kill a server due to huge result sets and with limit in place the resource utilization dropped tremendously.

Check this page for more specifics: MySQL Documentation: LIMIT Optimization

3
  • The page you linked says: "If you are selecting only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan." That doesn't look like the query itself is always processed as usual.
    – che
    Jan 18, 2009 at 17:29
  • good point. I was making a generalization based on observations from using EXPLAIN. Thanks for the catch. Jan 18, 2009 at 17:31
  • The link is much appreciated. FWIW, most of this answer seems to apply to situations where without Limit, there might be a large result set. Question is asking whether it matters when only 1 row will ever successfully match; in which case the "result set" is only 1 row (even w/o Limit). Mar 19, 2019 at 15:26
10

The answer, in short, is yes. If you limit your result to 1, then even if you are "expecting" one result, the query will be faster because your database wont look through all your records. It will simply stop once it finds a record that matches your query.

2
  • 2
    This answer is misleading, because it lacks the caveat seen in comments on other answers (7 years earlier!) that if the DB knows that only one record can possibly match (e.g. querying a unique column), then Limit 1 will have no effect. Mar 19, 2019 at 14:51
  • Seriously? The query optimizer can take advantage of the fact that certain conditions guarantee that only 1 record can possibly be returned. I was pointing out that your answer is not correct under all circumstances. (And therefore subtracts value from what was already said years earlier.) If you agree, you could acknowledge the correctness of my comment. If you don't agree, you could clarify why. Mar 19, 2019 at 20:42
8

If there is only 1 result coming back, then no, LIMIT will not make it any faster. If there are a lot of results, and you only need the first result, and there is no GROUP or ORDER by statements then LIMIT will make it faster.

2
  • 4
    It should be faster even with 1 row, if there are no unique/primary keys, because it stops searching after it founds the first occurrence
    – the_nuts
    Aug 28, 2015 at 8:41
  • Kris, your first sentence could be read one of two ways. If you mean "only 1 row could ever possibly be returned" (e.g. querying a unique column), then what you say is true. OTOH, if you mean "whenever only 1 matching result is found" limit won't make it faster: are you sure? Doesn't it sometimes avoid having to pull in additional pages? Mar 19, 2019 at 14:48
2

If you really only expect one single result, it really makes sense to append the LIMIT to your query. I don't know the inner workings of MySQL, but I'm sure it won't gather a result set of 100'000+ records just to truncate it back to 1 at the end..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.