3

So let's say I have a table with two indexes - one on column a and one on columns a, b and c.

I've noticed that, depending on the order of the columns in the index definition that MySQL might wind up using the single column index instead of the multi column index, even if all three columns in the multi column index are being referenced in the ON part of the JOIN.

This kinda begs the question... how does one figure out the ideal ordering of the columns? Do you just have to brute force it?

Barmar
  • 669,327
  • 51
  • 454
  • 560
neubert
  • 14,976
  • 22
  • 102
  • 184
  • Can you show an example query where that happens? Ordering should only matter if you use a subset of the columns in an index: the columns have to be a prefix of the index. – Barmar Oct 17 '13 at 22:01
  • Not that easily. It's my employers database and I don't want to post the schema to a non-public database let alone provide data with which queries can be made. I've tried to make sandboxed examples on sqlfiddle.com (whose uptime has been spotty as of late) without success :( – neubert Oct 17 '13 at 22:06
  • .. It kinda begs the question - Why post a vague question on a forum? – Ed Heal Oct 17 '13 at 22:10
  • What constitutes vague? If I say "is it possible to find all files that contain a particular code snippet? im using linux" one correct answer would be `grep -r whatever .`. I suppose in that question one could see the fact that they didn't say which editor or IDE they're using as making the question vague but that doesn't mean there's not a clear answer. – neubert Oct 17 '13 at 22:22
  • @neubert - No schema, No query to work on. guess that makes it vague and therefore impossible to give advice – Ed Heal Oct 17 '13 at 22:37

2 Answers2

14

The order of columns in an index is not irrelevant. There is a method.

  • First, one or more columns that are involved in equality terms combined with AND.

    WHERE a = 1 AND b = 2

  • Second, a single column involved in a range term. Either that, or else one or more columns involved in sorting.

    WHERE a = 1 AND b = 2 AND c > 3

    or

    WHERE a = 1 AND b = 2
ORDER BY c, d

  • Third, columns referenced in the select-list, but which aren't referenced by searching or sorting.

    SELECT x, y, z
. . .
WHERE a = 1 AND b = 2 AND c > 3

This would result in an index on (a, b, c, x, y, z).

I explain this in more detail in my presentation How to Design Indexes, Really.

Bill Karwin
  • 499,602
  • 82
  • 638
  • 795
  • Interesting method Bill. Makes a lot of sense. Essentially including the `x,y,z` columns in the index you are simulating included columns found in MS SQL server 2005+ indexes. That never occurred to me. Absolutely brill actually. This one is going into my bag of tricks. – Namphibian Oct 18 '13 at 03:34
  • 1
    @Namphibian, yes, it's sometimes called a *covering index* and if the index is in the buffer, it's a huge performance win. – Bill Karwin Oct 18 '13 at 05:10
  • Yeah it would be. No need to read the values from the table just read straight out of the index. Very cool indeed. Just never thought about it like that. – Namphibian Oct 18 '13 at 05:16
1

How and when MySQL uses a index is based on the statistics on the table. Using the statistics it might decide on one or the other. You can force it to use a particular index but that is generally not a good idea. See this link for more information.

Here is the thing to remember as the table grows and changes the statistics change. Thus the query might run with one index today another tomorrow. MySQL will determine which is the better one to use. This is why forcing a index is just shooting yourself in the foot at some point.

You cant create a perfect index forever so what you need to do is create a index that will help speed up the query when it becomes slow. So switch on the slow log and monitor that. When a query starts running slow do a EXPLAIN and then figure out how to optimize. Oh and at a point you might have to remove indexes as they might not get used anymore.

Ask if you need more information.

Namphibian
  • 11,705
  • 7
  • 45
  • 70