SQL performance optimisation: find slow queries and fix them with EXPLAIN

"The database is slow" is rarely true. Usually one or two queries are slow, and fixing them is a matter of method, not magic. Guessing and adding random indexes wastes time; a reliable process finds the problem, measures it, changes one thing and measures again. This post gives that process and the fixes that solve most real cases. The examples ran on MySQL 8.4 with a table of 100,000 employee rows (the same table as in the index guide), and the numbers shown are from real runs on a laptop. Treat them as illustrations; your timings will differ, but the ratios and the plan shapes will hold.

The method

1. Find the queries that cost the most (slow query log, monitoring, the application's own timing).
2. Look at the plan with EXPLAIN, and the real work with EXPLAIN ANALYZE.
3. Change one thing: an index, a rewritten condition, fewer columns, a different shape of query.
4. Measure again. Keep the change only if it helps.
5. Check the whole workload, because an index that helps one query slows every write.

Step 1: find the slow queries

MySQL can log every statement slower than a threshold:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;        -- seconds

The log file shows the statement, its time and the number of rows examined. A query that examines 100,000 rows to return 5 is the classic target. Managed hosting often has a query dashboard that does the same, and performance_schema keeps statistics per statement pattern. Look at both the slowest queries and the most frequent ones: a 20 ms query run 50 times per page load matters more than a 5 s report that runs weekly.

Step 2: read the plan

EXPLAIN shows how MySQL intends to run the statement without running it:

EXPLAIN SELECT * FROM employees WHERE last_name = 'L77';

Column	What to look for
type	ALL means a full table scan. const, eq_ref, ref, range and index are better, in roughly that order.
key	The index actually used. NULL means none.
rows	Estimated rows to examine. Compare with how many you return.
Extra	Using filesort and Using temporary are costly. Using index (a covering index) is good.

EXPLAIN ANALYZE (MySQL 8.0.18 and later) runs the query and shows the actual rows and times next to the estimates, which exposes plans where the optimiser guessed wrong. Use it on SELECT statements; remember that it really executes them.

Fix 1: add the right index

Without an index on last_name, the plan above shows type: ALL, rows: 100005. After CREATE INDEX idx_lastname ON employees (last_name) it shows type: ref, rows: 50. For queries with several conditions, a composite index in the right column order does better than separate ones; the leftmost-prefix rule is explained in the index guide.

Index the columns that appear in WHERE, JOIN ... ON, ORDER BY and GROUP BY. For example, a grouped count:

EXPLAIN SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

Before the index	After CREATE INDEX idx_dept ON employees (department_id)
type: ALL, Using temporary (builds a temporary table)	type: index, key: idx_dept, Using index

Fix 2: do not hide columns inside functions

An index is sorted by the stored value. If you apply a function to the column, MySQL must compute the function for every row, so it cannot use the index.

Slow (cannot use the index)	Fast (can use it)
WHERE YEAR(hired) = 2020	WHERE hired >= '2020-01-01' AND hired < '2021-01-01'
WHERE salary + 100 = 40100	WHERE salary = 40000
WHERE email LIKE '%9@example.com'	WHERE email LIKE 'user99%'
WHERE LOWER(name) = 'asha'	compare with a case-insensitive collation, or index a generated column

In my test the first form of the date query examined all 100,005 rows (type: ALL), while the range form used idx_hired (type: range). Mismatched types do the same damage: WHERE email = 12345 on a text column forced MySQL to scan the whole index instead of looking up a value. Always compare a column with a value of the same type.

Fix 3: select only what you need

SELECT * reads and transfers every column, including wide text and blobs you never use. It also prevents covering indexes: if the index contains all the requested columns, MySQL never touches the table.

EXPLAIN SELECT *               FROM employees WHERE salary BETWEEN 40000 AND 40200;
EXPLAIN SELECT employee_id, salary FROM employees WHERE salary BETWEEN 40000 AND 40200;

Both used idx_salary with an estimate of 429 rows, but the second shows Using where; Using index: the answer came entirely from the index, with no lookups into the table. Name your columns, which also protects the code when someone adds a column later.

Fix 4: paginate with keys, not deep OFFSET

LIMIT 10 OFFSET 90000 looks cheap but is not: the database must produce and skip the first 90,000 rows before returning ten. Compare with a "seek" on the last id seen:

-- deep offset
EXPLAIN ANALYZE SELECT employee_id, email FROM employees
ORDER BY employee_id LIMIT 10 OFFSET 90000;

-- keyset pagination
EXPLAIN ANALYZE SELECT employee_id, email FROM employees
WHERE employee_id > 90000 ORDER BY employee_id LIMIT 10;

Query	Rows read	Actual time (this run)
OFFSET 90000	90,010	about 17.6 ms
WHERE employee_id > 90000	10	about 0.05 ms

The gap grows with the page number: page 1 costs the same either way, page 9,000 does not. The application remembers the last employee_id of the current page and asks for the rows after it. The trade-off is that you can go "next" and "previous" but not jump to an arbitrary page; for admin screens with moderate data, plain OFFSET is fine.

Fix 5: let the index do the sorting

ORDER BY salary LIMIT 5 without an index reads and sorts the whole table: type: ALL, rows: 100005, Using filesort. With an index on salary, MySQL reads the first five index entries in order: type: index, rows: 5, no sort. When you need ORDER BY a, b with a WHERE on a, a composite index on (a, b) can filter and sort in one pass.

Fix 6: avoid the N+1 query problem

The slowest query pattern is often not one query but a loop of them. The application fetches 100 orders, then runs one query per order to fetch its customer, 101 round trips instead of one.

-- N+1: one query, then one more per row
SELECT * FROM orders LIMIT 100;
SELECT * FROM customers WHERE id = 1;
SELECT * FROM customers WHERE id = 2;
-- ...

-- one query instead
SELECT o.order_id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
LIMIT 100;

Each round trip has fixed overhead for the network and parsing, however fast the query is. ORMs hide this problem; look for "eager loading" or JOIN options, and watch the query count per page. The same applies to writes: insert many rows with one multi-row INSERT or a transaction instead of one commit per row.

INSERT INTO log_entries (user_id, action) VALUES (1,'a'), (2,'b'), (3,'c');

Fix 7: write queries the optimiser can use

• Filter early. Put conditions in WHERE, not in application code after fetching everything.
• Use EXISTS for existence checks instead of counting: WHERE EXISTS (SELECT 1 ...) can stop at the first match, where COUNT(*) > 0 counts them all.
• Prefer UNION ALL to UNION when duplicates are impossible or fine; UNION sorts to remove duplicates.
• Beware of OR across different columns. WHERE last_name = 'L77' OR salary = 30500 worked in my test only because both columns had indexes (MySQL merged them, Using sort_union). With one side unindexed it falls back to a full scan; a UNION of two indexed queries is another option.
• Choose join order and indexes deliberately. Join columns should be indexed and of the same type. See the joins guide.
• Replace correlated subqueries with joins, derived tables or window functions when they run per row (see subqueries).
• Avoid SELECT DISTINCT as a bandage. If you need DISTINCT to hide duplicate rows, a join is probably multiplying them; fix the join.
• Use COUNT(*) to count rows (MySQL picks the cheapest index for it). COUNT(column) skips NULLs and means something different.

Fix 8: design and data types

Performance starts in the schema:

• Use the smallest suitable data type: INT instead of BIGINT where the range allows, DATE instead of a text date, DECIMAL for money.
• A good primary key: a short, increasing integer keeps inserts fast and indexes small.
• Normalise first, then denormalise only measured hot spots; see normalisation.
• Archive old data out of hot tables, or partition very large tables by date.
• Keep statistics fresh: ANALYZE TABLE employees; helps the optimiser choose well after big data changes.

Fix 9: use caching and the database features

A repeated query on slowly changing data should not hit the database every time. Cache the result in the application (or a store such as Redis) with a sensible expiry. MySQL 8 removed its old built-in query cache, so this layer is your job. For heavy reports, a pre-computed summary table refreshed on a schedule beats re-aggregating millions of rows for every page view. Transactions should be short, since long ones hold locks and make other queries wait.

What not to do

• Do not add an index to every column. Each one slows writes and uses memory.
• Do not tune without measuring. Compare EXPLAIN and timing before and after, on realistic amounts of data. A query that is instant on 100 rows tells you nothing about 10 million.
• Do not judge by a single timing: the second run is usually faster because data is cached. Run a query several times.
• Do not tune the database to compensate for a query that should not exist (loops, repeated identical calls).
• Do not run EXPLAIN ANALYZE on a destructive statement on production data.

A worked checklist

When someone says "this page is slow":

1. Which query? Check the slow log or time each query in the code.
2. EXPLAIN it. Is type ALL? Is there Using filesort or Using temporary?
3. Are the WHERE and JOIN columns indexed? Are there functions or type mismatches on them?
4. Does it fetch more columns or rows than the page needs?
5. Is it one query or hundreds (N+1)?
6. Is the result cacheable?
7. Re-measure.

Common interview questions

• How do you optimise a slow SQL query? Find it, read the execution plan, then fix indexes, conditions, selected columns and query shape, and measure again.
• What does EXPLAIN show? The access type, chosen index, estimated rows and extra operations such as sorting.
• Why is SELECT * discouraged? More data to read and transfer, and it blocks covering indexes.
• Why is OFFSET slow for large pages? The database must generate and discard all skipped rows; keyset pagination avoids that.
• What is the N+1 problem? One query to get a list plus one per item, instead of a single join.

Next steps

The biggest single lever is indexing, so read the index guide next. Then practise on real data with the MySQL course, keep the MySQL cheat sheet for the EXPLAIN and index commands, and test yourself with the MySQL interview questions.