Composite and covering indexes
Indexes on several columns: why their order decides which queries they serve, and when the table need not be read at all.
5 minute lesson
Sorted like a phone book
A composite index covers several columns. It is sorted by the first column, and within equal values by the second. A phone book works the same way: by surname, then by first name. It finds all the Millers at once. It is no help in finding everyone called Ana.
CREATE INDEX orders_customer_date
ON orders (customer_id, created_at);The first column leads
- customer_id = 7Servedone stretch of the index
- customer_id = 7 and a date rangeServed besta part of that stretch
- A date range aloneNot servedthose rows are scattered all over the index
A table has one index, on (country, city). There is no other index.
Which query can it not serve well?
- Rows where the city is Lyon
- Rows where the country is France
- Rows in France whose city is Lyon
- Rows in France, sorted by their city
Show the answer
- Rows where the city is Lyon
The index is sorted by country first. Lyon appears under France, but a search for a city alone does not know which country to look under, and cities called Lyon could sit anywhere in the index. The three other queries all fix the country, so they read one connected stretch.
A composite index needs its first column fixed. A condition on the second column alone cannot use it well.
When the index is the answer
CREATE INDEX orders_customer_date
ON orders (customer_id, created_at)
INCLUDE (total);Normally an index only points to rows, and the database then fetches each row from the table. If the index already contains every column the query asks for, that second step is skipped. Such an index is a covering index for the query. INCLUDE adds columns just to be read, without sorting by them.
An index is on (customer_id, created_at) and includes total. A query asks for the created_at and total of customer 7's orders.
Why can the database answer without reading the table?
- Every column that the query needs is in the index
- The query asks for the orders of one customer only
- The table is small enough to be kept in memory
- The index is on two columns and not just on one
Show the answer
- Every column that the query needs is in the index
The query filters by customer_id and returns created_at and total, and all three are stored in the index. Nothing remains to fetch from the table, so the lookup of each row there, usually the most expensive part, is skipped. If the query asked for one more column, such as the status, the table would be needed again.
If the index contains every column a query uses, the table is not read at all.
The price of putting more in
- A bigger index. Every included column is stored a second time.
- More writes. A change to an included column now updates the index as well.
- Less memory for the rest. A fat index pushes other data out of the cache.
An index is on (customer_id, created_at). Which queries can use it, and how should the columns of such an index be ordered?
Show the answer
Queries that fix customer_id, with or without a condition on created_at, because the index is sorted by customer first and by date within a customer. A query on the date alone cannot use it well: those rows are spread over the whole index. Put the columns compared for equality first, and the column used for a range or a sort last.
Recap
- A composite index is sorted by its first column, then its second. The first column must be fixed.
- Order the columns: equality first, then the range or sort column.
- An index that holds every column a query needs answers it without reading the table.