DEV Community

Cover image for MySQL EXPLAIN: Read the Output and Fix Slow Queries
devTalk
devTalk

Posted on Originally published at dev-talk.com

MySQL EXPLAIN: Read the Output and Fix Slow Queries

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode
type=ref key=idx_user rows=25 Extra=Using filesort
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode
type=ref key=idx_user_created rows=25 Extra=Backward index scan
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode
type=range key=idx_created rows=1440 Extra=Using index condition
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode
type=ALL key=NULL rows=498489 filtered=11.11 Extra=Using where
Enter fullscreen mode Exit fullscreen mode

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%';
Enter fullscreen mode Exit fullscreen mode
type=range key=idx_ref rows=100 Extra=Using where; Using index
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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_at indexed, MySQL reads the whole week (7 days at 1,440 rows a day) and filters on status afterwards. 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 status condition 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 showed status = 'pending' AND created_at range 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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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');
        });
    }
};
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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

  1. Run EXPLAIN again: expect a lower type, the right key, a small rows, and no unwanted Using filesort or Using temporary.
  2. Run EXPLAIN ANALYZE if the estimate looks suspicious, comparing rows with the actual rows.
  3. Check the slow log or the performance schema digest from Part 1: the query should stop appearing, or its Rows_examined should drop close to Rows_sent.
  4. If a plan looks wrong after big data changes, run ANALYZE TABLE to 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 the WHERE clause.
  • A leading wildcard in LIKE.
  • Reading rows as an exact count.
  • Judging a query by COUNT(*) or SELECT id only: a covering index can make a scan look better than the real SELECT * 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

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


Originally published on DEV Talk.

Top comments (0)