Advanced indexes: composite, covering, ignored index

Master the leftmost-prefix rule, covering indexes, and the pitfalls that make an index invisible to the planner.

11 min · 3 questions

A composite index orders rows across multiple columns, in the declared order. The decisive rule is the leftmost prefix: an index on (a, b) can serve a filter on a, or on a and b, but never on b alone. The column order is therefore a design choice, not a detail. First create the dataset below.

SQL

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000),
  (4, 'Edsger', 1, 90000);

Test table for composite indexes.

Let's create an index on (department_id, salary). A filter that starts with department_id triggers a SEARCH. But a filter on salary only breaks the leftmost prefix: the planner cannot jump straight to the right rows and falls back to a SCAN. Compare the two plans.

SQL

CREATE INDEX idx_dept_salary
  ON employees (department_id, salary);

-- Leftmost prefix present: index used
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE department_id = 1
  AND salary > 60000;
-- SEARCH ... USING INDEX idx_dept_salary

-- Leading column missing: index ignored
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE salary > 60000;
-- SCAN employees

The same index serves one case but not the other.

A covering index contains every column read by the query. The planner then answers directly from the index, without opening the table: that's the meaning of USING COVERING INDEX. Here, the index (department_id, salary) covers a query that reads only those two columns. Remember that the id (the rowid) is implicitly present in every index, so it too is covered for free.

SQL

-- Every column read is in the index
EXPLAIN QUERY PLAN
SELECT department_id, salary
FROM employees
WHERE department_id = 1;
-- SEARCH employees USING COVERING INDEX
-- idx_dept_salary (department_id=?)

The index answers alone; the table isn't opened.

An existing index can be ignored. The classic pitfalls: applying a function to the column (WHERE lower(email) = ... makes the predicate non-sargable), a LIKE with a leading wildcard ('%word'), a type-affinity mismatch (comparing a TEXT column to a number), or too low a selectivity. On a tiny table, the planner will even deliberately prefer a SCAN, faster than hopping from index to table.

For a filter on an expression, create an expression index: CREATE INDEX ix ON users(lower(email)) becomes usable again by WHERE lower(email) = ?. Finally, note that SQLite generally uses only one index per table in a simple query (the OR optimization being an exception). When in doubt, settle it with EXPLAIN QUERY PLAN.

Knowledge check

Make sure you remember the key points of this lesson.

With a composite index on (department_id, salary), which query CANNOT use it?
- WHERE department_id = 1
- WHERE department_id = 1 AND salary > 60000
- WHERE salary > 60000
- WHERE department_id = 1 AND name = 'Ada'
What is a covering index?
- An index that contains every column read, avoiding opening the table
- An index that covers several tables at once
- An index automatically recreated after each INSERT
- A synonym for a unique index on the primary key
Which of these conditions prevents the use of a simple index on email?
- WHERE email = 'a@b.co'
- WHERE email > 'm'
- WHERE lower(email) = 'a@b.co'
- WHERE email IN ('a@b.co', 'c@d.co')