Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Single Column Indexes


Single Column Indexes

Author
Message
Naga02
Naga02
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3227
In the time I have been working at my current company as part of a DBA team, I have noticed that the standard seems to be to have many single column indexes on a table. Most of the larger tables have around 10 non-clustered indexes, each on a single column from the table. All the tables have a primary key (and clustered index) on the Identity column for the table.

The problem with this index setup that I see is very few queries in all the stored procedure code ever use just a single column in the WHERE clause. I have looked at a few query plans, it does appear that SQL Server uses just one of the single column indexes as a seek, then does a bookmark lookup.

To my knowledge, it would be better to find the top 3-5 queries using the table and create covering indexes for those queries. So most likely, each index would have 4-5 columns in it. From what I have seen in query plans, even if I reference only 1 or 2 columns from a 5 column index, it can still use that index.

So, I would welcome comments and opinions about this. Am I right in thinking that 5 indexes with 4 columns covering popular queries is better than 10 indexes each on a single column?

Thanks,

Nathan
GilaMonster
GilaMonster
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58341 Visits: 44717
websites2 (6/29/2009)
Am I right in thinking that 5 indexes with 4 columns covering popular queries is better than 10 indexes each on a single column?


In general, yes. The majority of the time, SQL will only use one index per table for a query, if there are three conditions in the where and 3 single column indexes, it's likely going to use the most selective, then look up to the clustered index, then filter on the other two conditions.

I wrote a blog post about index columns. It doesn't cover exactly this question, but it might help.
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-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


Naga02
Naga02
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3227
Hi Gail,

The article was very helpful. I wasn't aware that the order of the columns in the index mattered, but now I understand why.

So, my conclusion from that is, the advantage of having 10 indexes on a single column is that a larger number of queries will find an index it can use. The disadvantage being it will have to do checks against the index seek results for each other column filter in the query.

The advantage of 5 indexes with 4 columns each(the first column being different for each index) is that some queries (using all or some of the columns in index order) can find all the results in a single seek. The disadvantage is that now, for a query to use one of the 5 indexes, it must be referencing one of the 5 first columns, otherwise it cannot use any of the indexes.

Am accurate in saying that?

Also, there is another aspect of this issue we haven't talked about yet: Is there any difference in the maintenance of the two index structures? Will reindexing 10 indexes with single columns take longer than fewer indexes with more columns? Which index structure will slow down inserts more?

Any insight to this side of the issue would be appreciated too.

Thanks,

Nathan
GilaMonster
GilaMonster
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58341 Visits: 44717
websites2 (6/29/2009)
So, my conclusion from that is, the advantage of having 10 indexes on a single column is that a larger number of queries will find an index it can use. The disadvantage being it will have to do checks against the index seek results for each other column filter in the query.

The advantage of 5 indexes with 4 columns each(the first column being different for each index) is that some queries (using all or some of the columns in index order) can find all the results in a single seek. The disadvantage is that now, for a query to use one of the 5 indexes, it must be referencing one of the 5 first columns, otherwise it cannot use any of the indexes.

Am accurate in saying that?


Yes and yes

Also, there is another aspect of this issue we haven't talked about yet: Is there any difference in the maintenance of the two index structures? Will reindexing 10 indexes with single columns take longer than fewer indexes with more columns? Which index structure will slow down inserts more?


Of the top of my head, not sure, but the more places a column appears, the more places if has to be changed and the slower the change can be. That said, test it. I've never noticed a major performance degradation from 5 or 6 indexes. Once that gets to 15 or so, I start worrying.
Your system may be different, so set up some tests and see what the effect is.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search