One reader responds to yesterday’s editorial on using multiple indexes to solve a single query with a question. Michael asks, “Are you saying it ultimately better to have multiple single column indexes than to have multiple column indexes?”
When a query uses multiple indexes to support a query it can be helpful. However, it will not perform as well as a single complex index that solves the needs of the query. So, the answer is that you are balancing a number of things all at one time. Having many indexes on a table slows down insert, update, and delete commands. So, if the table is for OLTP, it is best to reduce the number of indexes. In this case, sometimes it helps to have a number of single purpose indexes that may be used together to solve a query.
If an OLTP table does not have queries using many different columns, then you may find that a few compound indexes are a better fit for resolving the needs of the common queries.
David provides some insight as to how a DBA managing a database answers this question.
Now, as you post about additional indexes, well that is a really good point, because there is usually more than one argument in the where clause. Here you can add the other indexes you think you may need. But a good DBA will monitor all of the index usage, and start to remove the unused ones and also find a way consolidate, if possible, the remaining indexes. The DBA can also see what columns are being used without an index to support the queries.
The whole point is to always be checking the indexes and see what is needed as well as what is not needed.
When you are working with OLAP databases, the better plan is to again monitor and build the compound indexes that solve the needs of the table. At that point you have to option of dropping or disabling indexes when modifying data, generally through an ETL process, and then rebuilding them after making all the data modifications, or simply updating with the indexes in place. You implementation will depend on how often you update your OLAP data, and the volume being applied.
This should provide you with a little more guidance regarding index management. Frankly, this is one of the key skills a DBA will bring to the table. So, if you have the time to build up your skills in this area it will have continuing value in your tool box.
Cheers,
Ben