MySQL can only use the leftmost part of the definition index

Lab Environment: MySQL 5.7.17

1. Table structure as follows, establish primary key in ID,NAME,TX column

mysql> show create table txtx;+-------+------------------------------------------------ --------------------------------------------------------------------------------------------------------------- ----------------------------------------+| table | create table                                                                                                                                                                                                    |+-------+---- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+| txtx  |  CREATE TABLE  ' Txtx '   (   ' id '  int (one)  NOT NULL,   ' name '  char (2)  NOT NULL,   ' TX '  char (3)  NOT NULL,   ' Id1 '  int ( One)  DEFAULT NULL,  PRIMARY KEY  (' id ', ' name ', ' TX ')  ENGINE=InnoDB  Default charset=gbk |+-------+--------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------+1  row in set  (0.00 SEC)

2. View Execution Plan

Mysql> explain select * from txtx where id=1 and id1 =1  and tx= ' TX ', +----+-------------+-------+------------+------+---------------+---------+---------+--- ----+------+----------+-------------+| id | select_type | table |  Partitions | type | possible_keys | key     | key_ len | ref   | rows | filtered | extra        |+----+-------------+-------+------------+------+---------------+---------+---------+- ------+------+----------+-------------+|  1 | simple      |  txtx  | NULL       | ref  |  primary       | primary | 4        | const |     1 |    33.33 | using where |+----+------------ -+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+1  row in set, 1 warning  (0.00 SEC)

As you can see from the execution plan above, although the query uses the ID and TX columns in the WHERE statement, only the ID column is used because MySQL can only use the leftmost portion of the definition index.

This article is from the "Corasql" blog, make sure to keep this source http://corasql.blog.51cto.com/5908329/1913142

MySQL can only use the leftmost part of the definition index