Read the query plan before you add an index

"Just add an index" is the most common advice for a slow query, and it is often right. It is also frequently a guess. A wrong index costs disk, slows every write, and sometimes never gets used at all. Reading the query plan first takes two minutes and removes most of the guessing.

Start with one real slow query

Do not tune in the abstract. Find the query that is actually hurting: the slow query log, Laravel Telescope, Debugbar, or a DB::listen callback that logs anything over a threshold. Pick one, copy the exact SQL with real values, and work on that.

Run EXPLAIN and read four columns

Here is a typical list page: the newest open orders for one account.

EXPLAIN SELECT id, total, created_at FROM orders
WHERE account_id = 42 AND status = 'open'
ORDER BY created_at DESC LIMIT 25;

type   key                  rows     filtered  Extra
ref    orders_account_idx   180000   10.00     Using where; Using filesort

You can ignore most of the output at first. Look at these:

• key: the index MySQL chose. NULL means none.
• type: ALL is a full table scan. ref and range are fine; const and eq_ref are better.
• rows: how many rows MySQL expects to examine. This should be close to what you return, not a large fraction of the table.
• Extra: Using filesort and Using temporary are the usual suspects.

The plan above does use an index, but only on account_id. It reads about 180,000 rows for that account, throws most of them away on status, then sorts what is left to get the latest 25. The index is helping a little and the query is still slow.

Column order is the whole game

A composite index is read left to right. For a query like this one the rule of thumb is: equality columns first, then the column you sort or range on. Here that gives account_id, then status, then created_at.

Schema::table('orders', function (Blueprint $table) {
    // equality columns first, then the column you sort or range on
    $table->index(
        ['account_id', 'status', 'created_at'],
        'orders_account_status_created'
    );
});

With that index, MySQL jumps straight to one account's open orders, which are already ordered by date, and stops after 25.

type   key                            rows   filtered  Extra
ref    orders_account_status_created  4100   100.00    Backward index scan

Rows examined drops from a large slice of the table to roughly what you return, and the filesort is gone. The same index also serves queries that filter only on account_id, or on account_id and status, because of the left-to-right rule. It does not help a query that filters only on status.

Things that quietly stop an index being used

• A function wrapped around the column. WHERE DATE(created_at) = ... cannot use an index on created_at. Eloquent's whereDate() generates exactly that.
• A leading wildcard. LIKE '%smith' has no sorted starting point to seek to. LIKE 'smith%' does.
• Mismatched types. Comparing a string column to a number forces a conversion on every row.
• OR across different columns. Sometimes MySQL can merge indexes, often it cannot. A UNION of two simple queries can be faster.

// Wraps the column in DATE(), so MySQL cannot use an index on created_at
Order::whereDate('created_at', $day)->get();

// A plain range lets the index do its job
Order::where('created_at', '>=', $day->copy()->startOfDay())
    ->where('created_at', '<', $day->copy()->addDay()->startOfDay())
    ->get();

Indexes are not free

Every insert, update and delete has to maintain every index on the table. Adding five "just in case" indexes to a write-heavy table is a real cost. On MySQL 8 you can ask which ones have never been used:

-- Needs performance_schema. Counters reset on restart, so check after a long uptime.
SELECT object_name AS table_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database';

Treat the result as a lead, not a verdict. Counters reset when the server restarts, and a monthly report might be the only thing that uses an index.

Adding one to a big table

InnoDB builds secondary indexes online in current MySQL versions, so reads and writes can continue. I still run the migration off-peak, watch replication lag if there are replicas, and check the plan again afterwards. If the new plan does not look the way I expected, I want to know before the next deploy rather than after.

When the estimate looks wrong

From MySQL 8.0.18, EXPLAIN ANALYZE runs the query and reports actual row counts and timings next to the estimates. If the two differ wildly, the table statistics may be stale (ANALYZE TABLE refreshes them) or the data is skewed in a way the optimizer does not see, such as one account holding half the rows.

Cache last

Caching a slow query hides the problem and adds a second one: invalidation. Fix the query, measure again, and only then decide whether a cache still earns its place. Often it does not.