Most slow MySQL queries are slow for one reason: MySQL reads far more rows than the answer needs. Optimising a query means giving MySQL a way to read fewer rows (usually an index) and writing the query so it can use that way. This page takes one 50,000-row table and fixes five common problems in turn, checking each fix with EXPLAIN.
The sample table
Copy this into any MySQL 8 database. The recursive CTE generates 50,000 orders spread over 2024 and 2025 across five cities.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
city VARCHAR(30) NOT NULL,
phone VARCHAR(15) NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
SET SESSION cte_max_recursion_depth = 100000;
INSERT INTO orders
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 50000)
SELECT i,
1 + MOD(i * 7, 5000),
ELT(1 + MOD(i, 5), 'Delhi','Mumbai','Pune','Jaipur','Indore'),
CONCAT('98', LPAD(i, 8, '0')),
DATE('2024-01-01') + INTERVAL MOD(i, 730) DAY,
100 + MOD(i * 37, 4900)
FROM n;
ANALYZE TABLE orders;
In the EXPLAIN results below, watch three columns: type (ALL means a full table scan), key (the index used) and rows (MySQL's estimate of rows it will examine). The rows figures are estimates, so yours may differ slightly.
Problem 1: a function wrapped around the column
A December 2025 sales report is often written like this:
SELECT order_id, amount FROM orders
WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 12;
Even after CREATE INDEX idx_orders_date ON orders (order_date);, a WHERE YEAR(order_date) = 2025 filter still scans the table, because the index is sorted by the date, not by YEAR() of the date:
| type | key | rows | Extra |
|---|---|---|---|
| ALL | NULL | 50224 | Using where |
Rewrite the condition as a range on the bare column:
EXPLAIN SELECT order_id, amount FROM orders
WHERE order_date >= '2025-12-01' AND order_date < '2026-01-01';
| type | key | rows | Extra |
|---|---|---|---|
| range | idx_orders_date | 2040 | Using index condition |
2,040 rows examined instead of about 50,000, and a COUNT(*) confirms exactly 2,040 orders match. The half-open range (>= start, < next start) also avoids missing rows on the last day when the column is a DATETIME. MySQL 8.0.13 and later also support functional indexes such as CREATE INDEX idx_yr ON orders ((YEAR(order_date)));, but a plain range is simpler and works on every version.
Problem 2: the wrong column order in a composite index
Now filter by city as well and sort by date:
EXPLAIN SELECT order_id, order_date, amount FROM orders
WHERE city = 'Pune' AND order_date >= '2025-12-01'
ORDER BY order_date;
With only the date index, MySQL reads all 2,040 December rows and then throws away the 80% that are not Pune (filtered = 10.00, Using where). Add a composite index with the equality column first and the range column second:
CREATE INDEX idx_orders_city_date ON orders (city, order_date);
| type | key | rows | Extra |
|---|---|---|---|
| range | idx_orders_city_date | 408 | Using index condition |
Now MySQL jumps straight to the Pune entries for December, already in date order, so there is no Using filesort either. An index on (order_date, city) would be much less useful, because once a range starts on the first column, the second column cannot narrow the search the same way.
Problem 3: fetching columns you do not need
Drop amount from the select list and the same index answers the whole query:
EXPLAIN SELECT order_id, order_date FROM orders
WHERE city = 'Pune' AND order_date >= '2025-12-01';
| type | key | rows | Extra |
|---|---|---|---|
| range | idx_orders_city_date | 408 | Using where; Using index |
Using index means a covering index: every needed column is inside the index (InnoDB secondary indexes always carry the primary key, so order_id comes free). No table rows are read at all. This is why SELECT * in application code costs more than it looks.
Problem 4: comparing a string column with a number
The phone column is a VARCHAR with its own index. Compare the two queries:
CREATE INDEX idx_orders_phone ON orders (phone);
EXPLAIN SELECT order_id FROM orders WHERE phone = 9800012345; -- number
EXPLAIN SELECT order_id FROM orders WHERE phone = '9800012345'; -- string
| literal | type | rows | Extra |
|---|---|---|---|
| number | index | 50224 | Using where; Using index |
| string | ref | 1 | Using index |
With a numeric literal, MySQL has to convert every stored phone string to a number before comparing, so it walks the entire index. Quote the value (or bind it as a string parameter) and the lookup touches one row.
Problem 5: deep OFFSET pagination
LIMIT 10 OFFSET 40000 looks cheap but MySQL reads and discards 40,000 rows first (rows = 40010). Keyset pagination remembers the last ID shown and starts from there:
-- page after the one that ended at order_id 40000
SELECT order_id, amount FROM orders
WHERE order_id > 40000
ORDER BY order_id
LIMIT 10;
This is a primary-key range read that stops after 10 rows, however deep the page is.
A checklist before you blame the server
- Run
EXPLAIN(orEXPLAIN ANALYZE, available from MySQL 8.0.18, which runs the query and shows actual times). - Look for
type = ALLon large tables,Using filesortandUsing temporary. - Keep indexed columns bare in
WHERE: no functions, no arithmetic, matching data types. - A leading wildcard (
LIKE '%sharma') cannot use a normal index; use a full-text index for word search. - Do not add an index for every query. Each one slows writes, so prefer a few well-ordered composite indexes.
Two myths to drop: in MySQL 8, IN (subquery) and EXISTS are usually rewritten into the same semi-join plan, so swapping one for the other rarely helps by itself; and COUNT(*) is not slower than COUNT(1).
Next steps: learn to read every column of the plan in the EXPLAIN statement guide, see how B-tree indexes work in MySQL indexes explained, and find the queries worth fixing with the slow query log in how to find and fix slow queries. Server-level settings such as the buffer pool are covered in MySQL performance tuning tips.