I created a lot of normal indexes in my table. My colleage is suggesting that I use composite indexes, but couldn't give a clear reason.
How would it be different from Composite Index? Is there any performance improvement?
asked
Advantages of Composite Index over Simple Index?
If you frequently query only a subset of columns in the table, then building a composite index on those columns will mean that the database is able to retrieve all the required data from the index rather than having to go to the table.
Also, there are cases where you will benefit from improved selectivity by indexing over two columns. For example, if you have a table containing both ORDERDATE and PRODUCTID, you may have many orders for the same day, and many orders for the same product, but relatively few orders for a given product on a given day.
The relative benefits of a composite index and two single column indexes will depend on the individual case. Composite indexes can use a lot of storage and be expensive to maintain, and are often abused as a quick-fix for poorly performing queries (where each index is added to fix one query, but as each slows down the rest of the system slightly the cumulative effect is poor), but are a very useful tool if used carefully.
