Debugging composite indexes in MySQL with EXPLAIN

The composite index in MySQL is an index on multiple columns. This kind of indexes may increase the performance of a query if it tests multiple columns in its WHERE or ORDER BY sections. Let’s see an example of such a query:

SELECT * FROM product WHERE price = 100 AND size = 'Medium'

As it reads in the query, in the WHERE clause we test two different columns — price and size. Normally MySQL can use only one index per single query, so if we create two separate indexes for price and size, it won’t help — only one of them can be used in an efficient way. For example, if we had the price and size indexes, EXPLAIN output for the query above would look like this (only part of the output is shown):

Or, even worse, MySQL is very likely to decide to perform the index_merge operation on these two indexes, which usually affects performance badly (it’s a very costly operation itself).

So it seems to be the right time to add a composite index to those two columns. It’s done in a very simple way:

CREATE INDEX price_size ON products (price, size)

All good! Now it’s time to check if our index is applied correctly. Let’s run EXPLAIN with the query from the previous example:

EXPLAIN SELECT * FROM product WHERE price = 100 AND size = 'Medium'

Here we can see that our index has been used successfully. Let’s pay special attention to the key_len field. It shows the index length in bytes used in the query. The price part of the index is 6 bytes (on a DECIMAL(10, 2) column), and the size is 153 bytes (on a VARCHAR(50) column), so it makes a sum of 159 we can see in the output.

Well, a condition like price = 100 doesn’t seem to be useful in real life. We’d rather filter our table with something like price > 100. Let’s EXPLAIN this new query:

EXPLAIN SELECT * FROM product WHERE price > 100 AND size = 'Medium'

Have you noticed a change? The key_len is just 6 now. But it was 159! It means that the index hasn’t been used fully. It’s because of the way how composite indexes are stored internally — we can roughly compare it to having the values from price and size concatenated to the same string. So for such query, MySQL can use the first part of the index (price), but can’t use the second part.

To solve this, we need to add an index with the same columns, but in a different order:

CREATE INDEX size_price ON products (size, price)

Is makes more sense, because we do the = comparison with the size column, so when the index starts with its value, MySQL then is also able to use the other part of this index to get the records where price > 100:

EXPLAIN SELECT * FROM product WHERE price > 100 AND size = 'Medium'

So, we conclude that the key_len field in MySQL EXPLAIN output helps us to check if a composite index has been used fully. Also, we see that if our queries use both = and < (or any other non-equal comparison), it would be better to create a composite index beginning with the column we test with = in the queries.