Does the Column Order in the WHERE clause matter for Index Selection?

Question

Suppose I'm running a query that has:

WHERE column1 = "value1" 
  AND column2 = "value2"

column1 is indexed, and column2 is not. Does the order of my WHERE clause matter? Should I run a subquery over the indexed column first? Or, is SQL smart enough to automatically query over the indexed column first?

Answer 1

The order in the SQL statement does not matter, certainly not for indexes that are not covering indexes (more than one column).

Covering indexes require that there be a reference in the query for at least one column, starting from the left of the list. IE: A covering index defined as "column1, column2, column3" needs queries to at least reference column1 in order to use the index. A query that only has references to either column2, or a combination of column2 and column3 would not use the covering index.

That said, index decisions by the optimizer are determined by table statistics & how fragmented the index is at the time of the query. Neither of these is self-maintaining, because depending on the amount of data can be very time consuming (so you wouldn't want it happening all the time). Having an index doesn't guarantee the index will always be used.

Indexes are also not ANSI, but surprisingly vendors (MySQL, Oracle, etc) have relatively similar syntax & naming.