Post reply

composite indexes... higher cardinality first or lowest?

  • December 31, 2004 at 10:13 am

    #86190

    I'm creating an index on a table. The table is selected from by dozens of different sprocs, funcs and views. All invariably have the same 3 columns in the where clause. Currently, they're filtered in different orders from object to object,

    (where TimeFrom = xxx and instID = @instid and enumID = @enumid,

    or enum then inst then time, etc...)

    I'd like to set all objects that reference this table to do so with the same order of columns in their where clause, then create an index on the table in the same order. My question is this... Is it better to create the index by naming the most selective (highest cardinality) first? or is it better to name the lowest cardinality index first?

    lowest: enumID (about 80 possible values)

    middy: instID (about 20000 possible values)

    hightest: TimeFrom (millions of possible values)

    which is better?

    create index on mytable ([enumID],[instID],[TimeFrom])

    or

    create index on mytable ([TimeFrom],[instID],[enumID])

    thanks

  • December 31, 2004 at 11:29 am

    #535533

    Highest cardinality first is better.  Is your TimeFrom column a dateTime field?  I've noticed SQL not using the index on a dateTime field.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • December 31, 2004 at 11:32 am

    #535534

    Yes, it is a datetime field.

    Are you saying SQL will ignore indexes with a datetime field as the first column in the index? I have evidence otherwise in my instance (standard edition, 8.00.818).

  • December 31, 2004 at 11:34 am

    #535535

    I'm not sure if it happens all of the time.  I have seen it happen, but maybe it is because I had to use > instead of =.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • December 31, 2004 at 11:37 am

    #535536

    Highest cardinality first. I'm going to make it so. Thanks.

    I'm a grasshopper!

  • January 3, 2005 at 2:26 am

    #535581

    Here's an interesting quote from "Inside SQL Server 2000" on this topic:

    You should put the most selective columns leftmost in the key of nonclustered indexes. For example, an index on emp_name,emp_sex is useful for a clause such as WHERE emp_name = 'Smith' AND emp_sex = 'M'. But if the index is defined as emp_sex,emp_name, it isn't useful for most retrievals. The leftmost key, emp_sex, cannot rule out enough rows to make the index useful. Be especially aware of this when it comes to indexes that are built to enforce a PRIMARY KEY or UNIQUE constraint defined on multiple columns. The index is built in the order that the columns are defined for the constraint. So you should adjust the order of the columns in the constraint to make the index most useful to queries; doing so will not affect its role in enforcing uniqueness.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 3, 2005 at 7:20 pm

    #535690

    Read this http://www.microsoft.com/sql/techinfo/tips/development/queryopstats.asp

    It also tends to be more people put stock in composite indexes than they need to so be carefull that you are truely make a usefull index.

Viewing 7 posts - 1 through 6 (of 6 total)

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