Part 1 found five slow queries in a log. Finding them is half the job. This part asks the other question: why is each one slow, and what is the smallest change that fixes it? The tool for that is EXPLAIN, and the fixes are almost always an index or a rewritten condition.
Every example uses the same shop database as Part 1: 20,000 users and 500,000 orders on MySQL 8.0.46. The only index on orders at the start is the primary key.
Reading EXPLAIN without memorizing it
EXPLAIN
SELECT * FROM orders
WHERE user_id = 1234
ORDER BY created_at DESC
LIMIT 20;
Of the columns in the output, six do most of the work:
| Column | What to look for |
|---|---|
type |
How rows are fetched. From best to worst the manual lists system, const, eq_ref, ref, range, index, ALL. ALL is a full table scan. index is the same as ALL except that the index tree is scanned instead of the table. |
key |
The index actually chosen. NULL means none. |
key_len |
How many bytes of the index are used. Useful to see how much of a composite index a query uses. |
rows |
The optimizer's estimate of rows to examine. It is not a count. |
filtered |
Estimated percentage of those rows that survive the table's conditions. |
Extra |
Details such as Using filesort, Using temporary, Using index (covering index), Using index condition. |
Because rows is an estimate, add EXPLAIN ANALYZE when you doubt it. According to the manual it exists from MySQL 8.0.18, runs the statement, always uses the tree format, and reports actual time, row counts and loop counts. Do not run it on a DELETE or UPDATE you are not ready to execute. Plain EXPLAIN FORMAT=TREE (8.0.16 and later) shows the plan without running it.
In the examples below I show only the columns that matter, one line per table.
Case 1: filter on one column, sort on another
The Part 1 query: the latest 20 orders for one user. With no index:
type=ALL key=NULL rows=498489 filtered=10.00 Extra=Using where; Using filesort
A full scan of half a million rows, then a sort. The obvious fix is an index on user_id:
ALTER TABLE orders ADD INDEX idx_user (user_id);
type=ref key=idx_user rows=25 Extra=Using filesort
Now only about 25 rows are touched, but they still have to be sorted. Make the index match both the filter and the sort:
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
type=ref key=idx_user_created rows=25 Extra=Backward index scan
The filesort is gone. The index is already ordered by created_at within each user_id, so MySQL reads it backwards and stops after 20 rows. This follows the manual's ORDER BY optimization rules: an index can satisfy the sort when the ORDER BY follows the index columns and the leading columns are fixed by equality conditions in the WHERE clause. With both indexes present the optimizer chose the composite one (possible_keys listed both), and idx_user became redundant.
EXPLAIN ANALYZE confirms what really happened:
-> Limit: 20 row(s) (rows=20) (actual time=... rows=20 loops=1)
-> Index lookup on orders using idx_user_created (user_id=1234) (reverse) (rows=25) (actual time=... rows=20 loops=1)
In my two runs the query took roughly 130 to 160 ms with no index and about 0.1 ms with the composite index. Treat those as the order of magnitude on one machine, not a benchmark.
Case 2: a function on the indexed column
This is the Laravel favorite. whereDate('created_at', '2026-03-15') does not generate a date comparison; the query it produced on Laravel 13.40 was:
select * from `orders` where date(`created_at`) = '2026-03-15'
With an index on created_at in place, MySQL still cannot use it to seek, because the index holds created_at values, not DATE(created_at) values. The plan for the SELECT * form:
type=ALL key=NULL rows=498489 Extra=Using where
Rewrite the condition as a range on the bare column:
SELECT * FROM orders
WHERE created_at >= '2026-03-15' AND created_at < '2026-03-16';
type=range key=idx_created rows=1440 Extra=Using index condition
Same result (I compared counts), 1,440 estimated rows instead of half a million. In Laravel:
$day = Carbon::parse('2026-03-15');
Order::where('created_at', '>=', $day->startOfDay())
->where('created_at', '<', $day->copy()->addDay()->startOfDay())
->get();
Careful: startOfDay() mutates a mutable Carbon, which is why the second bound uses copy(). Laravel's date helpers return CarbonImmutable if you configure it, in which case copy() is harmless.
The whereBetween variant with endOfDay() generated between '2026-03-15 00:00:00' and '2026-03-15 23:59:59'. It can use the index, but it misses rows with fractional seconds after 23:59:59 if the column has them. The half-open range (>= start, < next day) has no such gap.
The trap: a covering index hides the problem
For SELECT COUNT(*) with the function, the plan was type=index key=idx_created rows=498489 Extra=Using where; Using index. That is not a seek. MySQL scanned the whole index, which is cheaper than scanning the table but still reads every entry. The estimate of nearly 500,000 rows is the tell. Rewriting it as a range gave type=range rows=1440.
Case 3: LIKE with a leading wildcard
With an index on reference, a search for the order whose reference ends in a fragment:
SELECT * FROM orders WHERE reference LIKE '%0042';
type=ALL key=NULL rows=498489 filtered=11.11 Extra=Using where
The manual says a LIKE pattern can use an index when it is a constant that does not start with a wildcard, so 'Patrick%' can and '%Patrick%' cannot. Anchoring the start worked as described:
SELECT id FROM orders WHERE reference LIKE 'ORD-00004%';
type=range key=idx_ref rows=100 Extra=Using where; Using index
If you genuinely need "contains" search over large text, a B-tree index is the wrong tool. Look at a FULLTEXT index, or at a search engine. I did not test those here.
Case 4: column order in a composite index
The query: pending orders in a 7-day window.
SELECT id, total FROM orders
WHERE status = 'pending'
AND created_at >= '2026-06-01' AND created_at < '2026-06-08';
It returns 2,016 rows. I tried three index setups, one at a time:
| Index | EXPLAIN rows (estimate) |
InnoDB rows read |
|---|---|---|
(created_at) |
10,080 | 10,080 |
(created_at, status) |
18,766 | 2,016 |
(status, created_at) |
2,016 | 2,016 |
"InnoDB rows read" is the Innodb_rows_read session status counter (from performance_schema.session_status), measured as the difference around the query. Three things come out of it:
- With only
created_atindexed, MySQL reads the whole week (7 days at 1,440 rows a day) and filters onstatusafterwards. Four out of five rows read are thrown away. - Both composite indexes got the work down to 2,016 rows on this data, because the
statuscondition is checked inside the index before the table row is fetched. - The estimate is where they differ. With
(created_at, status)the optimizer guessed 18,766 rows, nine times too high; with(status, created_at)it was exact, and the plan showedstatus = 'pending' AND created_atrange as a single bounded range.
So I would not claim one order reads fewer rows here. The usual rule is to put equality columns first and the range column last, and the numbers agree with it where the optimizer's choices are concerned: that order gave an accurate estimate, and a bad estimate is what pushes the optimizer into wrong plans on bigger queries. Test with your own data, because selectivity decides this.
Case 5: the leftmost prefix, and where skip scan changes the picture
The manual's rule for multi-column indexes is that the index helps queries that use a leftmost prefix of its columns. A query filtering only on the second column of (user_id, created_at) cannot do a normal lookup.
In practice, on 8.0.46, the result depended on what the query selected:
-- covering query on created_at only, index (user_id, created_at)
type=range key=idx_user_created rows=55376 Extra=Using where; Using index for skip scan
-- same condition with SELECT *
type=ALL key=NULL rows=498489 Extra=Using where
MySQL can use skip scan: it jumps between distinct user_id values and applies the range on created_at inside each. It only appeared when the index covered the query, and it worked on an index with a low-cardinality leading column like status as well. With SELECT * it fell back to a full scan. Do not design around it. If you query by created_at alone, index created_at.
Case 6: joins need an index on the join column
SELECT u.country, COUNT(*) AS c
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid' AND u.country = 'LK'
GROUP BY u.country
ORDER BY c DESC
LIMIT 5;
Without an index on orders.user_id the plan scanned orders (type=ALL, 498,489 rows) and looked up each user by primary key (eq_ref). Primary key lookups are cheap, but half a million of them add up. After adding an index that starts with the join column:
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
The join order flipped. users became the outer table (scanned, filtered by country, about 20,000 rows) and orders was reached with type=ref on idx_user_status, about 24 rows per user, Extra=Using index because the index covers the query. The tree format shows it:
-> Nested loop inner join
-> Filter: (u.country = 'LK')
-> Table scan on u
-> Covering index lookup on o using idx_user_status (user_id=u.id, status='paid')
Note that the optimizer is free to reorder joins. If an index appears to be ignored, check whether a different table became the driver. A foreign key declared in InnoDB gets an index automatically if none exists, but a plain user_id column does not, and Laravel's foreignId() without constrained() creates no index.
Adding the index safely in Laravel
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->index(['user_id', 'created_at'], 'idx_user_created');
});
}
public function down(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->dropIndex('idx_user_created');
});
}
};
On a big production table, building an index takes time and the table is busy meanwhile. Run it off-peak and check the MySQL manual's online DDL page for what your version does with concurrent writes. I only built indexes on a test table with no traffic.
You can also explain a query from the application. The query builder has explain(); on Laravel 13.40 it returned a collection, and its first row for the unindexed query was type=ALL key=NULL Extra=Using where; Using filesort.
Order::where('user_id', 1234)
->orderByDesc('created_at')
->limit(20)
->explain()
->dd();
The cost of indexes
Every index has to be maintained on each insert, update of its columns and delete, and it takes disk space and buffer pool memory. I did not measure the write overhead here, so I can't give a number. The practical rules are: index for queries you actually run, prefer one composite index over several overlapping ones (idx_user was made redundant by idx_user_created in Case 1), and drop indexes that no query uses.
Verify the fix
- Run
EXPLAINagain: expect a lowertype, the rightkey, a smallrows, and no unwantedUsing filesortorUsing temporary. - Run
EXPLAIN ANALYZEif the estimate looks suspicious, comparingrowswith the actual rows. - Check the slow log or the performance schema digest from Part 1: the query should stop appearing, or its
Rows_examinedshould drop close toRows_sent. - If a plan looks wrong after big data changes, run
ANALYZE TABLEto refresh index statistics. I did not need it in these tests.
Common mistakes
- Adding an index and never checking that the optimizer uses it.
- Wrapping an indexed column in a function (
DATE(),LOWER(), arithmetic) in theWHEREclause. - A leading wildcard in
LIKE. - Reading
rowsas an exact count. - Judging a query by
COUNT(*)orSELECT idonly: a covering index can make a scan look better than the realSELECT *will be. - Creating a single-column index for every column "just in case".
Alternatives
- Rewrite instead of index. Case 2 needed no new index at all, just a condition that matched the existing one.
- Cache the result. If the query is right but runs too often, see the Redis caching series.
- Reduce the data. Archive or partition old rows if the table only grows. I did not test partitioning.
Related Dev Talk articles
- MySQL Slow Query Log and EXPLAIN, Part 1: Find Slow Queries with the Slow Query Log
- Laravel Redis Caching Patterns, Part 1: Cache-Aside, Key Design, Invalidation and Tags
- PostgreSQL vs MySQL: Performance Comparison (Tested on the Same Machine)
Frequently asked questions
Does EXPLAIN run my query?
Plain EXPLAIN does not. EXPLAIN ANALYZE does, so use it with care on statements that change data.
Why does MySQL ignore my index?
Common causes seen above: a function on the column, a leading wildcard, a query on a non-leading column of a composite index, or a plan where scanning is estimated to be cheaper. Compare possible_keys with key.
Is a full table scan always bad?
No. On a small table, or when a query needs most of the rows, a scan can be the cheapest plan. It is a problem when the rows returned are a tiny fraction of the rows examined.
Sources
- MySQL 8.0 manual: EXPLAIN Output Format
- MySQL 8.0 manual: EXPLAIN Statement
- MySQL 8.0 manual: ORDER BY Optimization
- MySQL 8.0 manual: Multiple-Column Indexes
- MySQL 8.0 manual: How MySQL Uses Indexes
Originally published on DEV Talk.
Top comments (0)