MySQL column order in composite key
I have doubts. 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 request:
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 times they used are 0.9s and 1.68s! Can anyone tell me why? What happens to composite key order?
source to share
MySQL has good documentation on compound indexes that you should look at. Let me summarize the question for your request.
The relative part of the query is the sentence where
:
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:
- Decide not to use the index at all (based on statistics or not applicable to the query)
- Decide to use a component
index1
. - Decide to use component
index1
andindex2
.
In the first query, MySQL may choose the second option. Thus, it uses the index for comparison index1
. Then it appears to index2
go through the corresponding rows, loop through the value in the index, find the row ids, loop through them, and return the rows.
For the second sentence, where
he cannot use an index. The first index key index1
, and it is not in the query. So MySQL has to do a full table scan.
source to share