What happens when you add an index toi a table? Think about it for a moment. Every time you modify a record, (insert, update or delet), then every index in that table will also have to be modified. Perhaps I am hanging onto this topic a little too long, but, this is really important when it comes to tuning the performance of your database.
So, I have spent a lot of time telling you to be careful of how many indexes you create on a table. Today I want to look at one way to reduce the number of indexes you maintain in a database through index consolidation. Index consolidation is the process of creating indexes with multiple columns in the key. They do not necessarily perform as well as multiple indexes with a single column in the key. But, on tables where there is a lot of constant modification of data, consolidated indexes can increate the performance during modifications, while still performing better for queries than a table scan.
Index consolidation can be done by analyzing the queries used against a table being optimized. Reviewing joins and filter criteria can reveal columns used together consistently. For example if you have a PurchaseOrderDetail table, it will almost always be queried against the PurchaseOrderId, which is used to join it to the PurchaseOrder table. You may find it is often queried against the ProductId column from the Product table. This can be a scenario where consolidation helps your query. If you create an index on PurchaseOrderId, ProductId, that index may be used to query either column.
If you wish to query against the ProductId column, with a query to find any PurchaseOrder with a specific ProductId, you can still do that using the consolidated index. You can create a where clause like WHERE PurchaseOrderId > 0 AND ProductId = 245. This is where the index has less value than having an index on only the ProductId. When searching for the ProductId = 245 in the index, it will have multiple places inside the index where that ProductId may exist. It must search for ProductId 245 inside every distinct purchase order.
What you can observe from this example is that the more line items contained in a purchase order, the more efficient this composite index will be when used to only locate ProductId. If the average purchase order has two or less records, this composite index won’t provide much value over a simple table scan. For this reason, it is important to review the queries being executed against your table. The easy choice for consolidation are those query patterns that repeat often. iIf you see multiple queries using the same columns over and over, consolidate those columns into a single index. You may have to modify the queries so they specify the columns in the same order to help the query analyzer to select your new composite index more easily.
With your new composite index in place, you may now delete the individual indexes, resulting in less records that must be maintained when the table contents are modified. Be aware that this is not a task for the weak of heart, and not something you will want to play with in a production system.
Cheers,
Ben