Understanding SQL Indexes and Query Performance

Part 5 of 16 · ~3 min

Covering Indexes

The problem at the end of the last chapter was narrow and specific: the index knew which rows, but not what was in them, so it had to go ask the table 6,200 times. There is an obvious fix available, and it is obvious in a way that turns out to be productive — put the missing columns in the index.

An index that contains every column a query touches is a covering index for that query. "Covers" is literal: there is nothing left for the engine to go and find, so it never touches the table at all.

CREATE INDEX idx_emp_dept_covering ON Employees (dept, name, salary);

SELECT dept, name, salary FROM Employees WHERE dept = 'Facilities';

And the plan changes in one word, which is the word to look for:

SEARCH Employees USING COVERING INDEX idx_emp_dept_covering (dept=?)

COVERING. Every column the query asked for is inside the index, the scattered per-row trips back to the table are gone, and what remains is a descent plus a sequential walk along sorted leaves. On 6,200 matching rows that is the difference between a query you notice and one you do not.

Two pieces of precision before you start adding columns to everything.

Key columns and included columns are not the same thing. In (dept, name, salary), all three are key columns: they are part of the sort order, they all sit in the internal routing nodes as well as the leaves, and they all make the tree wider. Some engines — SQL Server with INCLUDE, Postgres with INCLUDE since version 11 — let you attach columns as leaf-only payload instead. Those columns are available for covering but take no part in the sort order and add no weight to the internal nodes, which is strictly what you want for a column you only ever display. SQLite and MySQL have no such clause, so on our schema every covering column has to be a key column, and that constraint is about to become the subject of the next chapter.

Covering is a property of a query, not a badge on an index. idx_emp_dept_covering covers the three-column query above. Add one more column to the SELECT list and it stops covering, silently, with no error and no warning — just the key lookups quietly coming back and the screen getting slower again. This is a real and common regression: somebody adds a field to a listing screen, nothing in the query looks alarming, and a covering index becomes an ordinary one. The cost is real too — a covering index duplicates more of the table than a narrow one, which means more storage and more work on every write. Reach for it on a genuinely hot query, which the pay-band screen is. Not as a reflex.

Now. We have an index on (dept, name, salary), which contains every column the pay-band screen needs. It should be perfect. Point the real query at it — the one with the salary range and the sort:

SELECT name, dept, salary
FROM Employees
WHERE dept = 'Facilities' AND salary BETWEEN 60000 AND 90000
ORDER BY salary;

SEARCH Employees USING COVERING INDEX idx_emp_dept_covering (dept=?)
USE TEMP B-TREE FOR ORDER BY

Still covering. But look at what it is actually doing: it seeks on dept=? and then stops seeking. The salary range is not in the seek at all, and there is now a second line — the engine is building a temporary B-tree at query time purely to sort the results, which is the sort we were hoping the index would have done for free.

We picked three correct columns and put them in the wrong order. That is the next chapter.