SQL Query Optimization: 10 Ways to Speed Up a Slow Query

A slow query is almost never cured by "a bigger server". Nine times out of ten the cause is the query itself or a missing index — and it takes minutes to fix. Here is a checklist of ten techniques in the order you should apply them.

1. Start with EXPLAIN: Learn What the DBMS Is Doing

Optimizing blindly is pointless. The EXPLAIN command shows the query plan — how exactly the DBMS intends to execute it:

MySQL 8.1
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

Seq Scan on orders  (cost=0.00..10.70 rows=60 width=44)
  Filter: (user_id = 1)

Seq Scan means a full table scan: the DBMS reads every row and checks the condition on each. On a thousand-row table it is invisible; on ten million rows it is seconds. The goal of most optimizations is to turn a Seq Scan over a large table into an Index Scan — pinpoint access through an index.

EXPLAIN ANALYZE additionally runs the query and shows the actual time and row counts of every step — it is the right starting point for any slow query investigation.

2. Index the Columns Used in WHERE and JOIN

An index is the main read-speed tool: instead of scanning the whole table, the DBMS descends a sorted structure straight to the matching rows. The index candidates are columns that regularly appear in WHERE, JOIN conditions and ORDER BY:

MySQL 8.1
CREATE INDEX idx_orders_user_id ON orders (user_id);

We measured how much this changes the picture on a table of one million orders (PostgreSQL 17). Looking up one customer's orders before and after creating the index:

without index:  Seq Scan, all 1,000,000 rows scanned — 23.0 ms
with index:     Index Scan, only the 20 matching rows read — 0.1 ms

A two-hundred-fold difference on the exact same query — and it grows with the table. The absolute numbers depend on the hardware; the order of magnitude does not.

Check the foreign keys separately: primary keys are indexed automatically, but referencing columns (user_id in the orders table) are not — even though that is exactly what joins run on. The details of how indexes work are in the lesson Indexes in SQL.

The flip side: every index slows down inserts and updates, because it has to be maintained. Index what you search by, not everything in sight.

3. Don't Wrap an Indexed Column in a Function

A condition that wraps a column in a function strips the DBMS of the ability to use the index — it has to evaluate the function for every row:

MySQL 8.1
-- the index on order_date is not used
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024;

Seq Scan on orders  (cost=0.00..11.84 rows=2 width=44)
  Filter: (EXTRACT(year FROM order_date) = '2024'::numeric)

Rewrite the condition so the column stays "bare" — as a range:

MySQL 8.1
-- the index on order_date works
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

The same rule is broken by WHERE LOWER(email) = ..., WHERE price * 1.2 > 100, WHERE CAST(...). The universal trick: move the computation from the column to the constant (price > 100 / 1.2).

The price of the mistake on a million rows with an index on order_date — a one-day filter written two ways:

WHERE order_date::date = '2025-03-01'                plan: Seq Scan — 24.1 ms
WHERE order_date >= '2025-03-01'
  AND order_date < '2025-03-02'                      plan: Index Only Scan — 0.16 ms

Both queries return the same rows, but the first is some hundred and fifty times slower: the ::date cast is the same function around the column.

The query SELECT * FROM users WHERE YEAR(created_at) = 2026 is slow even though created_at is indexed. What is the cause?

4. Drop SELECT *

SELECT * drags in every column: heavy text fields, JSON, everything you don't need. That is extra disk I/O, extra traffic to the application, and a missed opportunity: when a query can be satisfied by columns already present in an index, the DBMS may answer without opening the table at all. List only what you use:

MySQL 8.1
SELECT order_id, total_amount FROM orders WHERE user_id = 1;

5. Beware of LIKE That Starts with a Percent

The pattern LIKE 'SUMMER%' uses an index: it is a prefix search, like in a dictionary. The pattern LIKE '%SUMMER%' does not: the match can start anywhere, so the DBMS has no choice but to scan every row.

If substring search is a regular need on a large table, that is a case for full-text search (FULLTEXT in MySQL, tsvector/pg_trgm in PostgreSQL), not for LIKE.

Which of the LIKE patterns can use a regular index on the name column?

6. Review the Subqueries: EXISTS vs IN

For "does a related record exist" checks prefer EXISTS — it stops at the first match, while IN first materializes the whole list:

MySQL 8.1
SELECT u.user_id
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

A separate trap is NOT IN with a subquery: if the list contains even a single NULL, the condition returns no rows at all. NOT EXISTS is free of that problem and is usually faster.

7. Filter Before Aggregating, Not After

WHERE discards rows before grouping, HAVING after. Conditions that do not depend on aggregates always belong in WHERE:

MySQL 8.1
-- wasted work: grouped everything, then threw half away
SELECT user_id, SUM(total_amount)
FROM orders
GROUP BY user_id
HAVING MIN(order_date) >= '2024-01-01';

-- if the condition is about individual rows, filter right away
SELECT user_id, SUM(total_amount)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY user_id;

Note that these two queries differ in meaning — the second sums only the 2024 orders. The general principle holds: the earlier the DBMS gets rid of unneeded rows, the less work every following step has to do.

8. Pagination: OFFSET Reads Everything It Skips

The classic pagination LIMIT 20 OFFSET 100000 makes the DBMS read and discard a hundred thousand rows to return twenty. The deeper the page, the slower the query.

The solution is keyset pagination: remember the last value shown and continue from it via the index:

MySQL 8.1
SELECT order_id, order_date
FROM orders
WHERE order_id > 100020
ORDER BY order_id
LIMIT 20;

This query runs equally fast for the first page and for the thousandth. On the same million-row table: a page after OFFSET 500000 takes 79.6 ms, the keyset query with WHERE order_id > 500000 — 0.05 ms.

9. Composite Index: Column Order Decides

If a query filters by two columns at once, two separate indexes are worse than one composite:

MySQL 8.1
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

The rule: an index on (user_id, status) serves conditions on user_id and on the pair user_id + status, but it does not help a query that filters only by status — just like a phone book sorted by last name is useless for searching by first name. Put first the column that filters more often and more selectively.

10. Eliminate N+1 and Row-by-Row Changes

Sometimes slow SQL is not one query but a thousand small ones. The classic is a loop in the application: fetch the list of orders, then fetch each order's customer with a separate query. The database executes N+1 queries instead of one JOIN:

MySQL 8.1
SELECT o.order_id, u.email
FROM orders o
JOIN users u ON u.user_id = o.user_id;

The same logic applies to writes: inserting a hundred rows with one INSERT INTO ... VALUES (...), (...), ... is several times faster than a hundred separate INSERT statements — each statement pays the network round-trip and the transaction overhead.

Where to Dig Deeper

This checklist covers the typical cases, but each item has its own depth behind it: index selectivity, covering and partial indexes, the optimizer's cost model, JOIN and sort optimization. A dedicated course, SQL Query Optimization in PostgreSQL, is built around exactly that — with live query plans and measurements for every technique.

What's Next

• how indexes work and what kinds exist — in the lesson Indexes in SQL;
• a fresh breakdown of ranking functions for reports — in the article ROW_NUMBER, RANK and DENSE_RANK;
• level up query performance systematically — in the course SQL Optimization in PostgreSQL.