How to realize the leftmost principle of index in mysql

In this issue, the editor will bring you about how to achieve the leftmost principle of the index in mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Build a table

CREATE TABLE `user` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (10) DEFAULT NULL, `sex` tinyint (1) DEFAULT NULL, `age` tinyint (2) DEFAULT NULL, PRIMARY KEY (`id`), KEY `Index_ user` (`name`, `age`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

Test sql

First kind

Mysql > explain SELECT * FROM `user` where name= "tom"\ Gmail * 1. Row * * id: 1 select_type: SIMPLE table: user partitions: NULL type: refpossible_keys: Index_user key: Index_user key_len: 43 ref: const rows: 1 filtered: 100.00 Extra: NULL

The second kind

Mysql > explain SELECT * FROM `user` where age=18 and name= "tom"\ Gmail * 1. Row * * id: 1 select_type: SIMPLE table: user partitions: NULL type: refpossible_keys: Index_user key: Index_user key_len: 45 ref: const,const rows: 1 filtered: 100.00 Extra: NULL

The third kind

Mysql > explain SELECT * FROM `user`where age=18\ Gmail * 1. Row * * id: 1 select_type: SIMPLE table: user partitions: NULL type: ALLpossible_keys: NULL key_len: NULL ref: NULL rows: 3 filtered: 33.33 Extra: Using where1 row in set, 1 warning (0.00 sec)

The fourth kind

Mysql > explain SELECT * FROM `user` where name= "tom" and age=18\ Gmail * 1. Row * * id: 1 select_type: SIMPLE table: user partitions: NULL type: refpossible_keys: Index_user key: Index_user key_len: 45 ref: const,const rows: 1 filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec) the above is how to implement the leftmost index principle in the mysql shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.