9.5 What is a covering index?

In a book: Some indexes include a short summary next to each word. If the summary is all you need, you never open the book.

In the system: Normally an index lookup finds the matching entries and then fetches each row from the table to get the other columns. If the index already contains every column the query needs, the database can answer from the index alone. That's a covering index, giving an "index-only scan".

For "My orders", if the page only shows the order date:

SQLite's plan said SEARCH orders USING COVERING INDEX idx_user_time (user_id=?). PostgreSQL calls it an "Index Only Scan"; MySQL says "Using index".

Ways to make an index covering: add the needed columns to the composite index, or use an INCLUDE clause where the database supports it (PostgreSQL and SQL Server do) to carry extra columns in the leaf pages without sorting by them.

The trade-off: a wider index is bigger and costs more on every write. Cover the hot queries, not everything.

Memory code: "If the index has the answer, don't open the book."