Posted on 4 Comments

MySQL 5.0 index merge – using multiple indexes

Did you know that… MySQL 5.0 is able to use multiple indexes for a single table?
The simplest example of where this comes in useful is ... WHERE a=10 OR b=20

This join type optimization is new in MySQL 5.0, and represents a significant change in behavior with regard to indexes, because the old rule was that the server is only ever able to use at most one index for each referenced table.

The new optimization can use intersection, union or sort-union algorithms. It’s all in the manual, see http://dev.mysql.com/doc/mysql/en/index-merge-optimization.html

Posted on 4 Comments

4 thoughts on “MySQL 5.0 index merge – using multiple indexes

  1. Goog to know, especially as until now at conferences MySQL guys have always preached that “MySQL is only ever going to use one index per query per table” 🙂

  2. Is there a maximum number that you can do at one time?

    Also with 4.x if you have something like this:
    select a,b from c where a=’bla’;

    And you only a single index on a and an index on b. The query above when you run an explain with only have “Using where”. But if you change the query to this:

    select a from c where a=’bla’;

    You get a “Using where; Using index”. Now of course I could delete the original a index and create an a+b index and then the problem would go away, I know that one but everybody doesn’t know that one.

    So what will happen when I only have an index on a and one on b in 5.0?

    Donny

  3. Because of the way and place where it is implemented, I don’t think there’s a limit.

    “Using WHERE” vs “Using WHERE; Using index”. This is not a problem, it is merely an indication of what’s going on. In the second case, because all selected columns are part of the index, the server does not need to look at the row at all since it can retrieve all values from the index. It’s just an extra optimization.
    Having an index on (a,b) may make sense in some cases, but not always.

    This has no bearing on the 5.0 index merge system though… the index(es) are used for lookups. It makes no sense to use a separate index on b just to get its value, it’s an extra lookup. Might as well go for the row data, which is a more generic case since many queries will select more columns anyway. I don’t think there would be a benefit to optimizing this niche situation… it’s an extra lookup somewhere vs an extra lookup elsewhere.

  4. I need to update myself… it’s actually downright impossible to use a separate index on b just to get the value! The reason being that you’d need to know the value to begin with, otherwise you can’t do the lookup using (b). You’d need to go to the row data based on what you find in a, there’s no other choice.

Comments are closed.