Index Help: Use one or two indexes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Index Help: Use one or two indexes

Author

Topic

doverton1024
Starting Member

2 Posts

Posted - 2010-09-16 : 08:28:40

Assuming the following:

Table with columns 1 (PK), 2, 3, 4, 5, and 6
Query 1: select 5 from Table where 2 = x and 3 = y
Query 2: select 5, 6 from Table where 2 = x and 3 = y and 4 = z

Do you suggest one index:
2, 3, 4 including 5 and 6
Or two indexes
2, 3, 4 including 5 and 6
2, 3 including 5

I understand that the one will cover both queries, but will the second index perform better? I also understand that with indexes it always depends on the situation, but if you were to say what would happen 51% of the time which would you recommend? Thanks for the help.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-09-16 : 10:38:50

imo...index to cover columns 2,3,4 only...index seek + a bookmark lookup.

2,3,4,5,6....index far too wide => storage costs higher.
index seek becomes an index scan (albeit with 1 record returned)

doverton1024
Starting Member

2 Posts

Posted - 2010-09-16 : 10:59:06

Would your opinion change if I had said the following:

Table with columns 1 (PK), 2, 3, 4, 5, 6, 7, 8, and 9

Didn't mean to have the index cover the whole table.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-16 : 11:34:33

The first. Having two indexes is just wasting space (the second is completely redundant), increasing index maintenance time, insert/update/delete overhead, backup time, etc.

There are some really exceptional cases where you might want the second option, but I'd say 99% of the time go for the first.

--
Gail Shaw
SQL Server MVP

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-09-17 : 09:51:50

Any chance you could bulk generate (50k-100k etc)some test data and TEST both theories?
remember to clear cache, etc in between execution timings.

theory is all fine - practical exercises are the most educational.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-17 : 10:40:49

Are you asking me or Doverton?

--
Gail Shaw
SQL Server MVP

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-09-21 : 03:41:15

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources