Post reply

Composite Index or individual indices?

  • January 16, 2012 at 5:45 am

    #262824

    Hi,

    I have one table, where 3 columns are used in the where clause. The possible combinations can be

    1> Col1

    2> Col2

    3> Col3

    4> Col1 + Col3

    5> Col2 + Col3

    Could you please suggest me what kind og indices should I create on this table?

    What I think regarding the indices is as follows,

    Option 1

    1>Col1

    2>Col2

    3>Col3

    Option 2

    1>Col1 + Col3

    2>Col2 + Col3

    Option 3

    1>Col1 + Col3

    2>Col2 + Col3

    3>Col3

    Please suggest me the best among these ones.

  • January 16, 2012 at 6:16 am

    #1434026

    http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

    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

  • January 16, 2012 at 6:41 am

    #1434044

    snigdhandream (1/16/2012)

    Option 3

    1>Col1 + Col3

    2>Col2 + Col3

    3>Col3

    This seems a reasonable starting point (it covers all the query examples you gave). You will obviously want to test that they are actually useful in practice; only you know the distribution of values (highly selective indexes are generally most useful) and how many queries would benefit. Each index adds a small amount of overhead to inserts, updates, and deletes, so bear that in mind too.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 16, 2012 at 12:16 pm

    #1434223

    I did similar research last week for a table on two columns not 3. Essentially I had 3 queries like this

    Query 1) select col_pk from tab1 where col1=123

    Query 2) select col_pk from tab1 where col2=456

    Query 3) select col_pk from tab1 where col1= 123 and col2 = 456

    I tried the following two combinations.

    Option 1) Index on col1 and then another index on col2

    Option 2) One index on (col1, col2)

    Using option 1, all three select queries resulted in index seeks. The 3rd select query would in fact first do 2 index seeks and then merge the result set.

    But if I used option 2 then the Query 2 performed an INDEX SCAN on the composite index. It would depend on the actual queries that are involved but if the above situation is what you have then I would create individual indexes on each column involved.

    Check the execution plans for each query using different indexing options and see what works best.

    http://saveadba.blogspot.com/

    Blog
    http://saveadba.blogspot.com/

  • January 16, 2012 at 12:23 pm

    #1434227

    savethytrees (1/16/2012)

    But if I used option 2 then the Query 2 performed an INDEX SCAN on the composite index.

    Naturally. An index on (col1, col2) cannot support a seek on a col2 value alone. The three indexes in option 3 (of the original post) support seeks on all five example queries.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 17, 2012 at 2:21 am

    #1434464

    Thanks a lot guys for your responses!

    I tested every option, the third one only gives an index seek for every required search condition. the second one gave me either index scan/table scan for one or other search condition. The first option gave index seek and then a merge join... I think I will go with the third option, but I am worried about the index size now. where do we need to compromise? index size or index seek!!!

  • January 17, 2012 at 3:13 am

    #1434474

    Unless you're talking about indexes on hundreds of byte-wide char columns, for three indexes size should not be a concern. In general, one would go for a smaller number of wide indexes over a larger number of narrow ones. Narrow nonclustered indexes are in general less useful than wide ones.

    But it's the old tradeoff, performance for size. If size is a major, major concern and performance is no concern at all, drop all your indexes, but that's extremely unlikely to be the option taken in any reasonable system

    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

  • January 17, 2012 at 5:56 am

    #1434542

    Thank you Sir!

    As you said, that's an old tradeoff :). I will have to monitor the index size and the performance for couple of weeks I guess, to figure a way out.

  • January 17, 2012 at 6:01 am

    #1434545

    Also note that narrow indexes may well be useless - not used by SQL because they are not selective and not covering - and so you lose on both counts, you have indexes bloating space but they are not been used for anything.

    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 9 posts - 1 through 8 (of 8 total)

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