Does Limiting A Query To One Record Improve Performance

- 1 answer

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?



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