A covering index is a fantastic query performance optimization.
An index covers a query when the index has all the data needed to
execute the query, so the server can retrieve the query’s data
without reading any rows or documents. Covered queries are
usually SELECT queries, but in addition to reads, indexes can
cover portions of what a write query needs to do its work.
In this article I’ll explain what a covering index is in a bit
more detail; the technical implementation and caveats in MySQL,
PostgreSQL, and MongoDB; and how to check that a query is using a
covering index.
What’s a Covering Index?
A covering index, or index-covered query, doesn’t refer just to
an index. Instead, it’s the combination of a query and
an index: the index covers the query. An index that covers one
query might not cover another query, so “covering” doesn’t solely
describe the index, …
[Read more]