Geek Answers Handbook

Does the column order in the WHERE clause mean index selection?

Suppose I execute a query that has:

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

column1 indexed, but column2 is not. Does my article order WHERE ? Should I start the subquery on the indexed column first? Or is SQL smart enough to automatically query an indexed column first?

+4
source share
4 answers

The order in the SQL statement does not matter, of course, not for indexes that do not cover indexes (more than one column).

Coverage indexes require the query to contain a link for at least one column, starting from the left side of the list. IE: The coverage index, defined as "column1, column2, column3", requires at least queries to reference column1 in order to use the index. A query that has only references to column2 or a combination of column2 and column3 will not use the coverage index.

However, optimizer optimization decisions are determined by table statistics and how the index is fragmented during the query. None of them support self-service, because depending on the amount of data it can take a very long time (so you would not want this to happen all the time). Having an index does not guarantee that the index will always be used.

Indexes are also not ANSI, but surprisingly providers (MySQL, Oracle, etc.) have relatively similar syntax and naming conventions.

+6
source

The order in which you enter the where clause does not matter - the execution scheduler for the database will sort this.

In the above example, each column corresponding to column1 will be checked first because it is indexed and then the value of column2 is checked.

+1
source

For this request, any of them is optimal:

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

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

Power doesn't matter.

Read more about creating optimal indexes for MySQL ; most of them should relate to other engines.

+1
source

If I remember correctly, the order of the sentences does not matter. It is part of the same execution plan, so if you look at the exec plan, you will notice that the where clause in an unindexed field will be very expensive, no matter what order you place it in.

If you asked for a very high query, you'd better have this field in a non-clustered index, or at least with an include clause in the index.

0
source

More articles:


All Articles