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.

indexed_lookup_plan.sql 
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 ()) 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 ()) 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 ()) SELECT name FROM products WHERE category = (SELECT wanted_category FROM params) ORDER BY name;
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.