SQL Query Optimization

SQL query optimization is the practice of writing and structuring queries so the database engine does the least possible work to return the correct result. A query that could scan an entire million-row table can often be rewritten to run in milliseconds using an index instead. Optimization is not a bag of clever tricks — it means understanding how the query planner thinks, giving it indexes and conditions it can actually use, and reading its execution plan to confirm it is doing what you expect. This lesson walks through how the planner makes decisions, how to write indexable (“sargable”) queries, and the mistakes that quietly turn a fast query into a slow one.

Overview: How SQL Query Optimization Works

When you run a SELECT statement, the engine does not simply obey the SQL literally. It parses the query, and a component called the query planner (or optimizer) considers several ways to physically execute it — a full table scan, an index seek, a range scan, different join orders — and picks the one it estimates will cost the least, measured mainly in disk pages read and rows examined.

To make that estimate, the planner relies on statistics: row counts, and for indexed columns, how many distinct values exist and how they are distributed. SQLite gathers these with the ANALYZE command; PostgreSQL and MySQL maintain similar statistics automatically or via commands like VACUUM ANALYZE and ANALYZE TABLE. Without decent statistics, the planner can guess badly and pick a slow plan even when a fast one exists.

Indexes are the biggest lever you control. An index is a separate, sorted structure (almost always a B-tree) mapping column values to the rows that contain them. Looking up a value in a B-tree takes roughly O(log n) comparisons instead of the O(n) row-by-row check a full scan requires — the difference between reading a handful of pages and reading every page in the table.

Two ideas matter once you index seriously. First, sargability (from “Search ARGument ABLE”): a condition is sargable if the engine can evaluate it directly against an index without transforming every row first. WHERE department = 'Engineering' is sargable if department is indexed; WHERE UPPER(department) = 'ENGINEERING' generally is not, because the engine would have to compute UPPER() for every row before comparing. Second, a covering index contains every column a query needs, so the engine can answer entirely from the index without touching the table rows — an “index-only” scan, often dramatically faster than an index seek followed by a row lookup.

Joins add another layer. A common join algorithm is the nested loop join: for each row of the outer table, the engine looks up matching rows in the inner table. If the inner table’s join column is indexed, each lookup is fast; if not, every outer row triggers a full scan of the inner table — one of the most common causes of a slow join in production.

Syntax: The Tools You Use to Optimize a Query

There is no special “optimize” keyword. You combine ordinary statements — creating the right indexes, writing sargable filters, limiting result size — with commands that reveal how the engine plans to execute your query.

Technique	Purpose
`CREATE INDEX idx ON table(col);`	Builds a B-tree so lookups/filters on that column avoid a full scan.
`CREATE INDEX idx ON table(c1, c2);`	A composite index; speeds queries filtering by c1 (and c2) or sorting by c1 then c2, in that order.
`CREATE INDEX idx ON table(LOWER(col));`	An expression index; keeps a function-wrapped condition sargable.
`EXPLAIN QUERY PLAN SELECT ...;`	Shows whether the engine will SCAN (read every row) or SEARCH (use an index).
`SELECT ... LIMIT n;`	Stops after n rows instead of computing the full result set.
`ANALYZE;`	Refreshes the statistics the planner uses to cost each plan.

The example below adds an index to the shared employees table and confirms with EXPLAIN QUERY PLAN that a filter on it is actually being searched, not scanned.

CREATE INDEX idx_employees_department ON employees(department);

EXPLAIN QUERY PLAN
SELECT name, salary
FROM employees
WHERE department = 'Engineering';

Result: the index is created, then the plan output includes a detail line reading something like SEARCH employees USING INDEX idx_employees_department (department=?) — confirming the filter uses the new index instead of scanning all rows.

Examples

Example 1: Select only what you need, then filter, sort and limit

SELECT * with no filter forces the engine to read and return far more data than the question requires. Here we ask for two columns only, filter first, and cap the result to a single row.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  region TEXT,
  amount REAL,
  sale_date TEXT
);
INSERT INTO sales (sale_id, customer_name, region, amount, sale_date) VALUES
(1,'Alice Corp','West',1200.00,'2026-01-05'),
(2,'Beta LLC','East',450.50,'2026-01-10'),
(3,'Gamma Inc','West',980.75,'2026-02-01'),
(4,'Delta Co','South',2200.00,'2026-02-15'),
(5,'Epsilon Ltd','East',300.00,'2026-03-01');

SELECT sale_id, amount
FROM sales
WHERE region = 'West'
ORDER BY amount DESC
LIMIT 1;

sale_id	amount
1	1200.0

Only the West rows (sale 1 at 1200.00 and sale 3 at 980.75) are considered; sorting by amount descending and taking the top one returns sale 1. On a huge table, filtering before sorting and stopping at LIMIT 1 means the engine can avoid sorting the whole result.

Example 2: Let an index do the searching

Here we index category and confirm with EXPLAIN QUERY PLAN that the engine uses it instead of scanning every row.

CREATE TABLE products_big (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products_big (product_id, product_name, category, price) VALUES
(1,'Widget A','Hardware',9.99),
(2,'Widget B','Hardware',12.50),
(3,'Gadget C','Electronics',45.00),
(4,'Gadget D','Electronics',60.00),
(5,'Tool E','Hardware',15.75);

CREATE INDEX idx_products_big_category ON products_big(category);

SELECT product_name, price
FROM products_big
WHERE category = 'Electronics';

product_name	price
Gadget C	45.0
Gadget D	60.0

CREATE TABLE products_big (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products_big (product_id, product_name, category, price) VALUES
(1,'Widget A','Hardware',9.99),
(2,'Widget B','Hardware',12.50),
(3,'Gadget C','Electronics',45.00),
(4,'Gadget D','Electronics',60.00),
(5,'Tool E','Hardware',15.75);

CREATE INDEX idx_products_big_category ON products_big(category);

EXPLAIN QUERY PLAN
SELECT product_name, price
FROM products_big
WHERE category = 'Electronics';

Result: a single plan row whose detail text reads something like SEARCH products_big USING INDEX idx_products_big_category (category=?) instead of SCAN products_big. That single word — SEARCH versus SCAN — is exactly how you confirm an index is doing its job rather than assuming it.

Example 3: A composite covering index

When a query always filters by one column and needs a couple of others, a composite index containing all of them can let the engine answer the query from the index alone.

CREATE TABLE orders_demo (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  amount REAL
);
INSERT INTO orders_demo (order_id, customer_id, order_date, amount) VALUES
(1,1,'2026-01-03',250.00),
(2,2,'2026-01-07',75.50),
(3,1,'2026-02-11',400.00),
(4,3,'2026-02-20',120.00),
(5,2,'2026-03-01',60.00);

CREATE INDEX idx_orders_demo_covering ON orders_demo(customer_id, order_date, amount);

SELECT order_date, amount
FROM orders_demo
WHERE customer_id = 1
ORDER BY order_date;

order_date	amount
2026-01-03	250.0
2026-02-11	400.0

The index on (customer_id, order_date, amount) covers this query completely: customer_id drives the search, order_date is already stored in ascending order within each customer’s entries (so no separate sort step is needed), and amount sits right there in the index without a trip back to the table. This is why column order in a composite index matters: put the equality-filtered column first, then the column you sort or range-filter by, then any columns you only need to select.

How It Works Step by Step: Under the Hood

A SELECT is logically processed in a fixed order, even though the physical plan may reorder or fuse steps for efficiency: FROM (identify and join source tables) → WHERE (filter rows) → GROUP BY (bucket remaining rows) → HAVING (filter buckets) → SELECT (compute output expressions) → ORDER BY (sort) → LIMIT (trim to the requested count).

This order explains several optimizer behaviors. Because WHERE runs before SELECT, an index on a WHERE column can eliminate most rows before the engine ever computes your selected expressions — which is why filtering on indexed, sargable conditions pays off so much. Because ORDER BY runs after everything else, if rows already arrive from an index in the requested order (as in Example 3), the engine can skip a separate, memory-hungry sort step entirely. And because LIMIT runs last, a plan that produces rows already sorted by an index can stop as soon as it has enough of them, instead of computing and sorting the full result first — which is why ORDER BY ... LIMIT n queries benefit enormously from an index on the ORDER BY column.

Common Mistakes

Mistake 1: Wrapping an indexed column in a function

Applying LOWER() to email makes the condition non-sargable: a plain index on email cannot be used, because the engine would have to compute LOWER(email) for every row before comparing.

Wrong (executes correctly, but scans the table):

CREATE TABLE contacts (
  contact_id INTEGER PRIMARY KEY,
  email TEXT
);
CREATE INDEX idx_contacts_email ON contacts(email);
INSERT INTO contacts (contact_id, email) VALUES
(1,'alice@example.com'),
(2,'bob@example.com'),
(3,'carol@example.com');

SELECT contact_id, email
FROM contacts
WHERE LOWER(email) = 'bob@example.com';

Fixed, by indexing the expression itself instead of the raw column:

CREATE TABLE contacts (
  contact_id INTEGER PRIMARY KEY,
  email TEXT
);
INSERT INTO contacts (contact_id, email) VALUES
(1,'alice@example.com'),
(2,'bob@example.com'),
(3,'carol@example.com');

CREATE INDEX idx_contacts_email_lower ON contacts(LOWER(email));

SELECT contact_id, email
FROM contacts
WHERE LOWER(email) = 'bob@example.com';

Both return the same single row (contact 2, bob@example.com), but the corrected version lets the planner search idx_contacts_email_lower directly instead of evaluating LOWER() against every row.

Mistake 2: A leading wildcard in LIKE

A pattern that starts with % cannot use a standard B-tree index, because the engine cannot know where in the sorted list a “contains” match might begin, so it must check every row.

Wrong:

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT
);
CREATE INDEX idx_articles_title ON articles(title);
INSERT INTO articles (article_id, title) VALUES
(1,'Learning SQL Basics'),
(2,'Advanced SQL Optimization'),
(3,'SQL for Data Analysts');

SELECT article_id, title
FROM articles
WHERE title LIKE '%SQL%';

This matches all three articles (every title contains “SQL” somewhere), but finding that out required scanning the whole table.

Fixed, when only a prefix match is needed, drop the leading wildcard so the index can be used as a range scan:

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT
);
CREATE INDEX idx_articles_title ON articles(title);
INSERT INTO articles (article_id, title) VALUES
(1,'Learning SQL Basics'),
(2,'Advanced SQL Optimization'),
(3,'SQL for Data Analysts');

SELECT article_id, title
FROM articles
WHERE title LIKE 'SQL%';

This narrows the match to titles starting with “SQL”, returning only article 3. If you genuinely need substring search at scale, do not rely on LIKE '%text%' at all — reach for a full-text search feature such as SQLite’s FTS5 virtual tables instead.

Mistake 3: Combining two separately indexed columns with OR

department and city each have their own index, but a single OR across both columns often prevents the planner from using either index efficiently, since one index lookup can only satisfy one equality condition at a time.