Why MySQL Ignores Your Composite Indexes
September 9, 2026 • 5 min read
You add an index, deploy it, and the query is exactly as slow as before. EXPLAIN still says type: ALL. The index is there - MySQL just isn’t using it.
Nine times out of ten this is column order. A composite index can only be read from its first column onward - so if it doesn’t start with the column you’re filtering on, it’s an index MySQL can’t use.
Everything below is real output from MySQL 8.4 against an orders table of 100,000 rows: 50,000 distinct user_id values, and a status column where 80% are completed and only 2% are pending.
The setup
Two queries the app actually runs. The admin queue:
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();Now we index it. user_id has 50,000 distinct values and status has five, so the more selective column goes first:
Schema::table('orders', function (Blueprint $table) {
$table->index(['user_id', 'status']);
});The customer query gets what you’d expect - an index lookup 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 move 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. MySQL didn’t reject the index as too expensive - it never considered it. And it’s scanning the whole table to find 2,000 rows.
From left to right, without skipping
A composite index is sorted by its first column, then ties are broken by the second, then the third. That’s it. It’s a phone book sorted by last name, then first name.
Which means a query can only use a prefix of the columns. An index on (user_id, status) can serve:
WHERE user_id = ?WHERE user_id = ? AND status = ?
Anything that doesn’t start with user_id gets no help from it. A query filtering on status alone would have to start somewhere in the middle of the sort order, and there’s no middle to start from. Same as asking a phone book for everyone named James - you’re reading the whole thing either way.
So flip it:
Schema::table('orders', function (Blueprint $table) {
$table->dropIndex(['user_id', 'status']);
$table->index(['status', 'user_id']);
});Same two columns, same table, 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. And the customer query is unchanged - status, user_id is a prefix it can use in full:
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, both queries.
dropIndex() with an array infers Laravel’s conventional name - here orders_user_id_status_index. If yours was created with an explicit name, pass that string instead.
What column order actually decides
It’s worth noticing what putting the selective column first bought us: nothing. Compare the two customer-query plans above - (user_id, status) and (status, user_id) both resolve it to the same 2 rows. Ordering by selectivity didn’t make the lookup faster, because the lookup was never the problem.
What it decides is which queries can use the index at all. That’s a question about your app, not about your data distribution.
So the rule is: write down the queries the index is meant to serve, and put the column that appears in the most of them first. Here that’s status - it’s in both. user_id is in one.
If two columns appear in the same number of queries, put the one you filter with = ahead of the one you filter with a range (>, BETWEEN, LIKE 'x%') or sort by. An index stops narrowing at the first range condition, so that column belongs last.
One caveat: skip scan
MySQL 8.0 added skip scan, which can use (status, user_id) for a query on user_id alone by looping over each distinct status value behind the scenes. It’s real, but it’s narrower than people expect - the query has to be covered by the index.
Our query isn’t, because SELECT * needs columns the index doesn’t have:
EXPLAIN SELECT * FROM orders WHERE user_id = 148;
-- type: ALL, key: NULL, rows: 100100Ask only for columns that are in the index, and it kicks in:
EXPLAIN SELECT user_id FROM orders WHERE user_id = 148;
-- type: range, key: orders_status_user_id_index
-- Extra: Using where; Using index for skip scanUseful to know it exists. Not something to design around.
In practice
Whatever you land on, check it: EXPLAIN the query and read the key column. NULL means none of your indexes were used. If you’re already in Laravel, ->toRawSql() gets you the statement to paste in.
That’s the whole thing. Before you add a composite index, write down the queries it’s meant to serve - if the first column isn’t in all of them, the order is wrong.