select * from PEOPLE where FirstName="F" and LastName="L"
select * from PEOPLE where LastName="L" and FirstName="F"

答案是顺序没有影响,因为SQL引擎自己会执行最优的计划,顺序由SQL引擎处理,并不是你写的这个顺序,真正影响性能的是你有没有建立合适的索引,比如有没有索引,是FirstName和LastName上分别有索引,还是有复合索引(FirstName, LastName)等?

Does order of where clauses matter in SQL
No, that order doesn't matter (or at least: shouldn't matter).

Any decent query optimizer will look at all the parts of the WHERE clause and figure out the most efficient way to satisfy that query.

I know the SQL Server query optimizer will pick a suitable index - no matter which order you have your two conditions in. I assume other RDBMS will have similar strategies.

What does matter is whether or not you have a suitable index for this!

In the case of SQL Server, it will likely use an index if you have:

an index on (LastName, FirstName)
an index on (FirstName, LastName)
an index on just (LastName), or just (FirstName) (or both)
On the other hand - again for SQL Server - if you use SELECT * to grab all columns from a table, and the table is rather small, then there's a good chance the query optimizer will just do a table (or clustered index) scan instead of using an index (because the lookup into the full data page to get all other columns just gets too expensive very quickly).

Does the order of fields in a WHERE clause affect performance in MySQL?

本文转载自:ITEYE博客频道     作者:darrenzhu    发布日期:2015-09-09

© 2014-2016 ITdaan.com 粤ICP备14056181号