Friday, November 28, 2014

How does a covering index on a database work?

Today I was having a conversation with a co-worker who lives in Italy. He said that Christmas in Italy starts on December 8 which is a holiday in Italy but he didn't know the English name for the holiday.

I went to Google and typed in "december 8 holiday italy" and got back this result:

I didn't need to  click on the result of the first (or any) item that turned up in the search result because all the information that I needed was right there: The Feast of the Immaculate Conception.

This is a covering index. When the results from the index alone can satisfy the information that you seek then they are said to be "covered in the index" and no further lookup is required.

In my search analogy I only made one request to the Google index and never opened the page that it pointed to.

In a database I would pull back all the information that I needed from the table's index and not have to retrieve the record from the table to complete the information search.

Leaving the land of Google analogy you might have a table with an index on userId. Finding that ID in the index allows the database to quickly locate the record in the table from which it can pull the name, for example.

If you had an index on both userId and userName then only the index would need to be searched if all you are after is userName and the second lookup in the table would not be needed.