106

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?

Logan Serman
  • 29,447
  • 27
  • 102
  • 141
  • 1
    Not if there's a `UNIQUE` (or `PRIMARY KEY`) constraint (or index) on the filter column. http://stackoverflow.com/questions/8467092/does-using-limit-1-speed-up-a-query-on-a-primary-key – ma11hew28 May 09 '15 at 22:09

5 Answers5

90

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.

Eran Galperin
  • 86,251
  • 24
  • 115
  • 132
  • 33
    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. – Jeffrey Hantin Nov 30 '09 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. – Cristian Vrabie Jul 31 '13 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. – Jeffrey Hantin Aug 01 '13 at 01:30
  • 1
    @JeffreyHantin Fair enough, but except if you code for the Mars Rover you write tests for this rather then pollute your code. – Cristian Vrabie Aug 01 '13 at 13:53
  • @CristianVrabie And if you code for random apps where bombing is only an inconvenience, [ship with assertions on](http://c2.com/cgi/wiki?ShipWithAssertionsOn), because even if your code is flawless (unit tests help there) the environment may break your assumptions. – Jeffrey Hantin Sep 05 '13 at 22:50
  • 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. – Chris Middleton Aug 19 '14 at 22:27
  • @AmadeusDrZaius, I made the suggestion not as an optimization but rather as a run-time error check... and if you're in a table scan situation, you're probably already too slow, LIMIT or no LIMIT. – Jeffrey Hantin Aug 21 '14 at 00:55
  • @JeffreyHantin - but Chris correctly observes that `LIMIT 2` is pointless in this case, as it will search fruitlessly; no different than leaving that clause off. So why bother adding it? That will just confuse someone later, when they review the code... – ToolmakerSteve Mar 19 '19 at 14:41
  • @JeffreyHantin - actually I'm partially wrong: under some circumstances, `LIMIT 2` allows faster execution, even if entire table must be scanned; because it is known that at most 2 rows need to be kept in memory during the sort (whenever a row sorts to earlier than an existing row, can immediately drop the row that is now 3rd). Not sure if MySQL has such optimization, but other threads discussing SQL queries with limit mention this fact. Especially when complex query might otherwise require creating a temp table of unknown size. – ToolmakerSteve Mar 19 '19 at 15:20
22

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

rjamestaylor
  • 3,052
  • 1
  • 19
  • 10
  • 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 '09 at 17:29
  • good point. I was making a generalization based on observations from using EXPLAIN. Thanks for the catch. – rjamestaylor Jan 18 '09 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). – ToolmakerSteve Mar 19 '19 at 15:26
9

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.

Max Alexander Hanna
  • 3,388
  • 1
  • 25
  • 35
  • 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. – ToolmakerSteve Mar 19 '19 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. – ToolmakerSteve Mar 19 '19 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.

Kris Erickson
  • 33,454
  • 26
  • 120
  • 175
  • 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 '15 at 08: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? – ToolmakerSteve Mar 19 '19 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..

driAn
  • 3,245
  • 4
  • 41
  • 57