Post reply

order of column in compound index

  • June 4, 2002 at 4:40 pm

    #104946

    I think the order of column in a compound

    index is very important--i.e. it should be

    defined as (high_selectiviey_column, less_selectivity_column) rather than the other way around or as some people suggests

    'does not matter'.... Can someone who knows

    the internals of how index trees are built

    shed light in this?

    i.e. much better performance if index is

    defined as (employee_id, department_id),

    rather the other way around.

  • June 4, 2002 at 5:23 pm

    #430338

    The suggested method is highest selectivity first to lowest, the reason is that statistics are stored only for the first column. So in order for the query manager to properly select an index especially if you want it used as opposed to others is the you should put the most unique item first. Example if you have a bit field in your index (bad anyway), consider what it will think of the selectivity of that index and it's usefullness if only the stats for the bit column are stored. However, some will say that employee_id should be it's own index and that department_ id should be yet another. If this is a clustered index it will consider department_id more selective than say team_id because the stats from employee_id being stored states that index is the better choice. So in this case the combined index is not actually better but fortunately the query manager may actually use both indexes and use intersection to determine the fastest way to get the data so now the index may offer some bennifit. A little braind dead here so I hope this makes sense. Breakdown highest selectivity first if you want the index to be used more often.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

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

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