What is a covering index, and why does it make a query faster?
A covering index is one that holds every column that the query needs, so the engine can answer the question without going to the table at all.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount_cIMAL(10, 2) NOT NULL
);
-- The index holds the customer and the amount, so the query
-- below never needs the table.
CREATE INDEX idx_orders_covering ON orders (customer_id, amount);
So why does that make a query faster? Because the usual shape of an index lookup is two steps. The engine finds the matching entries in the index, and then it follows the pointer in each entry to the row in the table. When the index has every column that the query needs, the second step is not needed at all, and the engine reads fewer pages of the disk.
There is one more condition, and it is the one that catches people out. The engine can only use this shortcut if it is sure that the row is really there, so it has to check that the row has not been deleted or changed. That check usually happens against a reference that every row carries, and that reference has to be in the index too. The engines handle this for you, which is why you do not have to think about it, but it is the reason a covering index is a little bigger than the columns you asked for.
The next two questions in this topic show a query that is served entirely from the index and one that is not.