Post reply

Index Selectivity Ponderings

  • October 20, 2011 at 3:40 pm

    #250319

    The rule of thumb and suggested best practice is to create indexes with highly selective columns.

    I have a table with 40 million rows that I query against frequently for reporting. In addition to a timestamp field, I have bit fields and fields with only 3-5 possible values in this table that are included in probably 75% of my queries.

    In indexing this table, my tests have shown that indexes on these fields with low selectivity first are generating seeks that perform much faster than other indexes.

    So an index that looks like this: (IntWith4PossValues, IntWith3PossValues, Bit, TimeStamp) performs well and in fact was the index suggested in the execution plan

    This seems to go against the rule above, and I am wondering if anyone has further thoughts or insights into this.

  • October 20, 2011 at 4:40 pm

    #1397695

    That 'best practice' is at best vague. There's far more in consideration than just selectivity.

    Selectivity is irrelevant when the index is covering. It's very important when the index is not covering as SQL won't use a non-covering index that retrieves more than a very small portion of the table.

    My blog's down, but this should help: http://qa.sqlservercentral.com/articles/Indexing/68636/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply