SovranCode
SQL: Query, Model, and Analyze Data Query tuning patterns
This device
Course contentsQuery tuning patterns · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONEXPLAIN and query plans
NEXT LESSONBackups, restores, and migrations
Performance and administration · Lesson 27 155 min

Query tuning patterns

Indexes and plans are tools. Tuning is the habit: write the question with a clear grain, ask only for the columns you show, keep predicates in index order, page with a key instead of skipping rows, and never use DISTINCT to hide a broken join. Then explain the statement again.

Same catalog, named waste

Every runner starts from five customers and eight orders plus the indexes last two lessons taught. Alonzo has no orders on purpose. Tiny data still shows the shape of the waste. Production size makes that shape expensive. PostgreSQL may Hash Join where SQLite nested-loops; the patterns (covering lists, sargable ranges, keyset pages) still transfer.

Tune the question, then prove the recipe

A slow query is often a correct English sentence with a costly how. Tuning does not mean “add an index and hope.” It means: say what one result row is, drop work that does not serve that row, then use EXPLAIN QUERY PLAN as the before-and-after photo.

If the plan did not change, you did not tune the engine. If the rows changed, you tuned the meaning—usually by accident. Keep both checks.

Ask for the columns you show

SELECT * is a draft. Screens show email, not every future column. Unique email already indexes Ada. SELECT * SEARCHES that index, then visits the table for city and created. SELECT email can be USING COVERING INDEX: no heap trip.

Covering is last lesson's word used as a daily rule. Wide rows, blobs, and unused text columns make * worse than this catalog can show. The plan already names the extra visit.

SQL BROWSER RUNNER

Cover email instead of SELECT *

Same WHERE email, two SELECT lists, two plans.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

EXPLAIN QUERY PLAN
SELECT *
FROM customers
WHERE email = 'ada@example.com';

EXPLAIN QUERY PLAN
SELECT email
FROM customers
WHERE email = 'ada@example.com';

SELECT email
FROM customers
WHERE email = 'ada@example.com';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which plan still says COVERING, and which columns forced a table visit on SELECT *?

Keep the column naked so the index can jump

A predicate is sargable when the engine can search an ordered index with it. strftime('%Y', created) = '2026' computes a year for every row. idx_customers_created stores dates, not years. Expect SCAN.

Rewrite the year as a half-open range: created >= '2026-01-01' AND created < '2027-01-01'. Same customers, SEARCH on the date index. This is the same family as lower(email) and leading LIKE: wrap the column, hide the keys.

SQL BROWSER RUNNER

Replace strftime year with a date range

Plan the wrapped year, then the half-open 2026 range, then list those emails.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

EXPLAIN QUERY PLAN
SELECT email, created
FROM customers
WHERE strftime('%Y', created) = '2026';

EXPLAIN QUERY PLAN
SELECT email, created
FROM customers
WHERE created >= '2026-01-01'
  AND created < '2027-01-01';

SELECT email, created
FROM customers
WHERE created >= '2026-01-01'
  AND created < '2027-01-01'
ORDER BY created;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which plan SCANs, which SEARCHES idx_customers_created, and who is excluded as a 2025 signup?

Do not throw away skipped pages

LIMIT 2 OFFSET 5 means “walk in order, discard five, keep two.” SQLite shows SCAN even on an integer primary key: it still visits the skipped rowids. Page 50 of a busy shop is fifty pages of discarded work. New inserts also make OFFSET pages drift.

Keyset pagination remembers the last key you showed. WHERE order_id > 5 ORDER BY order_id LIMIT 2 is SEARCH using INTEGER PRIMARY KEY. The next request sends the new last id. This needs a stable unique sort (id, or (placed, order_id) if you sort by date).

SQL BROWSER RUNNER

Compare OFFSET skip with WHERE order_id > last

Same two orders (6 and 7): OFFSET 5 versus keyset after 5.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

EXPLAIN QUERY PLAN
SELECT order_id, sku
FROM orders
ORDER BY order_id
LIMIT 2 OFFSET 5;

SELECT order_id, sku
FROM orders
ORDER BY order_id
LIMIT 2 OFFSET 5;

EXPLAIN QUERY PLAN
SELECT order_id, sku
FROM orders
WHERE order_id > 5
ORDER BY order_id
LIMIT 2;

SELECT order_id, sku
FROM orders
WHERE order_id > 5
ORDER BY order_id
LIMIT 2;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Do both SELECTs return the same sku rows, and which plan SEARCHES rowid instead of scanning?

DISTINCT is not a join

A comma join of customers and orders builds 5 × 8 = 40 pairs. COUNT(DISTINCT email) can still say 5. SELECT DISTINCT email looks like a customer list and still scanned both tables as a product. That is last lesson's Cartesian, with a mop. Alonzo never ordered; DISTINCT still lists him because every customer was paired with every order.

Fix the grain: JOIN orders ON orders.customer_id = customers.customer_id when you want one row per order, or EXISTS / grouped customers when you want one row per customer who ordered. DISTINCT after a correct join is rare and honest (you really had duplicate facts).

SQL BROWSER RUNNER

Count the Cartesian, then DISTINCT, then a real join

pair_count, distinct emails, two plans.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

SELECT COUNT(*) AS pair_count
FROM customers AS c, orders AS o;

SELECT COUNT(DISTINCT c.email) AS distinct_emails
FROM customers AS c, orders AS o;

EXPLAIN QUERY PLAN
SELECT DISTINCT c.email
FROM customers AS c, orders AS o;

EXPLAIN QUERY PLAN
SELECT c.email
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why is pair_count 40 while distinct_emails is 5, and why does the real join omit Alonzo while DISTINCT does not?

Explain OR; split it if the plan SCANs

email = 'ada@example.com' OR city = 'London' asks two different indexes. SQLite 3 can emit MULTI-INDEX OR and SEARCH both. Other engines often SCAN. If you see SCAN, write two queries and UNION (distinct people) or UNION ALL (if you will dedupe yourself).

Do not “optimize” OR into a Cartesian. Do not wrap both columns in functions. Read this catalog's OR plan, then the UNION plan, and notice UNION's TEMP B-TREE is the distinct bill you already know.

SQL BROWSER RUNNER

Read MULTI-INDEX OR next to a UNION of two lookups

Same Ada-or-London question, two shapes.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

EXPLAIN QUERY PLAN
SELECT customer_id, email, city
FROM customers
WHERE email = 'ada@example.com'
   OR city = 'London';

EXPLAIN QUERY PLAN
SELECT customer_id, email, city
FROM customers
WHERE email = 'ada@example.com'
UNION
SELECT customer_id, email, city
FROM customers
WHERE city = 'London';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which emails appear, did OR SEARCH both indexes, and what extra work does UNION add?

Probe with EXISTS, index the inner key

“Customers who have at least one order” is a yes/no per customer, not a row per order. EXISTS (SELECT 1 FROM orders WHERE customer_id = c.customer_id) can stop at the first match. It is still correlated: you want idx_orders_customer so each probe is SEARCH, not SCAN orders.

IN (SELECT customer_id FROM orders) is another shape (list subquery). Join-and-distinct is a third. Pick the grain first, then the plan. This runner keeps EXISTS plus the child index from the catalog.

SQL BROWSER RUNNER

List customers who ordered, with an EXISTS probe

Correlated EXISTS, inner SEARCH on idx_orders_customer.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

EXPLAIN QUERY PLAN
SELECT c.email
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

SELECT c.email
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Who is missing from the email list, and which index made the inner probe a SEARCH?

Count once per group, not once per round trip

Three COUNT(*) WHERE customer_id = n statements are three trips and three plans. In an application loop that is N+1: one query for the list, then one per parent. SQL can batch: GROUP BY customer_id reads orders once (here, walking the customer_id index) and returns every count.

Join that aggregate back to customers when you need email. Do not correlate a COUNT in the SELECT list unless the plan's inner SEARCH is acceptable at your size—last lesson showed that shape.

SQL BROWSER RUNNER

Replace three COUNTs with one GROUP BY

Per-id counts first, then one grouped plan and result.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

SELECT COUNT(*) AS ada_orders
FROM orders
WHERE customer_id = 1;

SELECT COUNT(*) AS grace_orders
FROM orders
WHERE customer_id = 2;

SELECT COUNT(*) AS linus_orders
FROM orders
WHERE customer_id = 3;

EXPLAIN QUERY PLAN
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many statements did the batched query need, and what is Ada's order_count compared with the first COUNT?

Leading LIKE is still a scan in disguise

Indexes help when the start of the string is known. LIKE '%example.com' is not. LIKE 'ada%' can SEARCH after the collation matches (last lesson's case_sensitive_like or a NOCASE index). If you must search inside a string, you need a different tool (full-text, a stored domain column), not a bigger OFFSET.

SQL BROWSER RUNNER

Show a leading wildcard SCAN, then a prefix after the pragma

Three LIKE plans on the unique email index.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

EXPLAIN QUERY PLAN
SELECT email
FROM customers
WHERE email LIKE '%example.com';

EXPLAIN QUERY PLAN
SELECT email
FROM customers
WHERE email LIKE 'ada%';

PRAGMA case_sensitive_like = ON;

EXPLAIN QUERY PLAN
SELECT email
FROM customers
WHERE email LIKE 'ada%';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which LIKE still SCANs after the pragma, and what column would you store to search by domain?

A short tuning loop

  1. Write what one output row means. If you cannot, stop.
  2. List columns the UI uses. Drop *.
  3. Put filters and joins on naked columns that match indexes (ranges, equality, trailing LIKE).
  4. Page with the last unique key, not OFFSET on deep pages.
  5. Kill Cartesian joins. Treat DISTINCT as a confession until proven otherwise.
  6. Batch aggregates. Index EXISTS/IN inner keys.
  7. EXPLAIN QUERY PLAN before and after. On real size, time it. SQLite plans are not PostgreSQL EXPLAIN ANALYZE clocks.

What you should be able to do

  • Swap SELECT * for a covering list when the index already holds the answer.
  • Rewrite year and case wrappers into ranges and stored form.
  • Page with WHERE id > :last.
  • Replace DISTINCT-over-product with a join or EXISTS.
  • Batch counts and re-explain.

Independent lab: the messy catalog page

The starter is a 2026 order page that uses strftime, SELECT *, and OFFSET. Explain it. Rewrite to a placed range, explicit columns, and keyset after the last order_id you would have shown on the previous page (here, 3). Confirm the three rows match the old page's intent, then confirm the plan SEARCHES.

SQL BROWSER RUNNER

Retune the 2026 order page and prove it

strftime + star + OFFSET first. Range, columns, keyset next.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  city TEXT NOT NULL,
  created TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  cents INTEGER NOT NULL,
  placed TEXT NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'London', '2025-11-02'),
  (2, 'grace@example.com', 'New York', '2026-01-15'),
  (3, 'linus@example.com', 'Helsinki', '2026-03-01'),
  (4, 'margaret@example.com', 'London', '2026-06-20'),
  (5, 'alonzo@example.com', 'Princeton', '2026-07-01');

INSERT INTO orders VALUES
  (1, 1, 'NB-1', 1200, '2026-01-02'),
  (2, 1, 'PEN-2', 400, '2026-02-02'),
  (3, 2, 'NB-1', 1200, '2026-03-02'),
  (4, 3, 'MUG-9', 900, '2026-04-02'),
  (5, 4, 'NB-1', 1200, '2026-05-02'),
  (6, 1, 'MUG-9', 900, '2026-06-02'),
  (7, 2, 'PEN-2', 400, '2026-07-02'),
  (8, 4, 'PEN-2', 400, '2026-08-02');

CREATE INDEX idx_customers_city ON customers(city);
CREATE INDEX idx_customers_created ON customers(created);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed ON orders(placed);

-- Catalog page, first draft. Tune it:
-- 1. Stop SELECT * if the UI only needs order_id, sku, cents.
-- 2. Replace OFFSET with keyset (last order_id you already showed).
-- 3. Filter 2026 with a created/placed range, not strftime.
-- 4. If you added DISTINCT, check you are not hiding a comma join.

SELECT *
FROM orders
WHERE strftime('%Y', placed) = '2026'
ORDER BY order_id
LIMIT 3 OFFSET 3;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which three order_ids did OFFSET 3 show, and which SEARCH replaced both the year wrap and the skip?

Common mistakes to avoid

  • Adding indexes until writes hurt, without changing the SQL that hid them.
  • Shipping SELECT * “for later.”
  • Filtering with functions on the column and blaming the planner.
  • Deep OFFSET because the ORM made it easy.
  • DISTINCT to clean a missing ON.
  • One COUNT per parent in a loop.
  • Calling four-row timings a benchmark.

Lesson review

  • I can tune by changing SQL meaning-safely, then proving the plan.
  • I can prefer covering column lists over SELECT *.
  • I can write sargable date ranges instead of strftime on the column.
  • I can page with a keyset instead of discarding OFFSET rows.
  • I can treat DISTINCT on a product as a bug until the join is fixed.
  • I can batch counts and index EXISTS probes.
KNOWLEDGE CHECK

Check your tuning judgment

Answer all ten questions, then reopen the runner whose OFFSET, DISTINCT, or strftime plan still feels unclear.

01What is query tuning, in this lesson?
02Why can SELECT * be more expensive than listing the columns you show?
03Why does WHERE strftime('%Y', created) = '2026' often SCAN an index on created?
04What is the problem with LIMIT 2 OFFSET 5 on a growing table?
05When is DISTINCT a warning light?
06What should you do after a suspicious OR?
07EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) wants which index?
08How do you stop repeating the same COUNT for every customer?
09Which LIKE still cannot jump an email index?
10You changed SQL and the result looks right. What is still missing?
PREVIOUS LESSONEXPLAIN and query plans
NEXT LESSONBackups, restores, and migrations
ON THIS PAGEThe tuning habitColumn listsSargable predicatesKeyset pagesDISTINCT as a smellOR lookupsEXISTS probesBatched countsLIKE prefixesIndependent labCommon mistakesKnowledge check
Course contents