Skip to content
Daryl Legion

Why MySQL Ignores Your Composite Indexes

September 9, 2026 • 4 min read

If your app has an orders table, it probably runs queries like these two. The admin queue of pending orders:

Order::query()->where('status', 'pending')->get();

And a customer looking at their own pending orders:

Order::query()->where('status', 'pending')->where('user_id', $userId)->get();

Both filter on status and user_id, so you add a composite index. user_id has 50,000 distinct values and status has five, and the common advice is to put the more selective column first:

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

The customer query gets faster. The admin query is exactly as slow as before, and EXPLAIN still says type: ALL. The index is there, but MySQL isn’t using it for that query, and the usual cause is column order.

Let’s look at what EXPLAIN shows for both queries, and how to choose the order.

Reading the EXPLAIN output

Here’s the EXPLAIN output for a table of 100,000 orders, where 80% are completed and only 2% are pending.

The customer query uses the index and narrows straight down to 2 rows:

EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND user_id = 148;
+------+-----------------------------+-----------------------------+------+
| type | possible_keys               | key                         | rows |
+------+-----------------------------+-----------------------------+------+
| ref  | orders_user_id_status_index | orders_user_id_status_index | 2    |
+------+-----------------------------+-----------------------------+------+

The admin query doesn’t use it at all:

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
+------+---------------+------+--------+-------------+
| type | possible_keys | key  | rows   | Extra       |
+------+---------------+------+--------+-------------+
| ALL  | NULL          | NULL | 100100 | Using where |
+------+---------------+------+--------+-------------+

possible_keys is NULL, which means MySQL never considered the index for this query. It scans the whole table to find 2,000 pending orders.

How a composite index is sorted

A composite index is sorted by its first column, and then by the second column within each value of the first. It works like a phone book sorted by last name, then first name.

So a query can only use the index from its first column onward. An index on (user_id, status) can serve:

  • WHERE user_id = ?
  • WHERE user_id = ? AND status = ?

A query on status alone would have to start somewhere in the middle of that sort order, and there’s nowhere to start. It’s like asking the phone book for everyone named James: you’d have to read every page.

So flip the columns:

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

Same two columns, same data. Now the admin query uses it:

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
+------+-----------------------------+-----------------------------+------+
| type | possible_keys               | key                         | rows |
+------+-----------------------------+-----------------------------+------+
| ref  | orders_status_user_id_index | orders_status_user_id_index | 2000 |
+------+-----------------------------+-----------------------------+------+

The row estimate drops from 100,100 to 2,000, exactly the number of pending orders. The customer query still uses the index in full, because it filters on both of its columns:

EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND user_id = 148;
+------+-----------------------------+-----------------------------+------+
| type | possible_keys               | key                         | rows |
+------+-----------------------------+-----------------------------+------+
| ref  | orders_status_user_id_index | orders_status_user_id_index | 2    |
+------+-----------------------------+-----------------------------+------+

One index now serves both queries.

dropIndex() with an array works out Laravel’s conventional name, here orders_user_id_status_index. If yours was created with an explicit name, pass that string instead.

Choosing the column order

Look at the two EXPLAIN results for the customer query again. With (user_id, status) and with (status, user_id), MySQL narrows it to the same 2 rows. Putting the more selective column first didn’t make that lookup any faster.

Column order decides which queries can use the index at all. So write down the queries the index is meant to serve, and put first the column that appears in most of them. Here that’s status, which is in both. user_id is only in one.

One rule comes before that count: the first column should be one you match with =. The index stops narrowing at the first range condition, like >, BETWEEN or LIKE 'x%', so the columns after it don’t help filter.

What about skip scan?

MySQL 8.0 added skip scan, which can use (status, user_id) for a query on user_id alone by checking each distinct status value in turn. It only works when the index covers the query, meaning every column the query needs is in the index.

SELECT * needs columns the index doesn’t have, so this still scans the table:

EXPLAIN SELECT * FROM orders WHERE user_id = 148;
+------+---------------+------+--------+-------------+
| type | possible_keys | key  | rows   | Extra       |
+------+---------------+------+--------+-------------+
| ALL  | NULL          | NULL | 100100 | Using where |
+------+---------------+------+--------+-------------+

Ask only for columns in the index, and skip scan kicks in:

EXPLAIN SELECT user_id FROM orders WHERE user_id = 148;
+-------+-----------------------------+-----------------------------+-------+----------------------------------------+
| type  | possible_keys               | key                         | rows  | Extra                                  |
+-------+-----------------------------+-----------------------------+-------+----------------------------------------+
| range | orders_status_user_id_index | orders_status_user_id_index | 10002 | Using where; Using index for skip scan |
+-------+-----------------------------+-----------------------------+-------+----------------------------------------+

It’s worth knowing about, but don’t count on it when you choose the column order.

Checking with EXPLAIN

Whatever order you choose, check it. Run EXPLAIN on the query and read the key column: NULL means MySQL didn’t use any of your indexes. In Laravel, ->toRawSql() gives you the finished statement to paste in.

So before you add a composite index, list the queries it should serve. If its first column isn’t in all of them, some of those queries won’t be able to use it.