将查询限制为一条记录可提高性能 [英] Does limiting a query to one record improve performance

问题描述

如果表只有一个匹配结果，那么将查询限制为一个结果记录会提高大型（ish）MySQL表中的性能？

p>

  select * from people where name =Re0slesslimit 1 
  

如果只有一个该名称的记录？如果 name 是主键/设置为unique？

解决方案

如果列有

唯一索引：否，不会更快

非唯一索引：

• 如果1行或更多行符合查询是，因为全表扫描将在第一行匹配后暂停


• 如果没有符合查询的行 ，因为它需要完成全表扫描

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

这篇关于将查询限制为一条记录可提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！