SQL performance tuning

A repeatable way to tune SQL queries

SQL performance tuning means reducing the work a database does while keeping the result correct. Start with a query you can repeat, read its execution plan, change one cause of extra work, and run the same checks again.

Start with evidence

Do not tune from the 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 and look for work that grows with the table: a broad scan, a repeated join lookup, or a temporary sort.

Example with filtering and ordering

Schema

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

Sample data

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 query

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

Before an index, SQLite may report:

Before plan

SCAN orders
USE TEMP B-TREE FOR ORDER BY

Add an index that starts with the equality filter and continues with the requested order.

Change

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

After query

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

Run the same query again. SQLite can now report:

After plan

SEARCH orders USING INDEX idx_orders_customer_created (customer_id=?)

The first index column narrows the rows to one customer. The second keeps those rows in the order the query asks for. SQLite can avoid both the full scan and the temporary sort. The query still has to read total_cents from the table, so the plan is better but not free.

Check the change

Did the query return the same rows in the same order?
Did you test the same parameters and data?
Did a broad table scan become a focused index search?
Did the temporary sort disappear?
Did the change make writes or other important queries worse?
Does the timing improve on realistic data after warm-up noise is removed?

Know the boundary

This example uses SQLite. PostgreSQL and MySQL show different plan fields and may choose a different index or join order. The workflow still applies: protect the result, find the growing work, change one cause, and verify the result and plan again.