A common index of a database is typically a single field, and if multiple fields are combined, a composite index is formed. For a composite index, if
What happens when you make a conditional query on one of the fields?
First, examples
Mysql>Show CREATE TABLE Watchdog\g***************************1. Row ***************************table:watchdog Create table:create Table ' watchdog ' (' index1 'int( One) Not NULL DEFAULT'0', ' Index2 'int( One) Not NULL DEFAULT'0', ' dog 'int( One) Not NULL DEFAULT'9', PRIMARY KEY (' index1 ', ' index2 ')) ENGINE=innodb DEFAULT charset=UTF81Rowinch Set(0.00Sec
The above data table is created, and the index is a composite index (INDEX1, INDEX2) for both cases:
<1>selectfromwhere40000084200007
<2>selectfromwhere40000094200007 ;
Admittedly, most people may think that the two conditions are the same index, at least I think so.
Second, verification
The experimental results of the above two conditions
<1 > ... | 4200001 | 4200002 | | 4200002 | 4200003 | | 4200003 | 4200004 | | 4200004 | 4200005 | | 4200005 | 4200006 | | 4200006 | 4200007 |+---------+---------+199997 Rows in set ( 0.09 sec)
<2>... 4200002 4200003 4200003 4200004 4200004 4200005 4200005 4200006 |+---------+---------+199997inset (1.68 sec)
The performance consumption time is far apart in both cases. What are the reasons for this?
Iii. Results
To find out why, MySQL's replication index has three scenarios under where conditions:
<1> No composite index used
<2> use only the first index
<3> Use all Indexes
Therefore, for the second case of the above experiment to use the second index, does not work as an index.
Document:
http://stackoverflow.com/questions/26255039/mysql-column-order-in-composite-key/26255121
Http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html
The Note:explain command can parse the SQL statement, but you need to consider it in detail.
Mysql Composite Key Index performance