MySQL not being able to utilize a compound index?

I came today upon a very strange issue. It seems like MySQL is unable to utilize a compound index when evaluating a plan for a query with a range condition. I’m looking for an explanation. I’ll appreciate any insight on this.

Take a look at the following table:

CREATE TABLE `t` (
  `a` int(11) NOT NULL default '0',
  `b` int(11) NOT NULL default '0',
  `c` int(11) default NULL,
  PRIMARY KEY  (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Filled with this data:

mysql> SELECT * FROM t;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 | NULL |
| 1 | 2 | NULL |
| 1 | 3 | NULL |
| 1 | 4 | NULL |
| 1 | 5 | NULL |
| 2 | 1 | NULL |
| 2 | 2 | NULL |
| 2 | 3 | NULL |
| 2 | 4 | NULL |
| 2 | 5 | NULL |
+---+---+------+
10 rows in set (0.00 sec)

Now, it is known that I can query by tuples:

mysql> SELECT * FROM t WHERE (a,b) < (2,2);
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 | NULL |
| 1 | 2 | NULL |
| 1 | 3 | NULL |
| 1 | 4 | NULL |
| 1 | 5 | NULL |
| 2 | 1 | NULL |
+---+---+------+
6 rows in set (0.00 sec)

MySQL understands tuple comparison (e.g. (a,b) < (2.2)) and returns correct results. Now here’s my issue: I would assume the PRIMARY KEY is used – since it’s on (a,b) – so that’s a simple (well, compound) range condition. Alas:

mysql> EXPLAIN SELECT * FROM t WHERE (a,b) < (2,2)G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
1 row in set (0.00 sec)

We get a full table scan! Now, MySQL has no problem when I do an equality search (e.g. (a,b) = (2,2)). In that case, the PRIMARY KEY is utilized.

Why, then, would it not use it in the range query? Why would it not consider it as a possible key (I could live with FORCE KEY if that would solve the problem). This problem undermines some efforts of mine for nice optimization tricks.

[UPDATE: the above is a simplified version of a very large table I was using (~50M rows, ~30GB), and on which same results were achieved]

Any ideas or suggestions?

25 thoughts on “MySQL not being able to utilize a compound index?

  1. Hi Justin,

    the syntax (a,b) < (x,y)

    means:

    CASE
    WHEN a > x THEN FALSE
    WHEN a = x THEN
    CASE
    WHEN b < y THEN TRUE
    ELSE FALSE
    END
    END

  2. darn…something is not well with literal <

    me tries again….

    (a,b) < (x,y) is equivalent to:

    CASE
    WHEN a > x THEN FALSE
    WHEN a < x THEN TRUE
    ELSE CASE
    WHEN b < y THEN TRUE
    ELSE FALSE
    END
    END

  3. @Matthew,

    This doesn’t really change much – you just get a full index scan instead of a full table scan. If I need to get an entire row, I get no advantage. Thanks!

  4. @Harrison, @Dean

    I don’t think this query can be categorized as “subquery” in any way, or else the optimizer is very wrong.
    Do you find that there’s a “sub” here?

  5. 1) Have you tried it on 5.1 version of MySQL?
    2) Do you get the same explain plan with and without “limit” ?
    3) What is the actual time that it took to return the results?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.