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

Suppose I'm running a query that has:

WHERE column1 开发者_JAVA技巧= "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?

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.

For that query, either of these is optimal:

INDEX(column1, column2)
INDEX(column2, column1)

The order of things in the WHERE does not matter; the order of the columns in an INDEX does matter, sometimes a lot.

Cardinality does not matter.

More on creating optimal indexes for MySQL; much of that should relevant to other engines.

The order that you type your where clause does not matter -- the execution planner for the database will sort that out.

In the example you show above, every row matching column1 will be looked up first because it is indexed and then the value of column2 checked.

If I remember correctly the order of clauses is not significant. Its all part of the same execution plan, so if you view the exec plan you will notice that the where clause on a nonindexed field will be very expensive, irregardless of the order you put it in.

If it is a highly queried you are better of having that field in a nonclustered index, or at the very least with an include clause in the index.