Anyone has experience in index covering

1. What is the technique of Index Covering (a.k.a. Covering Index)?
2. When considering overall performance, what ar开发者_Python百科e the advantages/disadvantages to their use?

The reasoning behind creating a covering index is so that all the columns that are required to ,be either output or are referenced in the where clause of your query, are present "within" the Index data structure (either as part of the index key or as an included column).

This in turn means that the database engine does not need to retrieve any additional database data pages in order to satisfy the needs of your query. In a nutshell, this means that in the vast majority of cases the query will be faster.

There is an excellent reference, SQL Server Optimization that provides an explanation with example of a covering index in SQL Server.

Here is a nice discussion on MySQL: How to exploit MySQL index optimizations

Now when considering disadvantages, that's an interesting question, suppose we had a very wide table and in order to create a covering index for your query you had to incorporate say 20 large data type columns, your index could quickly become quite large. You would then need to weigh up the performance gain in relation to the index maintenance and table insert/update costs.It would be one of those, it depends (dependant on workload patterns, data used etc.) cases.

IN addition to Johns answer:

Advantage: Faster access speed if the query can be answered from the covered fields as the access to the row is not needed.

Disadvantage: Slower update speed as more data in indices needs to be updated.