Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks Bill! There are indeed some results from dm_db_missing_index_group_stats that include client_id and/or status but not client_product_id but they are relatively insignificant in number. None of these columns are nullable, but out of curiosity, how would that cause a difference? –  Carson63000 Jun 30 '11 at 3:46
    
Some databases don't include null values in indices. Null values on leading values may lead to missing segments of the index. This is not necessarily a problem with equality, but can be a problem matching equalities with IS NULL on leading index columns. –  BillThor Jun 30 '11 at 5:46

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.