Shulou(Shulou.com)06/01 Report--

Experimental environment: MySQL 5.7.17

1. The table structure is as follows. Create a primary key in the id,name,tx column.

Mysql > show create table txtx +- -+ | Table | Create Table | | +-+- - -- + | txtx | CREATE TABLE `txtx` (`id` int (11) NOT NULL `name` char (2) NOT NULL, `tx` char (3) NOT NULL, `id1` int (11) DEFAULT NULL, PRIMARY KEY (`id`, `name`) `tx`) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-+- -+ 1 row in set (0.00 sec)

2. View the 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 above execution plan, 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 part of the defined index.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.