MySQL column order in composite key

Question

I have a doubt, Here is my table:

mysql> show create table watchdog\G
*************************** 1. row ***************************
   Table: watchdog
   Create Table: CREATE TABLE `watchdog` (
       `index1` int(11) NOT NULL DEFAULT '0',
       `index2` int(11) NOT NULL DEFAULT '0',
       `dog` int(11) NOT NULL DEFAULT '9',
        PRIMARY KEY (`index1`,`index2`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    1 row in set (0.00 sec)

<1> First query:

select index1, index2 
from watchdog 
where index1 > 4000008 and index1 < 4200007; 

Result:

 ...
| 4200001 | 4200002 |
| 4200002 | 4200003 |
| 4200003 | 4200004 |
| 4200004 | 4200005 |
| 4200005 | 4200006 |
| 4200006 | 4200007 |
+---------+---------+

199997 rows in set (0.09 sec)

<2> Second query:

select index1, index2 
from watchdog 
where index2 > 4000009 and index2 < 4200007;

Result:

...
| 4200002 | 4200003 |
| 4200003 | 4200004 |
| 4200004 | 4200005 |
| 4200005 | 4200006 |
+---------+---------+

199997 rows in set (1.68 sec)

The time they used are 0.9sec and 1.68sec! Could anyone tell me why? What is the matter with the composite key order?

Answer 1

MySQL has good documentation on composite indexes, which you should review. Let me summarize the issue for you query.

The pertinent part of the query is the where clause:

where index1 > 4000008 and index1 < 4200007;
index2 > 4000009 and index2 < 4200007;

You have an index on index1, index2, in that order. In general, MySQL can look at a query and do one of three things with the index:

1. Decide not to use the index at all (based on statistics or inapplicability to the query)
2. Decide to use index1 component.
3. Decide to use index1 component and index2.

In the first query, MySQL can choose the second option. So, it uses the index for the index1 comparison. It then presumably scans the appropriate rows, looking at index2 value in the index, finds the row ids, looks them up, and returns the rows.

For the second where clause, it cannot use the index. The index's first key is index1, and it is not present in the query. So, MySQL has to do a full table scan.