My thinking is that if I put my ANDs that filter out a greater number of rows before those that filter out just a few, my query should run quicker since that selection set is much smaller between And statements.
我的想法是，如果我把我的ANDs过滤掉那些过滤掉少量行之前的行，那么我的查询应该运行得更快，因为And语句之间的选择集要小得多。
But does the order of AND in the WHERE clause of an SQL Statement really effect the performance of the SQL that much or are the engines optimized already for this?
但是，SQL语句的WHERE子句中AND的顺序是否真的会影响SQL的性能，或者已经为此优化了引擎？
3 个解决方案
#1
18
It really depends on the optimiser.
这真的取决于优化者。
It shouldn't matter because it's the optimiser's job to figure out the optimal way to run your query regardless of how you describe it.
这应该不重要，因为无论您如何描述它，找出运行查询的最佳方式都是优化者的工作。
In practice, no optimiser is perfect so you might find that re-ordering the clauses does make a difference to particular queries. The only way to know for sure is to test it yourself with your own schema, data etc.
在实践中，没有优化器是完美的，因此您可能会发现重新排序子句确实会对特定查询产生影响。唯一可以确定的方法是使用自己的架构，数据等自行测试。
#2
6
Most SQL engines are optimized to do this work for you. However, I have found situations in which trying to carve down the largest table first can make a big difference - it doesn't hurt !
大多数SQL引擎都经过优化，可以为您完成这项工作。但是，我发现首先尝试分割最大的表可以产生很大的差异 - 这并没有伤害！
#3
1
A lot depends how the indices are set up. If an index exists which combines the two keys, the optimizer should be able to answer the query with a single index search. Otherwise if independent indices exist for both keys, the optimizer may get a list of the records satisfying each key and merge the lists. If an index exists for one condition but not the other, the optimizer should filter using the indexed list first. In any of those scenarios, it shouldn't matter what order the conditions are listed.
很大程度上取决于指数的设置方式。如果存在组合两个键的索引，则优化器应该能够使用单个索引搜索来回答查询。否则，如果两个键都存在独立索引，则优化器可以获得满足每个键的记录列表并合并列表。如果某个条件的索引存在而另一个条件不存在，则优化程序应首先使用索引列表进行过滤。在任何这些情况下，列出条件的顺序都无关紧要。
If none of the conditions apply, the order the conditions are specified may affect the order of evaluation, but since the database is going to have to fetch every single record to satisfy the query, the time spent fetching will likely dwarf the time spent evaluating the conditions.
如果没有条件适用，则指定条件的顺序可能会影响评估顺序，但由于数据库必须获取每个单个记录以满足查询，因此获取所花费的时间可能会使评估条件所花费的时间相形见绌。条件。
#1
18
It really depends on the optimiser.
这真的取决于优化者。
It shouldn't matter because it's the optimiser's job to figure out the optimal way to run your query regardless of how you describe it.
这应该不重要，因为无论您如何描述它，找出运行查询的最佳方式都是优化者的工作。
In practice, no optimiser is perfect so you might find that re-ordering the clauses does make a difference to particular queries. The only way to know for sure is to test it yourself with your own schema, data etc.
在实践中，没有优化器是完美的，因此您可能会发现重新排序子句确实会对特定查询产生影响。唯一可以确定的方法是使用自己的架构，数据等自行测试。
#2
6
Most SQL engines are optimized to do this work for you. However, I have found situations in which trying to carve down the largest table first can make a big difference - it doesn't hurt !
大多数SQL引擎都经过优化，可以为您完成这项工作。但是，我发现首先尝试分割最大的表可以产生很大的差异 - 这并没有伤害！
#3
1
A lot depends how the indices are set up. If an index exists which combines the two keys, the optimizer should be able to answer the query with a single index search. Otherwise if independent indices exist for both keys, the optimizer may get a list of the records satisfying each key and merge the lists. If an index exists for one condition but not the other, the optimizer should filter using the indexed list first. In any of those scenarios, it shouldn't matter what order the conditions are listed.
很大程度上取决于指数的设置方式。如果存在组合两个键的索引，则优化器应该能够使用单个索引搜索来回答查询。否则，如果两个键都存在独立索引，则优化器可以获得满足每个键的记录列表并合并列表。如果某个条件的索引存在而另一个条件不存在，则优化程序应首先使用索引列表进行过滤。在任何这些情况下，列出条件的顺序都无关紧要。
If none of the conditions apply, the order the conditions are specified may affect the order of evaluation, but since the database is going to have to fetch every single record to satisfy the query, the time spent fetching will likely dwarf the time spent evaluating the conditions.
如果没有条件适用，则指定条件的顺序可能会影响评估顺序，但由于数据库必须获取每个单个记录以满足查询，因此获取所花费的时间可能会使评估条件所花费的时间相形见绌。条件。