Skip to content
IntoCoder

Database optimization2 min read

Reading MySQL EXPLAIN: the four columns that matter first

A practical way to read EXPLAIN output and fix a slow query in a Laravel application with the right composite index.

By IntoCoder

A page is slow, and the cause is almost always one query. EXPLAIN tells you how MySQL plans to run it. The output has a dozen columns, but four of them answer most questions.

A slow query

Take an orders list filtered by customer and sorted by date:

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

The four columns

type is how the table is accessed. ALL means a full table scan: every row is read. ref and range mean an index narrowed the search. const means a single-row lookup. If you see ALL on a large table, that is your problem.

key is the index MySQL chose. NULL means none. Compare it with possible_keys: if an index is possible but not chosen, the optimizer judged it unhelpful, often because it matches too many rows.

rows is the estimated number of rows examined. It is an estimate, but the order of magnitude is what counts. Reading 400,000 rows to return 20 is the signature of a missing index.

Extra carries the warnings. Using filesort means MySQL sorts the result itself instead of reading rows in index order. Using temporary means it builds a temporary table. Using index is good news: the query was answered from the index alone.

The fix

A single-column index on customer_id helps the filter but still leaves the status check and the sort. A composite index covers all three:

Schema::table('orders', function (Blueprint $table) {
    $table->index(['customer_id', 'status', 'created_at']);
});

Column order matters. Equality filters go first (customer_id, status), then the column used for sorting or ranges (created_at). With this index MySQL jumps to the matching rows, reads them already sorted, and stops after 20.

Run EXPLAIN again. type should be ref, key should name the new index, rows should drop sharply, and Using filesort should be gone.

Finding the query in Laravel

To see which queries a request runs, log them in a local environment:

DB::listen(function ($query) {
    logger()->debug($query->sql, ['ms' => $query->time]);
});

Look for the slow ones and for the same query repeated many times, which points to an N+1 problem that eager loading with with() solves.

One caution

Indexes are not free. Each one slows down inserts and updates and takes disk space. Add them for queries you have measured, and remove ones that EXPLAIN shows are never chosen.

Related articles