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.

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 keyword is EXPLAIN QUERY PLAN.

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;

Without an index, the planner reports a full table scan — it reads every row to find matches. For five rows, this is trivial. For five million rows, it becomes a serious problem.

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 database no longer scans the whole table — it uses the index to locate only the rows in the office category.

Indexes are not free. They consume storage and add a small cost to every INSERT, UPDATE, and DELETE because the index must be kept in sync. Index columns that are rarely queried but frequently modified. Choose indexes for columns you actually filter, join, or sort by.

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', 95000),
  (2, 'Bob',     'marketing',   '2020-07-01', 72000),
  (3, 'Carol',   'engineering', '2022-11-20', 88000),
  (4, 'David',   'hr',          '2019-05-10', 65000),
  (5, 'Eve',     'engineering', '2023-01-08', 91000),
  (6, 'Frank',   'marketing',   '2021-09-30', 78000);

CREATE INDEX idx_employees_hire_date ON employees(hire_date);

-- BAD: wrapping hire_date in substr() prevents index use
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE substr(hire_date, 1, 4) = '2021';

-- GOOD: range filter on the indexed column directly
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE hire_date >= '2021-01-01' AND hire_date < '2022-01-01';

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

Rewriting the date filter as a range comparison lets the index do its job. The results are identical — but the path the database takes to find them is much shorter.

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 — ideally by using a subquery or CTE that pre-filters the data. This reduces the number of rows the join has to process.

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.

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

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount REAL,
  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, 1200.00, '2024-02-01'),
  (2, 1,  450.00, '2024-02-15'),
  (3, 2,  800.00, '2024-02-10'),
  (4, 3, 2100.00, '2024-02-20'),
  (5, 3,  300.00, '2024-03-01'),
  (6, 4,  950.00, '2024-03-05');

-- BAD: joins all customers then filters
EXPLAIN QUERY PLAN
SELECT c.name, SUM(s.amount) AS total
FROM sales s
JOIN customers c ON s.customer_id = c.id
WHERE c.region = 'north'
GROUP BY c.id, c.name;

-- GOOD: pre-filter customers in a CTE, then join only matching rows
EXPLAIN QUERY PLAN
WITH north_customers AS (
  SELECT id, name FROM customers WHERE region = 'north'
)
SELECT nc.name, SUM(s.amount) AS total
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) AS total
FROM sales s
JOIN north_customers nc ON s.customer_id = nc.id
GROUP BY nc.id, nc.name
ORDER BY total DESC;

In this small dataset, the difference is subtle. At scale, reducing the number of rows that flow through a join is one of the most powerful optimizations available.

Try It Yourself

The query below finds the top-spending customers, but it is written inefficiently. It uses SELECT *, applies a function to a column in the filter, and fetches more data than needed. Rewrite it to:

Select only the columns you actually use (customer_id and amount)
Replace the substr() date filter with a range comparison
Add an index on sale_date to support the filter

CREATE TABLE transactions (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount REAL,
  sale_date TEXT
);

INSERT INTO transactions VALUES
  (1,  10, 250.00, '2024-03-01'),
  (2,  11, 430.00, '2024-03-05'),
  (3,  10, 180.00, '2024-04-10'),
  (4,  12, 990.00, '2024-03-22'),
  (5,  11, 110.00, '2024-04-01'),
  (6,  13, 670.00, '2024-03-15'),
  (7,  10, 320.00, '2024-04-18'),
  (8,  12, 540.00, '2024-04-25');

-- Inefficient version — rewrite this query
SELECT *
FROM transactions
WHERE substr(sale_date, 1, 7) = '2024-03'
ORDER BY amount DESC;

Key Takeaways

Use EXPLAIN QUERY PLAN to inspect how the database executes your query before assuming it is fast
Indexes dramatically speed up WHERE, JOIN, and ORDER BY operations on large tables — but add overhead to writes
Never apply functions to indexed columns in WHERE clauses; rewrite the condition to keep the column bare
Avoid leading-wildcard LIKE patterns (LIKE '%value') — they prevent index use entirely
Pre-filter data in CTEs or subqueries before joining to reduce the number of rows processed
Select only the columns you need; SELECT * is convenient but wasteful in production queries
Optimization is data-driven — profile real queries on real data before adding indexes speculatively

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.