Does limiting a query to one record improve performance

Question

Will limiting a query to one result record, improve performance in a large(ish) MySQL table if the table only has one matching result?

for example

 select * from people where name = "Re0sless" limit 1

if there is only one record with that name? and what about if name was the primary key/ set to unique? and is it worth updating the query or will the gain be minimal?

Answer 1

I believe the LIMIT is something done after the data set is found and the result set is being built up so I wouldn't expect it to make any difference at all. Making name the primary key will have a significant positive effect though as it will result in an index being made for the column.

Answer 2

If the column has

a unique index: no, it's no faster

a non-unique index: maybe, because it will prevent sending any additional rows beyond the first matched, if any exist

no index: sometimes

• if 1 or more rows match the query, yes, because the full table scan will be halted after the first row is matched.
• if no rows match the query, no, because it will need to complete a full table scan

Answer 3

If "name" is unique in the table, then there may still be a (very very minimal) gain in performance by putting the limit constraint on your query. If name is the primary key, there will likely be none.

Answer 4

To answer your questions in order: 1) yes, if there is no index on name. The query will end as soon as it finds the first record. take off the limit and it has to do a full table scan every time. 2) no. primary/unique keys are guaranteed to be unique. The query should stop running as soon as it finds the row.

Answer 5

Yes, you will notice a performance difference when dealing with the data. One record takes up less space than multiple records. Unless you are dealing with many rows, this would not be much of a difference, but once you run the query, the data has to be displayed back to you, which is costly, or dealt with programmatically. Either way, one record is easier than multiple.

Answer 6

If you have a slightly more complicated query, with one or more joins, the LIMIT clause gives the optimizer extra information. If it expects to match two tables and return all rows, a hash join is typically optimal. A hash join is a type of join optimized for large amounts of matching.

Now if the optimizer knows you've passed LIMIT 1, it knows that it won't be processing large amounts of data. It can revert to a loop join.

Based on the database (and even database version) this can have a huge impact on performance.