Query Tuning Patterns

Covering Index

Satisfy a Query from the Index

A covering index contains both the filtered column and the returned column, so the table row may not need to be read.

Program

Play the script to choose a ticket status and see the plan for a covering-index query.

covering_index_plan.sql 
CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);
INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Nia');
CREATE INDEX idx_tickets_status_owner ON tickets(status, owner);
EXPLAIN QUERY PLAN WITH params(wanted_status) AS (VALUES ()) SELECT owner FROM tickets WHERE status = (SELECT wanted_status FROM params) ORDER BY owner;

CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);
INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Nia');
CREATE INDEX idx_tickets_status_owner ON tickets(status, owner);
EXPLAIN QUERY PLAN WITH params(wanted_status) AS (VALUES ()) SELECT owner FROM tickets WHERE status = (SELECT wanted_status FROM params) ORDER BY owner;
covering index The index stores `status` and `owner`, the two columns this query needs.
ORDER BY `ORDER BY owner` matches the index order after the selected status.
projection Returning only `owner` lets the index cover the query.