June 4, 2002 at 4:40 pm
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
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