Query Optimization

A query that returns the right data is only half the battle. A query that returns it fast, without hammering your database server, is the other half.

When tables are small, almost any query runs instantly. But as data grows — thousands of rows become millions — the difference between a well-written query and a poorly-written one shifts from milliseconds to seconds, or worse. The same logic, expressed differently, can run ten times faster or slower depending on how the database engine executes it.

Query optimization is the practice of writing SQL that gives the database engine the clearest, most efficient path to your data. That means understanding how queries are executed, where indexes help, and which common patterns quietly destroy performance.

What You'll Learn

By the end of this lesson you will read a query plan, change a query, and read the plan again to see whether your change did anything — the before-and-after loop that separates real optimization from folklore. The concrete task is to take a report that filters a date column with a function, rewrite it so the filter can use an index, and confirm the plan moves from a scan to a search. The discipline being taught is narrower than "make it fast": it is to stop guessing, and to make the engine tell you what it is going to do.

This is the plan-notes milestone, and the warehouse capstone ends with exactly this artifact — a short written note for each of its main reports giving the plan before an index, the index added, the plan after, and one sentence saying why. It is the last technique in the core path, so the capstone is where all of it lands. You arrive able to create an index and read EXPLAIN QUERY PLAN at a basic level (Indexes and Performance), to join and aggregate (Joins and Relationships, Aggregation and Grouping), and to reason about the schema those queries run against (Constraints and Keys); what is new is treating the plan as the measurement you act on rather than as a curiosity.

One caution to set the tone. Reading query plans is well-established professional tooling, but there is no strong research base saying that any particular way of teaching plan-reading works better than another — so treat what follows as a craft practice to adopt and test, not as a proven method. And every claim about speed in this lesson is a claim about the work the engine does, not about milliseconds: the tables here hold a handful of rows, where nothing is measurably faster than anything else. If you want a timing claim, you have to measure it on your own data.

How the Database Executes a Query

Before optimizing, you need to understand what the database is actually doing with your SQL. Every query goes through a query planner — a component that decides the best strategy to satisfy your request. It considers available indexes, table sizes, join order, and statistics about your data.

You can inspect this plan using EXPLAIN. In SQLite (used in this playground), the form you want is EXPLAIN QUERY PLAN, which describes the strategy in a line or two of readable text. Bare EXPLAIN also works here, but it prints the engine's internal bytecode — dozens of rows of opcodes — which is a debugging tool for people working on SQLite itself rather than on queries.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status TEXT,
  total REAL,
  created_at TEXT
);

INSERT INTO orders VALUES
  (1, 101, 'completed', 49.99, '2024-01-10'),
  (2, 102, 'pending',   12.50, '2024-01-11'),
  (3, 101, 'completed', 89.00, '2024-01-12'),
  (4, 103, 'cancelled', 34.75, '2024-01-13'),
  (5, 102, 'completed', 22.00, '2024-01-14');

-- See how the database plans to execute this query
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 101;

The plan comes back as a small table with four columns — id, parent, notused and detail — and detail is the one you read. For this query it holds:

SCAN orders

SCAN means the engine will read every row of orders and test each one. There is no index on customer_id, so it has no alternative. For five rows this is trivial; for five million it is the difference between a report and an outage. The vocabulary is small and worth learning exactly as the engine prints it: SCAN t for reading a whole table, and SEARCH t USING INDEX i (c=?) for jumping straight to matching rows through an index. Note there is no TABLE token in either form on this version. Older SQLite releases did print one, in the shape SCAN followed by TABLE and then the name, so documentation and blog posts written against those versions will not match what you see here.

Indexes and How They Help

An index is a separate data structure the database maintains alongside your table. It organizes a column's values in a way that allows the engine to jump directly to matching rows instead of scanning everything.

Adding an index on a frequently-filtered column is often the single highest-impact optimization you can make.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL,
  in_stock INTEGER
);

INSERT INTO products VALUES
  (1, 'Notebook',      'stationery', 4.99,  1),
  (2, 'Gel Pen Pack',  'stationery', 8.49,  1),
  (3, 'Desk Lamp',     'office',     34.99, 1),
  (4, 'Monitor Stand', 'office',     59.99, 0),
  (5, 'Stapler',       'office',     14.99, 1),
  (6, 'Sticky Notes',  'stationery', 3.29,  1);

-- Without index: full scan
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE category = 'office';

-- Create an index on the category column
CREATE INDEX idx_products_category ON products(category);

-- With index: uses the index to find rows directly
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE category = 'office';

Notice how the query plan changes after the index is created. The first plan reads SCAN products; the second reads SEARCH products USING INDEX idx_products_category (category=?). The word SEARCH is the change that matters — the engine is now jumping to the matching rows instead of examining all of them, and the part in parentheses names the column it seeks on.

You will also meet a longer form, SEARCH t USING COVERING INDEX i (c=?), and the extra word is worth recognising. COVERING means every column the query asked for is already inside the index, so the engine answers entirely from it and never touches the table at all. It is absent here for a reason you can see in the query: this one is SELECT * over a five-column table, and the index holds only category, so the engine must still fetch each matching row. Change it to SELECT category FROM products WHERE category = 'office' and the same index becomes covering. That is the concrete version of the advice about SELECT * later in this lesson — the columns you ask for decide whether an index can finish the job by itself.

CREATE TABLE events (id INTEGER PRIMARY KEY, actor TEXT NOT NULL, kind TEXT NOT NULL);
INSERT INTO events VALUES (1,'ada','login'),(2,'bo','login'),(3,'ada','purchase');
CREATE INDEX idx_events_actor ON events(actor);
EXPLAIN QUERY PLAN SELECT id FROM events WHERE actor = 'ada';
EXPLAIN QUERY PLAN SELECT id FROM events WHERE lower(actor) = 'ada';
EXPLAIN QUERY PLAN SELECT id FROM events WHERE actor LIKE '%da';

Indexes are not free. They consume storage and add a small cost to every INSERT, UPDATE, and DELETE, because every write has to update the index as well as the row. So avoid indexing columns that are rarely queried but frequently modified — you would be paying on every write for a lookup nobody performs. Choose indexes for the columns you actually filter, join, or sort by, and be willing to drop one that the plans show is never used.

Avoiding Full Scans with Selective Filters

The most effective filter is one that eliminates most rows early. A selective condition is one that matches only a small fraction of the table. A non-selective condition matches nearly everything and provides little benefit even with an index.

Two patterns that consistently kill query performance:

Using a function on an indexed column — this prevents the index from being used because the database must compute the function for every row before comparing.

Using LIKE with a leading wildcard — LIKE '%term' cannot use an index because the match pattern starts in the middle of the value.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary REAL
);

INSERT INTO employees VALUES
  (1, 'Alice',   'engineering', '2021-03-15', 9500000),
  (2, 'Bob',     'marketing',   '2020-07-01', 7200000),
  (3, 'Carol',   'engineering', '2022-11-20', 8800000),
  (4, 'David',   'hr',          '2019-05-10', 6500000),
  (5, 'Eve',     'engineering', '2023-01-08', 9100000),
  (6, 'Frank',   'marketing',   '2021-09-30', 7800000);

CREATE INDEX idx_employees_hire_date ON employees(hire_date);

-- Wrapping hire_date in substr() hides the stored value from the index
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE substr(hire_date, 1, 4) = '2021';

-- A range filter on the bare column can be searched instead
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE hire_date >= '2021-01-01' AND hire_date < '2022-01-01';

-- See the actual results from the second version
SELECT name, hire_date FROM employees
WHERE hire_date >= '2021-01-01' AND hire_date < '2022-01-01'
ORDER BY hire_date;

Read the two plans side by side. The first says SCAN employees; the second says SEARCH employees USING INDEX idx_employees_hire_date (hire_date>? AND hire_date<?), naming the index and the two bounds it seeks between. Both queries return Alice and Frank — the rows are identical and the work is not, which is precisely why the rows cannot tell you which query to keep.

The rewrite works because an index over hire_date is ordered by the stored text, and an ISO 8601 date sorts chronologically, so "everything in 2021" is a contiguous stretch of that ordering with a first and last entry to seek to. substr(hire_date, 1, 4) is a value the index does not contain, so there is nothing to seek.

Optimizing JOINs and Aggregations

Joins and aggregations are where queries most often become slow as data grows. Two principles help here:

Filter before joining. If you only need a subset of rows from a table, apply the WHERE condition before the join, so the join has fewer rows to process. In many engines this is advice about how you write the query; on a modern planner it is often advice the planner takes for you, as the next fence demonstrates.

Avoid SELECT * in production queries. Fetching every column forces the database to read and transmit more data than you need. Naming specific columns lets the database potentially satisfy the query from an index alone, without touching the main table at all — a technique called an index-only scan or covering index, and the thing COVERING means when you see it in a plan. Be aware that on a narrow table this proves less than it appears: if a table has only the two columns the index holds, then SELECT * is covered too, and the plan looks identical either way. The technique earns its keep on wide tables, where the columns you skip are the ones you would otherwise have to fetch.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  region TEXT
);

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount_cents INTEGER,
  sale_date TEXT
);

INSERT INTO customers VALUES
  (1, 'Acme Corp',   'north'),
  (2, 'Beta LLC',    'south'),
  (3, 'Gamma Inc',   'north'),
  (4, 'Delta Co',    'east');

INSERT INTO sales VALUES
  (1, 1, 120000, '2024-02-01'),
  (2, 1,  45000, '2024-02-15'),
  (3, 2,  80000, '2024-02-10'),
  (4, 3, 210000, '2024-02-20'),
  (5, 3,  30000, '2024-03-01'),
  (6, 4,  95000, '2024-03-05');

-- Plan A: join, then filter in the WHERE clause
EXPLAIN QUERY PLAN
SELECT c.name, SUM(s.amount_cents) AS total_cents
FROM sales s
JOIN customers c ON s.customer_id = c.id
WHERE c.region = 'north'
GROUP BY c.id, c.name;

-- Plan B: pre-filter the customers in a CTE, then join only those
EXPLAIN QUERY PLAN
WITH north_customers AS (
  SELECT id, name FROM customers WHERE region = 'north'
)
SELECT nc.name, SUM(s.amount_cents) AS total_cents
FROM sales s
JOIN north_customers nc ON s.customer_id = nc.id
GROUP BY nc.id, nc.name;

-- Actual results
WITH north_customers AS (
  SELECT id, name FROM customers WHERE region = 'north'
)
SELECT nc.name, SUM(s.amount_cents) AS total_cents
FROM sales s
JOIN north_customers nc ON s.customer_id = nc.id
GROUP BY nc.id, nc.name
ORDER BY total_cents DESC, nc.name;

Read those two plans carefully, because they are the same plan. Both report SCAN s, then a SEARCH of the customers table by its integer primary key, then USE TEMP B-TREE FOR GROUP BY; the only difference between them is the name the second one uses for the customers table. SQLite's planner flattens the CTE into the surrounding query and then makes exactly the same decisions it made for the first version.

That is worth more than a fence where the rewrite wins, because it is the commonest way optimization advice goes wrong. "Pre-filter in a CTE" is real advice with real cases behind it — but here it bought nothing, and the only reason you know that is that you asked. Had you not looked at the plan, you would have come away believing you had optimized something, and carried that belief to the next query. Every rewrite is a hypothesis until the plan confirms it.

If you want to see a rewrite that does change the plan, the function-on-a-column case in the previous section is one: substr(hire_date, 1, 4) = '2021' scans, the equivalent range comparison searches, and the two plans differ in their first word.

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_on TEXT NOT NULL, total_cents INTEGER NOT NULL);
INSERT INTO orders VALUES (1,'2024-01-05',12000),(2,'2024-02-11',8000),(3,'2024-01-20',5000),(4,'2024-03-02',9000);
CREATE INDEX idx_orders_placed_on ON orders(placed_on);
SELECT id, placed_on FROM orders WHERE substr(placed_on, 1, 7) = '2024-01' ORDER BY id;
EXPLAIN QUERY PLAN SELECT id, placed_on FROM orders WHERE substr(placed_on, 1, 7) = '2024-01';

4 row(s) affected
id | placed_on
--------------
1 | 2024-01-05
3 | 2024-01-20
id | parent | notused | detail
------------------------------
2 | 0 | 0 | SEARCH orders USING COVERING INDEX idx_orders_placed_on (placed_on>? AND placed_on<?)

Ordering a Before-and-After Measurement

The loop this lesson teaches has a fixed shape, and the shape is the lesson: measure, change one thing, measure again. The table below is already created and filled.

CREATE TABLE events (id INTEGER PRIMARY KEY, actor TEXT NOT NULL, kind TEXT NOT NULL);
INSERT INTO events VALUES (1,'ada','login'),(2,'bo','login'),(3,'ada','purchase');

Three rows are already loaded, two of them Ada's. The rows are given rather than shuffled in with the rest on purpose — the plan for this query is the same whether the table is full or empty, so where an INSERT sits would not show up in the output at all.

CREATE TABLE tickets (
id         INTEGER PRIMARY KEY,
status     TEXT NOT NULL,
opened_at  TEXT NOT NULL,
assignee   TEXT,
body       TEXT NOT NULL
);

SELECT id, assignee FROM tickets
WHERE status = 'open'
ORDER BY opened_at;

Try It Yourself

A transactions table has no indexes beyond its primary key, and a report over it filters by month using a function on the date column. This is a build task. The starter below runs and returns a list of individual transactions for March; the report that is actually wanted is a per-customer spend total for March and April combined, restricted to the customers who spent at least 50000 cents, with an index in place to support the date filter.

CREATE TABLE transactions (
id           INTEGER PRIMARY KEY,
customer_id  INTEGER NOT NULL,
amount_cents INTEGER NOT NULL,
sale_date    TEXT NOT NULL
);
INSERT INTO transactions VALUES
(1,10,25000,'2024-03-01'),(2,11,43000,'2024-03-05'),(3,10,18000,'2024-04-10'),(4,12,99000,'2024-03-22'),
(5,11,11000,'2024-04-01'),(6,13,67000,'2024-03-15'),(7,10,32000,'2024-04-18'),(8,12,54000,'2024-04-25');
-- TODO 1: add an index on sale_date, so the rewritten filter below has one to use.
-- TODO 2: replace the substr filter with a range comparison covering March AND April,
--         written so the bare sale_date column is on the left of each comparison.
--         Check your bounds: April 25th must be included.
-- TODO 3: report one row per customer as customer_id and q2_spend_cents, keeping only
--         customers whose total is 50000 or more. Customer 12 totals 153000.
SELECT customer_id, amount_cents
FROM transactions
WHERE substr(sale_date, 1, 7) = '2024-03'
ORDER BY amount_cents DESC;

8 row(s) affected
customer_id | q2_spend_cents
----------------------------
12 | 153000
10 | 75000
13 | 67000
11 | 54000

Before you submit the version that passes, try two variations on it in the block's editor — an unsolved run is never limited, and each one shows you its output beside the expected lines. Predict each before you run it, then put the passing version back and run that last. First, run EXPLAIN QUERY PLAN on your finished query and then again with the CREATE INDEX line deleted: the plan goes from a SEARCH naming your index and its two bounds to a bare SCAN transactions, while the four reported rows stay byte-identical — the clearest demonstration available that output and cost are independent. Second, change the upper bound from < '2024-05-01' to < '2024-04-25': customer 12's 54000 on April 25th falls outside the range, their total drops to 99000, and they stay at the top of a report that is now quietly missing a sale. Nothing errors, and the shape of the output is unchanged — which is the whole hazard of a bound that is one character off. (Try <= '2024-04-25' too, and note that it returns the same four rows as the original, because these dates carry no time component. That is why the half-open form is the habit worth keeping: it is correct either way, while the closed form is correct only until someone starts storing timestamps.)

Milestone: The Warehouse Plan Notes

The capstone's final deliverable includes a short performance note for its main reports, and this is where that note comes from. It is not a benchmark — it is a record of what the engine said it would do, before and after each index you chose to add.

Key Takeaways

• Use EXPLAIN QUERY PLAN to inspect how the database executes your query before assuming anything about it. On this lane it returns four columns, and the one you read is detail. Bare EXPLAIN works too but prints internal bytecode, which is not what you want.
• Read the first word. SCAN t means every row is examined; SEARCH t USING INDEX i (c=?) means the engine seeks straight to the matches. There is no TABLE token in either form on this version. The index's name appearing in a plan is not enough — SCAN t USING COVERING INDEX i is still a full scan.
• Indexes let the engine find rows without reading all of them, and cost a little on every INSERT, UPDATE and DELETE. Avoid indexing columns that are rarely queried but frequently modified; index the ones you filter, join or sort by.
• Never apply a function to an indexed column in a WHERE clause, and avoid leading-wildcard LIKE patterns — in both cases the value the index stores is not the value being tested, so the plan falls back to a scan with no error and no warning.
• A rewrite is a hypothesis until the plan confirms it. The CTE pre-filter in this lesson produces the same plan as the query it was supposed to improve — the same three steps, differing only in the name shown for the customers table — because the planner flattens it. Measure before and after, every time, and record the rewrites that changed nothing.
• Select only the columns you need. On a wide table this can make an index covering; on a narrow one it changes nothing, since SELECT * was already covered.
• Optimization is data-driven. Every claim in this lesson is about the work the plan says the engine will do, not about elapsed time — the tables here are too small for timing to mean anything, and so is any table you have not measured.

Pro Tip: The fastest query is one that reads the fewest rows. Before reaching for an index, ask whether your query structure itself is doing unnecessary work — are you joining tables you could filter first? Aggregating data you could pre-group? Often, restructuring the query logic eliminates the performance problem entirely, and the index becomes a bonus rather than a crutch.

Next Steps

That is the last new technique in the core path — you now have comprehensive database skills, from basic queries to advanced optimization. Next comes the capstone project, where you put all of it together: design a complete e-commerce database from scratch, seed it with realistic data, and write the analytical queries a real business would need.