How do you read EXPLAIN QUERY PLAN, and which common query shapes stop SQLite using an index?

13.9.1 · L3 · Interview

Predict first

Which of these queries scan the whole table?

import Foundation
import SQLite3

final class DB {
    var h: OpaquePointer?
    init(_ path: String = ":memory:", busy: Int32 = 0) { sqlite3_open(path, &h); sqlite3_busy_timeout(h, busy) }
    deinit { sqlite3_close(h) }
    @discardableResult func exec(_ sql: String) -> String? {
        var err: UnsafeMutablePointer<CChar>?
        if sqlite3_exec(h, sql, nil, nil, &err) != SQLITE_OK { defer { sqlite3_free(err) }; return String(cString: err!) }
        return nil
    }
    func rows(_ sql: String) -> [[String]] {
        var st: OpaquePointer?; var out: [[String]] = []
        guard sqlite3_prepare_v2(h, sql, -1, &st, nil) == SQLITE_OK else { return [["error: " + String(cString: sqlite3_errmsg(h))]] }
        defer { sqlite3_finalize(st) }
        while sqlite3_step(st) == SQLITE_ROW {
            out.append((0..<sqlite3_column_count(st)).map { i in sqlite3_column_text(st, i).map { String(cString: $0) } ?? "NULL" })
        }
        return out
    }
}

let db = DB()
db.exec("CREATE TABLE user(id INTEGER PRIMARY KEY, email TEXT UNIQUE, age INTEGER)")
db.exec("CREATE INDEX idx_age ON user(age)")
for i in 1...100 { db.exec("INSERT INTO user(email, age) VALUES('u\(i)@x.test', \(i % 50))") }
for q in ["SELECT * FROM user WHERE id = 5",
          "SELECT * FROM user WHERE email = 'u5@x.test'",
          "SELECT * FROM user WHERE age = 30",
          "SELECT * FROM user WHERE age + 1 = 31",
          "SELECT * FROM user WHERE lower(email) = 'u5@x.test'",
          "SELECT * FROM user ORDER BY age"] {
    print(q, "=>", db.rows("EXPLAIN QUERY PLAN " + q).map { $0.last! }.joined(separator: "; "))
}

Only "age + 1 = 31" and "lower(email) = …" show SCAN user; the others SEARCH an index, and ORDER BY age scans using idx_age
All of them SEARCH an index
Only the ORDER BY query scans

Mental model. An index is the index at the back of a book. “Find every page about actors” is a quick lookup, but “find every page where the topic’s name in lowercase is actors” means you read the whole book.

EXPLAIN QUERY PLAN SELECT … returns one line per step, and the first word tells you the cost: SEARCH … USING INDEX is an index lookup (fast), SCAN reads the whole table (fine for tiny tables, a bug on a large one). In the check, WHERE id = 5 is SEARCH … USING INTEGER PRIMARY KEY, WHERE email = … uses the automatic index behind the UNIQUE constraint, and WHERE age = 30 uses idx_age. ORDER BY age is SCAN … USING INDEX idx_age: it walks the index in order so no separate sort step is needed.

Two shapes defeat an index: arithmetic or a function on the indexed column. WHERE age + 1 = 31 and WHERE lower(email) = 'u5@x.test' both show SCAN user, because the index stores age and email, not their transformed values. Rewrite them (age = 30), or create an index on the expression (CREATE INDEX i ON user(lower(email))) or store a normalised column. Other classic causes: a leading wildcard (LIKE '%x'), a type mismatch between the column and the value, and a composite index used in the wrong order (the leftmost columns must appear in the WHERE). Workflow: reproduce the slow query on realistic data, read the plan, add or reshape one index, re-check, and remember every index costs writes and space. Run ANALYZE so the planner has statistics, and use the plan in tests as a guard for hot queries.

A function or arithmetic on an indexed column prevents index use.

Common traps

Wrapping the indexed column in a function or arithmetic. The predicate stops matching the index; move the operation to the other side.
Adding indexes without checking the plan. An unused index only slows writes.

They might follow up with

What is a covering index? One that contains every column the query needs, so SQLite never touches the table.
How do I see timing, not just the plan? Measure with realistic data volumes; plans on ten rows say little.

How do you read EXPLAIN QUERY PLAN, and which common query shapes stop SQLite using an index?

Predict first

Common traps

They might follow up with

Related questions