Does the order of columns in a WHERE clause matter?

Question

Does the order of the columns in a WHERE clause effect performance?

e.g.

Say I put a column that has a higher potential for uniqueness first or visa versa?

Answer 1

With a decent query optimiser: it shouldn't.

But in practice, I suspect it might.

You can only tell for your cases by measuring. And the measurements will likely change as the distribution of data changes in the database.

Answer 2

For Transact-SQL there is a defined precedence for operators in the condition of the WHERE clause. The optimizer may re-order this evaluation, so you shouldn't rely on short-circuiting behavior for correctness. The order is generally left to right, but selectivity/availability of indexes probably also matters. Simplifying your search condition should improve the ability of the optimizer to handle it.

Ex:

 WHERE (a OR b) AND (b OR c) 

could be simplified to

 WHERE b OR (a AND c) 

Clearly in this case if the query can be constructed to find if b holds first it may be able to skip the evaluation of a and c and thus would run faster. Whether the optimizer can do this simple transformation I can't answer (it may be able to), but the point is that it probably can't do arbitrarily complex transformations and you may be able to effect query performance by rearranging your condition. If b is more selective or has an index, the optimizer would likely be able to construct a query using it first.

EDIT: With regard to your question about ordering based on uniqueness, I would assume that the any hints you can provide to the optimizer based on your knowledge (actual, not assumed) of the data couldn't hurt. Pretend that it won't do any optimization and construct your query as if you needed to define it from most to least selective, but don't obsess about it until performance is actually a problem.

Quoting from the reference above:

The order of precedence for the logical operators is NOT (highest), followed by AND, followed by OR. Parentheses can be used to override this precedence in a search condition. The order of evaluation of logical operators can vary depending on choices made by the query optimizer.