Make Slow SQL Queries Fast: Indexes, EXPLAIN, and Pagination

A slow database is behind most slow applications. The good news is that a handful of techniques fix the majority of problems: reading query plans, adding the right indexes, and avoiding a few common query patterns.

The examples below use MySQL 8, but the ideas apply to PostgreSQL and other relational databases too.

The example schema

Imagine an online shop with an orders table that has grown to a few million rows:

CREATE TABLE orders (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    status      VARCHAR(20) NOT NULL,
    total       DECIMAL(10,2) NOT NULL,
    created_at  DATETIME NOT NULL
);

A page that lists a customer's recent orders runs this query, and it has become painfully slow:

SELECT id, total, created_at
FROM orders
WHERE user_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Let us find out why.

Step 1: Read the query plan with EXPLAIN

Put EXPLAIN in front of any query to see how the database plans to run it:

EXPLAIN SELECT id, total, created_at
FROM orders
WHERE user_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Pay attention to these columns in the output:

type: ALL means a full table scan, which is what you want to avoid. Better values are ref, range, and const.
key: the index MySQL actually chose. NULL means no index was used.
rows: the estimated number of rows examined. Lower is better.
Extra: watch for Using filesort and Using temporary, which signal extra sorting work.

If you see type: ALL and rows in the millions, you have found your problem. MySQL 8 also offers EXPLAIN ANALYZE, which runs the query and shows real timings.

Step 2: Add the right index

The query filters on user_id and status, then sorts by created_at. A composite index that matches this pattern helps a lot:

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);

Run EXPLAIN again. The type should now be ref or range, rows should be tiny, and the filesort should be gone, because the index already stores rows in the order needed.

Column order matters

A composite index follows the leftmost prefix rule. The index above can serve queries that filter on:

user_id
user_id and status
user_id, status, and created_at

But it cannot efficiently serve a query that filters only on status. A good rule of thumb is to put equality columns first, then range or sorting columns last.

Do not over index

Every index speeds up reads but slows down writes and uses disk space. Add indexes for queries you actually run, and drop unused ones. Do not index every column "just in case".

Step 3: Stop writing queries that ignore indexes

Even with a perfect index, some query patterns prevent its use.

Functions on indexed columns:

-- Slow: index on created_at cannot be used
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Fast: a range condition can use the index
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

Leading wildcards in LIKE:

-- Slow: cannot use a normal index
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Fast: a prefix search can use the index
SELECT * FROM users WHERE email LIKE 'rahim%';

For real text search, use a full text index instead.

Type mismatches: comparing a string column to a number forces conversion and can disable the index. Keep the types consistent.

Selecting everything: SELECT * transfers data you do not need and prevents covering indexes. Ask only for the columns you use.

Step 4: Use covering indexes

When an index contains every column a query needs, the database never touches the table itself. For a query that only needs user_id, status, and total:

CREATE INDEX idx_orders_covering
ON orders (user_id, status, total);

EXPLAIN will show Using index in the Extra column, which means the query was answered from the index alone. This is a powerful optimization for hot queries, but use it selectively.

Step 5: Fix slow pagination

Classic pagination gets slower as users go deeper:

SELECT id, total FROM orders
ORDER BY id
LIMIT 20 OFFSET 1000000;

MySQL must read and discard a million rows before returning 20. Use keyset pagination instead, where you remember the last seen ID:

SELECT id, total FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 20;

This uses the primary key and stays fast on page one or page fifty thousand. The tradeoff is that users cannot jump to an arbitrary page number, which is fine for infinite scroll and "next" buttons.

Step 6: Find slow queries in the first place

You cannot fix what you do not measure. Enable the slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

This logs every query taking longer than one second. Set the values permanently in your MySQL config file so they survive restarts. Review the log regularly, or use pt-query-digest from Percona Toolkit to summarize which queries cost the most total time.

Step 7: Reduce the work your app asks for

Some fixes live in application code, not SQL:

Avoid N+1 queries. Load related data with a join or a single WHERE id IN (...) query, not one query per row.
Cache results that are read often and change rarely.
Batch writes. Insert many rows in one statement instead of thousands of single inserts.
Keep transactions short. Long transactions hold locks and block other work.

Quick checklist

Run EXPLAIN on every query that feels slow
Add composite indexes that match your WHERE and ORDER BY patterns
Avoid functions on indexed columns and leading wildcards
Select only the columns you need
Use keyset pagination for large tables
Turn on the slow query log and review it
Remove indexes that nothing uses

Final thoughts

Query tuning is mostly a loop: measure, read the plan, change one thing, measure again. Do not guess and do not add indexes blindly. With EXPLAIN and a few solid habits, you can turn a multi second query into one that finishes in milliseconds.

What is the slowest query you have ever fixed? Share the story in the comments.