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.

wanted_status
covering_index_plan.sql
Replay: real traced execution (multi-file project)
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 ('open')) 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 ('closed')) SELECT owner FROM tickets WHERE status = (SELECT wanted_status FROM params) ORDER BY owner;

  1. tables ← 1 row

    1CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);2INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Nia');
    values this step1 rowtables

  2. tickets ← 4 rows

    1CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);2INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Nia');3CREATE INDEX idx_tickets_status_owner ON tickets(status, owner);
    values this step4 rowstickets

  3. indexes ← 1 row

    2INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Nia');3CREATE INDEX idx_tickets_status_owner ON tickets(status, owner);4EXPLAIN QUERY PLAN WITH params(wanted_status) AS (VALUES ('open')) SELECT owner FROM tickets WHERE status = (SELECT wanted_status FROM params) ORDER BY owner;
    values this step1 rowindexes

  4. result ← 5 rows

    3CREATE INDEX idx_tickets_status_owner ON tickets(status, owner);4EXPLAIN QUERY PLAN WITH params(wanted_status) AS (VALUES ('open')) SELECT owner FROM tickets WHERE status = (SELECT wanted_status FROM params) ORDER BY owner;
    values this step5 rowsresult

  1. tables ← 1 row

    1CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);2INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Nia');
    values this step1 rowtables

  2. tickets ← 4 rows

    1CREATE TABLE tickets (id INTEGER, status TEXT, owner TEXT);2INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Nia');3CREATE INDEX idx_tickets_status_owner ON tickets(status, owner);
    values this step4 rowstickets

  3. indexes ← 1 row

    2INSERT INTO tickets VALUES (1, 'open', 'Ada'), (2, 'closed', 'Lin'), (3, 'open', 'Mira'), (4, 'closed', 'Nia');3CREATE INDEX idx_tickets_status_owner ON tickets(status, owner);4EXPLAIN QUERY PLAN WITH params(wanted_status) AS (VALUES ('closed')) SELECT owner FROM tickets WHERE status = (SELECT wanted_status FROM params) ORDER BY owner;
    values this step1 rowindexes

  4. result ← 5 rows

    3CREATE INDEX idx_tickets_status_owner ON tickets(status, owner);4EXPLAIN QUERY PLAN WITH params(wanted_status) AS (VALUES ('closed')) SELECT owner FROM tickets WHERE status = (SELECT wanted_status FROM params) ORDER BY owner;
    values this step5 rowsresult
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.