MySQL Indexes – Multi-column indexes and order of columns

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.

  • Pingback: Tweets that mention MySQL Indexes – Multi-column indexes and order of columns | ovais.tariq -- Topsy.com()

  • Will “col_c = ‘some value’ and col_b = ‘some value’ and col_a = ‘some value’ ” use the index(col_a, col_b,col_c)?

  • Will “col_c = ‘some value’ and col_b = ‘some value’ and col_a = ‘some value’ ” use the index(col_a, col_b,col_c)?

  • The answer to your question is yes, because

    “col_c = ‘some value’ and col_b = ‘some value’ and col_a = ‘some value’ ” will be re-arranged by the mysql optimizer to become “col_a = ‘some value’ and col_b = ‘some value’ and col_c = ‘some value’ ”.

    Since both mean the same thing ( A and B and C = C and B and A)

  • The answer to your question is yes, because

    “col_c = ‘some value’ and col_b = ‘some value’ and col_a = ‘some value’ ” will be re-arranged by the mysql optimizer to become “col_a = ‘some value’ and col_b = ‘some value’ and col_c = ‘some value’ ”.

    Since both mean the same thing ( A and B and C = C and B and A)

  • Pingback: Tutorialslk.com()

  • Dor

    This article was enormously helpful to me.

  • Coach Factory Outlet,

    If you are a Coach fan like everybody, then
    chances are you know that the best place to find Coach Outlet
    Online
    Products at a super discount is at
    one’s own Coachoutlet365.org.
    Although there are some local shops that are discounting ever more so they can
    go inventory. Believe it! You can save a handsome profit at
    Coach
    Factory
    in amazing products. If you didn’t
    made it out to one of their Coach Outlet
    Online
    or discount retail stores, all you
    need to try and do is check online for Outletfactorycoach.us
    or outlet malls. You’ll soon discover a lot of specialty stores dedicated to
    supply discount Coach
    Outlet
    where they sell only genuine
    Discipline items and accessories.

    The Coach
    Usa
    internet is also a good resource for
    finding low priced Coach handbags, purses, trainers, and accessories. In simple
    fact, I personally prefer to continue online for great
    Coach Outlet Online
    buys rather than raiding the high-end stores around my neighborhood. As an
    icing on the cake, you’ll get your product shipped right to your doorstep
    instead of having to pay for gas expenses drive an automobile to the retail
    store orders
    here
    . Hope you have great memories shopping
    from my home
    within a great discount just when I have!

  • Guest

    Can you give a reference in the documentation (or some other article) that says this??

  • I cannot provide you a reference in MySQL manual that says this, because this is an understood principle in discrete maths. You might find something related though in this link http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html

  • Senthil Muthiah

    Hello , As you said, i have four column index colA, ColB, ColC, and colD. But in some places, my query use the four column also in where clause and some place, query uses only first three column. In this settings, query with first three column in where clause is very slow. I just removed 4th column from the index, then it works fine. WHY ?

  • That depends on how the query is written. If the query is using the columns in the same order in which they are defined in the index and not skipping one of the columns in between, then performance should be identical. Could you show me the query that is slow and the table structure?

  • This concept is important for speed but your first example shows no reason to have an index on product_id at all.

  • Also, it doesn’t matter which order the comparisons are made in the query. What matters is that the columns you’re comparing form the prefix of the index.

  • Maybe completely redoing this article would be a good plan.

  • One instance where the order of things in the query would be important would be in the ORDER BY clause. If you did

    SELECT `product_id`
    FROM `orders`
    ORDER BY `order_id`, `product_id`

    then you’d want to have an index on those two columns in that order.

  • It does actually matter if product_id is part of the index or not, since basically the other concept that they query shows is “covering index” which I didn’t necessarily cover here. The whole point is have the indexed column to be a prefix so for example just because you have a composite index such as (order_id, product_id) that doesn’t mean the queries filtering only on the product_id column are also going to be performant.
    I am pretty sure you didn’t read the highlighted part or you may as well want to read on “covering indexes”.

  • The post talks about the order of columns in the index. Having the columns as a prefix of the index actually means that the columns in the index are ordered in a certain way. Oh by the way the post has a nice heading “HOW DOES THE ORDER OF COLUMN IN THE INDEX MATTER”.

  • If I have an IO bound workload and I want to minimize key reads then I would index (order_id,product_id) for a query that filters by order_id and selects product_id column values. Because if I don’t do that and if order_id is a secondary index and product_id is not the primary index then InnoDB would fetch the primary index column value from the lead of the secondary index, lookup the primary index tree and fetch the value of the product_id column. If the query matches 1000 rows then that would mean 1000 additional key reads.
    This concept is known as “covering index”.

buy antibiotics online