将索引列放在 mysql WHERE 语句的左侧？
自从我在文档中读到它以来，我很好奇。 写入是否可以
select * from CONTACTS where id = ‘098’ and name like ‘Tom%’;
相反，
select * from CONTACTS where name like ‘Tom%’ and id = ‘098’;
与第一个在左侧有索引列 加快查询速度。它真的能加快速度还是迷信？
使用php和mysql
I was curious since i read it in a doc. Does writing
select * from CONTACTS where id = ‘098’ and name like ‘Tom%’;
speed up the query as oppose to
select * from CONTACTS where name like ‘Tom%’ and id = ‘098’;
The first has an indexed column on the left side. Does it actually speed things up or is it superstition?
Using php and mysql
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
使用
explain。它们应该是完全相同的。Check the query plans with
explain. They should be exactly the same.这纯粹是迷信。我认为这两个查询的速度没有任何不同。但是，如果它是 OR 查询而不是 AND 查询，那么我可以看到将其放在左侧可能会加快速度。
This is purely superstition. I see no reason that either query would differ in speed. If it was an OR query rather than an AND query however, then I could see that having it on the left may spped things up.
有趣的问题，我试过一次。查询计划是相同的（使用
EXPLAIN）。但考虑到短路评估，我也想知道为什么没有区别（或者mysql是否完全评估布尔语句？）
interesting question, i tried this once. query plans are the same (using
EXPLAIN).but considering short-circuit-evaluation i was wondering too why there is no difference (or does mysql fully evaluate boolean statements?)
关于通配符位于 Like 谓词中字符串文字的哪一侧，您可能记错或读错了其他内容。将通配符放在右侧（如示例中所示），允许查询引擎使用您正在搜索的表列上可能存在的任何索引（在本例中为名称）。但如果你把通配符放在左边，
那么引擎就不能使用任何现有的索引，必须进行全表扫描。
You may be mis-remembering or mis-reading something else, regarding which side the wildcards are on a string literal in a Like predicate. Putting the wildcard on the right (as in yr example), allows the query engine to use any indices that might exist on the table column you are searching (in this case - name). But if you put the wildcard on the left,
then the engine cannot use any existing index and must do a complete table scan.