Does the order of conditions in there where clause affect the speed of the query?

Question

Does the order of conditions in there where clause affect the speed of the query?

For instance, could:

select fname, lname 
from people 
where isValid = 1 and lname like '%test%'

be faster than

select fname, lname 
from people 
where lname like '%test%' and isValid = 1

Duplicate: Does the order of columns in a WHERE clause matter?

Answer 1

Exact duplicate of this question.

The short answer is yes, but it depends on the query optimizer.

Answer 2

Depends on query optimizer. Generally good optimizer would change order to optimal one based on index statistics. But not all databases have good optimizers.

Answer 3

If it did, do you think it would be an obscure question on Stack Overflow?

If the order of the where clause mattered, don't you think

• The vendors would document this.

• There would be 3rd party tools to optimize your where clauses.

• There'd be special JDBC or DAO wrappers that would re-order your where clause to optimize performance based on some criteria you provided.

• There'd be huge religious wars over the subtleties of one optimization strategy over another.

Since none of these things are true, you can safely bet that the where clause is decomposed into "conjunctive normal form" for processing and the original structure is completely lost.