dm_db_missing_index_details - does the order of equality_columns matter

Question

I've been investigating some databases using the dm_db_missing_index_group_stats DMV, and have a question about the equality_columns field in the dm_db_missing_index_details view.

MSDN says that this holds "a comma-separated list of columns that contribute to equality predicates of the form: table.column = constant value"

I have a table which is represented in three of the highest cost missing indexes, with the following equality columns:

• client_product_id
• client_id, client_product_id
• client_id, client_product_id, status

Does the order of the columns in the second and third index groups matter? e.g. if I created an index on client_product_id, client_id, status, it seems that it should satisfy all three missing index cases, on the grounds that a query like client_id = 123 AND client_product_id = 456 should be satisfied equally regardless of whether client_product_id or client_id comes first in the index definition.

(obviously if it was an inequality predicate, this would not be the case)

Answer 1

In this case the single index you propose could be used to satisfy all three equality searches. If any of the columns allows null values, it may matter which order the columns are in.

The optimizer should match the index regardless of the order in the query. What is important that the equality conditions match the leading columns in the index. The proposed index would not be good for an equality condition on client_id or stats but not including client_product_id.

In some cases the index may be used if the first field is referenced, but one of the subsequent values is skipped. Depending on data distribution an optimizer may use a range scan on the proposed index to search for equality on client_product_id and status but not client_id.

Answer 2

My rule of thumb in this case has always been to put the columns in order by how unique the values are in the rest of the table.

    client_id, client_product_id, status

This is possibly the order in which I would create the index given that clients have unique IDs, product IDs are probably shared across clients (less unique) and you most likely have less status values than either clients or products.