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

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 2

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.

Answer 3

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.