Composite Index or individual indices?

Question

Post reply

Composite Index or individual indices?

snigdhandream

Mr or Mrs. 500

Points: 558
More actions
January 16, 2012 at 5:45 am

#262824

Hi,
I have one table, where 3 columns are used in the where clause. The possible combinations can be
1> Col1
2> Col2
3> Col3
4> Col1 + Col3
5> Col2 + Col3
Could you please suggest me what kind og indices should I create on this table?
What I think regarding the indices is as follows,
Option 1
1>Col1
2>Col2
3>Col3
Option 2
1>Col1 + Col3
2>Col2 + Col3
Option 3
1>Col1 + Col3
2>Col2 + Col3
3>Col3
Please suggest me the best among these ones.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

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

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

snigdhandream (1/16/2012)
Option 3
1>Col1 + Col3
2>Col2 + Col3
3>Col3

This seems a reasonable starting point (it covers all the query examples you gave). You will obviously want to test that they are actually useful in practice; only you know the distribution of values (highly selective indexes are generally most useful) and how many queries would benefit. Each index adds a small amount of overhead to inserts, updates, and deletes, so bear that in mind too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

savethytrees SSCommitted Points: 1968 More actions · Answer 3

I did similar research last week for a table on two columns not 3. Essentially I had 3 queries like this

Query 1) select col_pk from tab1 where col1=123

Query 2) select col_pk from tab1 where col2=456

Query 3) select col_pk from tab1 where col1= 123 and col2 = 456

I tried the following two combinations.

Option 1) Index on col1 and then another index on col2

Option 2) One index on (col1, col2)

Using option 1, all three select queries resulted in index seeks. The 3rd select query would in fact first do 2 index seeks and then merge the result set.

But if I used option 2 then the Query 2 performed an INDEX SCAN on the composite index. It would depend on the actual queries that are involved but if the above situation is what you have then I would create individual indexes on each column involved.

Check the execution plans for each query using different indexing options and see what works best.

http://saveadba.blogspot.com/

Blog
http://saveadba.blogspot.com/

Paul White SSC Guru Points: 150467 More actions · Answer 4

savethytrees (1/16/2012)
But if I used option 2 then the Query 2 performed an INDEX SCAN on the composite index.

Naturally. An index on (col1, col2) cannot support a seek on a col2 value alone. The three indexes in option 3 (of the original post) support seeks on all five example queries.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

snigdhandream Mr or Mrs. 500 Points: 558 More actions · Answer 5

Thanks a lot guys for your responses!

I tested every option, the third one only gives an index seek for every required search condition. the second one gave me either index scan/table scan for one or other search condition. The first option gave index seek and then a merge join... I think I will go with the third option, but I am worried about the index size now. where do we need to compromise? index size or index seek!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Unless you're talking about indexes on hundreds of byte-wide char columns, for three indexes size should not be a concern. In general, one would go for a smaller number of wide indexes over a larger number of narrow ones. Narrow nonclustered indexes are in general less useful than wide ones.

But it's the old tradeoff, performance for size. If size is a major, major concern and performance is no concern at all, drop all your indexes, but that's extremely unlikely to be the option taken in any reasonable system

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

snigdhandream Mr or Mrs. 500 Points: 558 More actions · Answer 7

Thank you Sir!

As you said, that's an old tradeoff :). I will have to monitor the index size and the performance for couple of weeks I guess, to figure a way out.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Also note that narrow indexes may well be useless - not used by SQL because they are not selective and not covering - and so you lose on both counts, you have indexes bloating space but they are not been used for anything.

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