What is a covering index?
Options
- An index that holds every column a query needs, so no table read (Correct option)
- An index that is created over all the tables of the database
- An index that also works as a backup of the indexed table
- An index that is protected with a password for each user
Answer
An index that includes every column a query needs, so the database can answer from the index without reading the table
Why
Normally an index finds the row location, then the database goes to the table to read the other columns (an extra step). If the index itself contains all columns the query selects and filters on, the table is never touched (an "index-only scan"), which is much faster.
Example: query SELECT email FROM users WHERE country = 'ES' is covered by an index on (country, email).
The cost: bigger indexes and slower writes.
Remember
Covering index = the index alone answers the query.