Click here to Skip to main content
15,795,712 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
The production database where I work is very large. There are roughly less than 10 tables that are taking up 90% of the database size.

One table is roughly 25 gig in size and the indexes on this one table are 132 gig! Roughly 44,415,774 rows, 121 columns and 48 indexes. All the indexes are non-unique and non-clustered.

There are what appears to be several overlapping indexes. Example:
Index 1: DueDate
Index 2: WCNum
Index 3: composite that is DueDate + WCNum.

For my own knowledge, would the composite DueDate + WCNum still cover queries filtered by DueDate or WCNum only? Are all 3 indexes truly needed or would the composite cover all there scenarios?


1 solution

Index 1 would be redundant because of index 3, however, you would still need Index 2.
Share this answer
Jon_Boy 10-Jul-14 14:06pm    
Why would Index 1 be redundant and not #2?

I found this on stackoverFlow:
Think of it this way: a compound index gives you rapid look-up of the first field in a nested set of fields followed by rapid look-up of the second field within ONLY the records already selected by the first field, followed by rapid look-up of the third field - again, only within the records selected by the previous two indices.

Is that the correct reason why the first is not needed? This would be expanded out dependent on the # of columns?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900