Covering indexes in SQL

When the index already holds every column the query wants, the table is never touched.

CREATE INDEX orders_cover
    ON orders (status, customer_id, total);

EXPLAIN QUERY PLAN
SELECT
    customer_id,
    SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

How it works

1. A covering index turns two lookups into one.
2. The plan says USING COVERING INDEX when this happens.
3. It costs storage: the columns are stored twice.

Keywords and builtins used here

• AS
• BY
• CREATE
• EXPLAIN
• FROM
• GROUP
• INDEX
• ON
• SELECT
• SUM
• WHERE

The run, in numbers

Lines

10

Characters to type

183

Tokens

35

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 23 seconds.

Type this snippet

Step 5 of 5 in Indexes, step 7 of 17 in Plans & performance.

← Previous Next →