MySQL Indexes – Multi-column indexes and order of columns

Ovais Tariq -

The problem:

Many a times people find that they create index but the query is still slow or the index is not being used by MySQL to fetch the result-set.

Mostly the reason is that the index is not created properly, either not the right columns being indexed or the order of columns in the index does not match how its being used in the query.

The order of index! What’s that. Well that’s what we will be discussing today.

How does the order of column in the index matter?

The order of columns in the index matters a lot, an index won’t be used my MySQL if the order of columns in index don’t match with the order in which they are used in the query.

Consider the following example:

Suppose there is an orders table consisting of order_id and product_id columns and you want to select the product_id for specific order_id(s).

The query is as follows:

select product_id 
from orders 
where order_id in (123, 312, 223, 132, 224);

Now have a quick look at what the query is asking mysql to do, its asking MySQL to search by order_id(s) and return the corresponding product_id(s) so the order of columns in the index should be order_id, product_id

create index orderid_productid on orders(order_id, product_id)

Reasoning.

The reason for doing so is that MySQL will only use a multi-column index if at least a value is specified for the first column in the index. If the index in the above example would have been product_id, order_id then estentially the query would have been asking MySQL to search for * 123, * 312, * 223, * 132, * 224 and so on. And suppose there are a million records in the orders table then would could be worst then MySQL looking through each record one by one and we would be lucky if it would come accross a matching record.

Well we could have rewritten the above query to only have order_id as the index, sure that would do the job. But then still MySQL would have to fetch the value of product_id from the disk. While in the case of our composite index (order_id, product_id) MySQL would fetch all the results from the index.

Another important point!

One more thing to remember here is that the multi-column index would also work if the columns used in the query form a prefix of the index.

For example suppose we have an index composite(col_a, col_b, col_c) this index would be used by MySQL for all queries that involve any of the following comparisons:

  • col_a = ‘some value’
  • col_a = ‘some value’ and col_b = ‘some value’
  • col_a = ‘some value’ and col_b = ‘some value’ and col_c = ‘some value’

see the order being followed.

The same index would not have been used by MySQL for any of the following comparisons:

  • col_b = ‘some value’
  • col_b = ‘some value’ and col_c = ‘some value’

because the columns used in these comparisons don’t form a prefix of the index.