Let's say I have a table called PEOPLE having 3 columns ID, LastName, FirstName, none of these columns are indexed.LastName is more unique, and FirstName is less unique.
假设我有一个表格叫做PEOPLE有3列ID, LastName, FirstName，这些列都没有索引。LastName更加独特，FirstName也不那么独特。
If I do 2 searches:
如果我做两个搜索:
select * from PEOPLE where FirstName="F" and LastName="L"
select * from PEOPLE where LastName="L" and FirstName="F"
My belief is the second one is faster because the more unique criterion (LastName) comes first in the where clause, and records will get eliminated more efficiently. I don't think the optimizer is smart enough to optimize the first sql.
我认为第二种方法更快，因为在where子句中，更独特的判据(姓)排在第一位，记录将更有效地被删除。我认为优化器还不够聪明，不能优化第一个sql。
Is my understanding correct?
我的理解正确吗?
6 个解决方案
#1
65
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.
任何优秀的查询优化器都将查看WHERE子句的所有部分，并找出满足该查询的最有效方法。
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.
我知道SQL Server查询优化器将选择一个合适的索引——无论您的两个条件是什么顺序。我假设其他RDBMS也有类似的策略。
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:
对于SQL Server，如果您有:
- an index on
(LastName, FirstName) - 索引(LastName, FirstName)
- an index on
(FirstName, LastName) - 索引(FirstName, LastName)
- an index on just
(LastName), or just(FirstName)(or both) - 只是(LastName)或just (FirstName)的索引(或两者都有)
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).
另一方面——再次SQL Server——如果使用SELECT *获取所有列一个表,表是很小,那么很有可能查询优化器只会做一个表(或聚集索引)扫描而不是使用一个索引(因为查找到完整的数据页面得到所有其他列很快变得过于昂贵)。
#2
9
The order of WHERE clauses should not make a difference in a database that conforms to the SQL standard. The order of evaluation is not guaranteed in most databases.
WHERE子句的顺序不应影响符合SQL标准的数据库。在大多数数据库中，评价的顺序并没有得到保证。
Do not think that SQL cares about the order. The following generates an error in SQL Server:
不要认为SQL关心顺序。以下是SQL Server中的错误:
select *
from INFORMATION_SCHEMA.TABLES
where ISNUMERIC(table_name) = 1 and CAST(table_name as int) <> 0
If the first part of this clause were executed first, then only numeric table names would be cast as integers. However, it fails, providing a clear example that SQL Server (as with other databases) does not care about the order of clauses in the WHERE statement.
如果该子句的第一部分首先执行，那么只有数字表名将被转换为整数。但是，它失败了，提供了一个清晰的示例，即SQL Server(与其他数据库一样)不关心WHERE语句中子句的顺序。
#3
7
ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf
2003年ANSI SQL草稿5wd -01- framework2003-09.pdf
6.3.3.3 Rule evaluation order
6.3.3.3规则评估订单
...
…
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
如果优先级不是由格式或括号决定的，则表达式的有效计算通常是从左到右执行。但是，表达式的值是由左到右计算的，这与实现有关，特别是当操作数或操作符可能导致条件升高时，或者表达式的结果可以在不完全计算表达式所有部分的情况下确定时。
copied from here
从这里复制
#4
2
No, all the RDBMs first start by analysing the query and optimize it by reordering your where clause.
不，所有的RDBMs都是首先分析查询并通过重新排序where子句对其进行优化。
Depending on which RDBM you are you using can display what is the result of the analyse (search for explain plan in oracle for instance)
根据使用的是哪种rdbms，可以显示分析的结果(例如在oracle中搜索explain plan)
M.
M。
#5
1
Original OP statement
原始OP声明
My belief is the second one is faster because the more unique criterion (LastName) comes first in >the where clause, and records will get eliminated more efficiently. I don't think the optimizer is >smart enough to optimize the first sql.
我认为第二种方法更快，因为在>中，最独特的准则(LastName)首先出现在where子句中，而记录将被更有效地删除。我认为优化器对于优化第一个sql还不够聪明。
I guess you are confusing this with selecting the order of columns while creating the indexes where you have to put the more selective columns first than second most selective and so on.
我想你可能会混淆这一点，选择列的顺序，同时创建索引，在这些索引中，你必须把更有选择性的列放在优先级上，而不是第二大选择等等。
BTW, for the above two query SQL server optimizer will not do any optimization but will use Trivila plan as long as the total cost of the plan is less than parallelism threshold cost.
顺便说一句，对于以上两个查询SQL server optimizer来说，只要总成本小于并行阈值成本，不会进行任何优化，而是会使用Trivila plan。
#6
0
It's true as far as it goes, assuming the names aren't indexed. Different data would make it wrong though. In order to find out which way to do it, which could differ every time, the DBMS would have to run a distinct count query for each column and compare the numbers, that would cost more than just shrugging and getting on with it.
就目前而言，这是正确的，假设这些名称没有被索引。但是，不同的数据会使它出错。为了找出这种方法的方法(每次都可能不同)，DBMS必须对每一列运行一个不同的计数查询，并对数字进行比较，这将花费比仅仅耸耸肩并继续进行更大的成本。
#1
65
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.
任何优秀的查询优化器都将查看WHERE子句的所有部分，并找出满足该查询的最有效方法。
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.
我知道SQL Server查询优化器将选择一个合适的索引——无论您的两个条件是什么顺序。我假设其他RDBMS也有类似的策略。
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:
对于SQL Server，如果您有:
- an index on
(LastName, FirstName) - 索引(LastName, FirstName)
- an index on
(FirstName, LastName) - 索引(FirstName, LastName)
- an index on just
(LastName), or just(FirstName)(or both) - 只是(LastName)或just (FirstName)的索引(或两者都有)
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).
另一方面——再次SQL Server——如果使用SELECT *获取所有列一个表,表是很小,那么很有可能查询优化器只会做一个表(或聚集索引)扫描而不是使用一个索引(因为查找到完整的数据页面得到所有其他列很快变得过于昂贵)。
#2
9
The order of WHERE clauses should not make a difference in a database that conforms to the SQL standard. The order of evaluation is not guaranteed in most databases.
WHERE子句的顺序不应影响符合SQL标准的数据库。在大多数数据库中，评价的顺序并没有得到保证。
Do not think that SQL cares about the order. The following generates an error in SQL Server:
不要认为SQL关心顺序。以下是SQL Server中的错误:
select *
from INFORMATION_SCHEMA.TABLES
where ISNUMERIC(table_name) = 1 and CAST(table_name as int) <> 0
If the first part of this clause were executed first, then only numeric table names would be cast as integers. However, it fails, providing a clear example that SQL Server (as with other databases) does not care about the order of clauses in the WHERE statement.
如果该子句的第一部分首先执行，那么只有数字表名将被转换为整数。但是，它失败了，提供了一个清晰的示例，即SQL Server(与其他数据库一样)不关心WHERE语句中子句的顺序。
#3
7
ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf
2003年ANSI SQL草稿5wd -01- framework2003-09.pdf
6.3.3.3 Rule evaluation order
6.3.3.3规则评估订单
...
…
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
如果优先级不是由格式或括号决定的，则表达式的有效计算通常是从左到右执行。但是，表达式的值是由左到右计算的，这与实现有关，特别是当操作数或操作符可能导致条件升高时，或者表达式的结果可以在不完全计算表达式所有部分的情况下确定时。
copied from here
从这里复制
#4
2
No, all the RDBMs first start by analysing the query and optimize it by reordering your where clause.
不，所有的RDBMs都是首先分析查询并通过重新排序where子句对其进行优化。
Depending on which RDBM you are you using can display what is the result of the analyse (search for explain plan in oracle for instance)
根据使用的是哪种rdbms，可以显示分析的结果(例如在oracle中搜索explain plan)
M.
M。
#5
1
Original OP statement
原始OP声明
My belief is the second one is faster because the more unique criterion (LastName) comes first in >the where clause, and records will get eliminated more efficiently. I don't think the optimizer is >smart enough to optimize the first sql.
我认为第二种方法更快，因为在>中，最独特的准则(LastName)首先出现在where子句中，而记录将被更有效地删除。我认为优化器对于优化第一个sql还不够聪明。
I guess you are confusing this with selecting the order of columns while creating the indexes where you have to put the more selective columns first than second most selective and so on.
我想你可能会混淆这一点，选择列的顺序，同时创建索引，在这些索引中，你必须把更有选择性的列放在优先级上，而不是第二大选择等等。
BTW, for the above two query SQL server optimizer will not do any optimization but will use Trivila plan as long as the total cost of the plan is less than parallelism threshold cost.
顺便说一句，对于以上两个查询SQL server optimizer来说，只要总成本小于并行阈值成本，不会进行任何优化，而是会使用Trivila plan。
#6
0
It's true as far as it goes, assuming the names aren't indexed. Different data would make it wrong though. In order to find out which way to do it, which could differ every time, the DBMS would have to run a distinct count query for each column and compare the numbers, that would cost more than just shrugging and getting on with it.
就目前而言，这是正确的，假设这些名称没有被索引。但是，不同的数据会使它出错。为了找出这种方法的方法(每次都可能不同)，DBMS必须对每一列运行一个不同的计数查询，并对数字进行比较，这将花费比仅仅耸耸肩并继续进行更大的成本。