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.

  • http://topsy.com/trackback?utm_source=pingback&utm_campaign=L2&url=http://ovaistariq.blog.com/2010/05/26/mysql-indexes-multi-column-indexes-and-order-of-columns/ Tweets that mention MySQL Indexes – Multi-column indexes and order of columns | ovais.tariq — Topsy.com

    [...] This post was mentioned on Twitter by Ovais Tariq, Ovais Tariq. Ovais Tariq said: MySQL Indexes – Multi-column indexes and order of columns – http://bit.ly/baPp3T [...]

  • http://www.masalife.com huarong

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

  • http://www.masalife.com huarong

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

  • http://www.ovaistariq.net/ ovais.tariq

    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)

  • http://www.ovaistariq.net/ ovais.tariq

    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)

  • http://www.tutorialslk.com/2010/10/10/sqlite-expert-professional-3-1-21/ Tutorialslk.com

    SQLite Expert Professional 3.1.21…

    I found your entry interesting thus I’ve added a Trackback to it on my weblog :)

  • 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??

  • http://www.ovaistariq.net/ Ovais Tariq

    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

blog comments powered by Disqus