Query Tuning Patterns

Indexed Lookup

Read the Query Plan

EXPLAIN QUERY PLAN shows whether SQLite can use an index for a filtered lookup.

Program

Play the script to choose a category and inspect the query plan for the indexed search.

wanted_category
indexed_lookup_plan.sql
Replay: real traced execution (multi-file project)
CREATE TABLE products (id INTEGER, category TEXT, name TEXT);
INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');
CREATE INDEX idx_products_category ON products(category);
EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('tool')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;

CREATE TABLE products (id INTEGER, category TEXT, name TEXT);
INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');
CREATE INDEX idx_products_category ON products(category);
EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('book')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;

CREATE TABLE products (id INTEGER, category TEXT, name TEXT);
INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');
CREATE INDEX idx_products_category ON products(category);
EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('game')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;

  1. tables ← 1 row

    1CREATE TABLE products (id INTEGER, category TEXT, name TEXT);2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');
    values this step1 rowtables

  2. products ← 4 rows

    1CREATE TABLE products (id INTEGER, category TEXT, name TEXT);2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');3CREATE INDEX idx_products_category ON products(category);
    values this step4 rowsproducts

  3. indexes ← 1 row

    2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');3CREATE INDEX idx_products_category ON products(category);4EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('tool')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;
    values this step1 rowindexes

  4. result ← 6 rows

    3CREATE INDEX idx_products_category ON products(category);4EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('tool')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;
    values this step6 rowsresult

  1. tables ← 1 row

    1CREATE TABLE products (id INTEGER, category TEXT, name TEXT);2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');
    values this step1 rowtables

  2. products ← 4 rows

    1CREATE TABLE products (id INTEGER, category TEXT, name TEXT);2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');3CREATE INDEX idx_products_category ON products(category);
    values this step4 rowsproducts

  3. indexes ← 1 row

    2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');3CREATE INDEX idx_products_category ON products(category);4EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('book')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;
    values this step1 rowindexes

  4. result ← 6 rows

    3CREATE INDEX idx_products_category ON products(category);4EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('book')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;
    values this step6 rowsresult

  1. tables ← 1 row

    1CREATE TABLE products (id INTEGER, category TEXT, name TEXT);2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');
    values this step1 rowtables

  2. products ← 4 rows

    1CREATE TABLE products (id INTEGER, category TEXT, name TEXT);2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');3CREATE INDEX idx_products_category ON products(category);
    values this step4 rowsproducts

  3. indexes ← 1 row

    2INSERT INTO products VALUES (1, 'tool', 'saw'), (2, 'book', 'sql'), (3, 'tool', 'drill'), (4, 'game', 'chess');3CREATE INDEX idx_products_category ON products(category);4EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('game')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;
    values this step1 rowindexes

  4. result ← 6 rows

    3CREATE INDEX idx_products_category ON products(category);4EXPLAIN QUERY PLAN WITH params(wanted_category) AS (VALUES ('game')) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;
    values this step6 rowsresult
EXPLAIN QUERY PLAN `EXPLAIN QUERY PLAN` returns SQLite's chosen access path.
index lookup The category index gives SQLite a direct path to matching rows.
parameter CTE The selectable `wanted_category` changes the lookup value without changing the query shape.