A repeatable way to tune SQL queries

SQL performance tuning means reducing database work while keeping the result correct. First, save a query and result you can check again. Next, read the execution plan and change one source of extra work. Then run the query again and check the result, plan, and time.

3 minute read
Examples run in SQLite

On this page

Start with evidence

Do not tune a query from its SQL text alone. Save the query, its parameter values, and the result you expect. Use data that is large and varied enough to show the real problem.

Then read the query execution plan. Look for work that grows with the table, such as a full table scan, a join lookup that repeats many times, or a temporary sort.

Also count database queries per application request. An N+1 problem repeats queries as the number of loaded records grows. A plan for one statement does not reveal those repeated calls.

The small example below shows a plan change, not a speed measurement. Measure execution time with realistic data before calling a query faster.

Example with filtering and ordering

The query reads one customer's orders, newest first. The orders table has no index yet.

Schema and sample SQL

Schema and sample data

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  created_at TEXT NOT NULL,
  total_cents INTEGER NOT NULL
);

INSERT INTO orders (id, customer_id, created_at, total_cents) VALUES
  (1, 42, '2026-01-03', 1900),
  (2, 42, '2026-01-05', 2200),
  (3, 7, '2026-01-04', 1800),
  (4, 42, '2026-01-01', 900);

Before

SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

Without an index, SQLite may read every order and then sort the matches in a temporary B-tree.

SCAN ordersReads rows by scanning a table or index.
USE TEMP B-TREE FOR ORDER BYUses a temporary structure to sort, group, or remove duplicates.

Open in the Visualizer

After CREATE INDEX idx_orders_customer_created

CREATE INDEX idx_orders_customer_created
  ON orders(customer_id, created_at DESC);

The new index starts with the equality filter and continues with the requested order.

SEARCH orders USING INDEX idx_orders_customer_created (customer_id=?)Finds the matching rows through an index.

Open in the Visualizer

The first index column narrows the search to one customer, and the second keeps that customer's rows in the requested order. SQLite can avoid both the full scan and the temporary sort.

The query still has to read total_cents from the table, so the new plan does less work but still reads each matching table row.

Key points

Confirm the query returns the same rows in the same order.
Test with the same parameters and data each time.
Check whether a full table scan became a focused index search.
Check whether the temporary sort went away.
Check whether the change hurt writes or other important queries.
Confirm the query is faster on realistic data once the caches are warm.

Know the boundary

The example runs in SQLite. PostgreSQL and MySQL show different plan fields and may choose a different index or join order. In any engine, first check the expected result and execution plan. Then change one source of extra work and check the result and plan again.

Practice this in Module 2: How queries run

Ten exercises on reading SQLite plans. You tell scans from searches and see how ranges and selective filters change the plan.

See Module 2