Search code examples
sqlsql-serversql-server-2008

Should these indexes be merged?

Here are 2 examples with 2 indexes each, assuming all of these indexes have high usage should they be merged or kept separate? My first thought is only example #2 should have its indexes merged.

Example #1:

CREATE INDEX [ix_Orders_1] 
ON [dbo].[Orders] ([cancelled], [approved], [denied], [saved])   
INCLUDE ([total], [subTotal], [tax])

CREATE INDEX [ix_Orders_2] 
ON [dbo].[Orders] ([cancelled], [isQuote]) 
INCLUDE ([dateStamp], [userId])

Example #2:

CREATE INDEX [ix_Products_1] 
ON [dbo].[Products] ([itemNumber])  
INCLUDE ([altItemNumber], [description]) 

CREATE INDEX [ix_Products_2] 
ON [dbo].[Products] ([itemNumber], [showPrice]) 
INCLUDE ([level_1], [level_2], [level_3], [level_4])

In example #1 since the second columns in both differ, I assume they should be kept as 2 indexes.

In example #2, could the indexes be merged into the following and keep its performance?

CREATE INDEX [ix_Products_3] 
ON [dbo].[Products] ([itemNumber], [showPrice]) 
INCLUDE ([altItemNumber], [description], [level_1], [level_2], [level_3], [level_4])
Solution

  • The composition of an index key is actually a vector, which means that the order of the columns of this index key is very important for it to be efficient in searching, otherwise it will be ignored... But the "strength" of the search is less on the final columns of the key than the very first ones, because each column that follows the previous one over-filters the already filtered results... This means that the last column filtering the least, in the case of a multi-column key can be ignored if it is put in the INCLUDE clause specific to SQL Server and recently to PostGreSQL...

    In other words, in your example #1, you could try the following synthesis:

    CREATE INDEX [ix_Orders_1]
ON [dbo].[Orders] ([cancelled]))
INCLUDE ([total], [subTotal], [tax], [approved], [denied], [saved], [dateStamp], [userId])

    But I'm not sure that it is really useful. It's up to the optimizer to decide and it will only do so if the criterion on "Cancelled" is very filtering, which I doubt since it is probably a boolean...

    In the second example#2, you have you yourself found the synthesis.