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

How do I look at the key_len in explain to determine which index is used? Many people don't know much about it. Today, in order to make you understand better, the editor summarizes the following contents. Let's look down together. I'm sure you'll get something.

There are multiple indexes in a table, and the condition in our where field has multiple index values, so which one is used?

We can use explain to view it, and the key_len field can be seen.

For example, the following sql

Explain select * from ent_calendar_diary where email='xxxx' and diary_id=1784 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+-+ | 1 | SIMPLE | ent_calendar_diary | NULL | const | PRIMARY Idx_email_stime | PRIMARY | 4 | const | 1 | 100.00 | NULL | +-+-- -+

There are two index fields in possible_keys, but look at key_len is 4 bytes

Note: key_len only indicates the index column selected for conditional filtering in WHERE, and does not contain ORDER BY/GROUP BY

Int type and not null is 4 bytes, so the sql above is the primary key index used

Explain select * from ent_calendar_diary where email='xxxx' +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | ent_calendar_diary | NULL | ref | idx_email_stime | idx_email_stime | 767 | Const | 100.00 | NULL | +-+ -+

This is 767 bytes, and varchar (255) not null 255 * 3 + 2 matches exactly, so it is the normal index of email used.

CREATE TABLE `ent_calendar_ Secrety` (`diary_ id` int (10) unsigned NOT NULL AUTO_INCREMENT, `email` varchar (25551) NOT NULL, `title`varchar (100000) NOT NULL, `stime`varchar (500mm) NOT NULL DEFAULT'', `stime`bigint (11) NOT NULL DEFAULT '0mm, `ctime`int (10) unsigned NOT NULL DEFAULT' 0mm (`diary_ id`), KEY `idx_email_ stime` (`email`, `stime`) ENGINE=InnoDB AUTO_INCREMENT=1809 DEFAULT CHARSET=utf8

On how to check the key_len in explain to determine which index is used to share here, of course, not only the above and everyone's analysis methods, but the editor can ensure its accuracy is absolutely no problem. I hope that the above content can have a certain reference value for everyone, and can be put into practice. If you like this article, you might as well share it for more people to see.

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.