Post reply

Index, one index per column or per column combination?

  • November 3, 2010 at 6:56 am

    #228593

    We're about to improve the performance of our database and I have started to read and learn about indexies but there is one question that I really canät find an answer on. Shall I create one index per column on a table or shall I create indexes with the combinations WHERE's that are most commonly used?

    Example:

    TABLE1 with (C1, C2, C3, C4,C5) where we search much on C2, C4 and C5 in different combinations. Is the best way to create three indexes with only one column in each, or X indexes with the combinations on C2, C4 and C5?

    Option 1:

    CREATE NONCLUSTERED INDEX [X_1] ON TABLE1 (C2 ASC)

    CREATE NONCLUSTERED INDEX [X_2] ON TABLE1 (C4 ASC)

    CREATE NONCLUSTERED INDEX [X_3] ON TABLE1 (C5 ASC)

    Option 2:

    CREATE NONCLUSTERED INDEX [X_1] ON TABLE1 (C2 ASC, C4 ASC, C5 ASC)

    CREATE NONCLUSTERED INDEX [X_2] ON TABLE1 (C4 ASC, C2 ASC, C5 ASC)

    CREATE NONCLUSTERED INDEX [X_3] ON TABLE1 (C2 ASC, C5 ASC, C4 ASC)

    So waht is best, Option 1 or option 2?

    Regards

    Daniel H

  • November 3, 2010 at 7:08 am

    #1245963

    Here's a great guide on the subject:

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

    -- Gianluca Sartori

  • November 3, 2010 at 7:48 am

    #1245994

    In addition, check out these two. I doubt that all three of the indexes in option 2 are required.

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

    http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

    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

  • November 4, 2010 at 5:34 am

    #1246459

    One of the keys to indexing is to watch the leading edge, the first column. You really need to avoid, as much as humanly possible (I'd say eliminate, but that's not entirely true) duplicating indexes that have the same leading edge. That's why one of the two of the indexes in Example 2 is probably not needed.

    Read Gail's blogs, but I'd also suggest picking up a copy of Kalen Delaney's Inside SQL Server to really understand what's happening and how the indexes are structured.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 5, 2010 at 2:05 pm

    #1247316

    If you are planning on doing some tuning and you are just learning about indexes, PLEASE do yourself a HUGE favor and hire a professional out for a few days or a week to review your stuff with you and mentor you on how to do tuning. Tremendous ROI there if you get a good tuner on board!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 5 posts - 1 through 4 (of 4 total)

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