CODEWITHSUNDEEP
mysqlsqlindexing
neubert
neubert

Reputation: 16792

finding the optimal order of multi-column index

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?

Upvotes: 3

Views: 1952

Answers (2)

Namphibian
Namphibian

Reputation: 12221

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.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562348

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.

Upvotes: 14

Related Questions