A brief discussion on the calculation method of key_len in mysql explain

The MySQL explain command can analyze the performance of SQL, one of which is the key_len (index length) statistic. This article will analyze the calculation method of key_len in MySQL explain.

1. Create test tables and data

CREATE TABLE `member` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 `age` tinyint(3) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `member` (`id`, `name`, `age`) VALUES (NULL, 'fdipzone', '18'), (NULL, 'jim', '19'), (NULL, 'tom', '19');

2. View explain

The name field type is varchar(20) , the character encoding is utf8 , and one character occupies 3 bytes, so key_len should be 20*3=60 .

mysql> explain select * from `member` where name='fdipzone';
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | member | ref | name | name | 63 | const | 1 | Using index condition |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

The key_len of explain is 63 , which is 3 more.

The name field allows NULL. Change name to NOT NULL and test again.

ALTER TABLE `member` CHANGE `name` `name` VARCHAR(20) NOT NULL;

mysql> explain select * from `member` where name='fdipzone';
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | member | ref | name | name | 62 | const | 1 | Using index condition |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

Now key_len is 62, which is 1 less than before, but still 2 more. It is certain that a NULL field will occupy one extra byte.

The name field type is varchar, which is a variable-length field. Change varchar to char and test again.

ALTER TABLE `member` CHANGE `name` `name` CHAR(20) NOT NULL;

mysql> explain select * from `member` where name='fdipzone';
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | member | ref | name | name | 60 | const | 1 | Using index condition |
+----+-------------+--------+------+---------------+------+---------+-------+------+-----------------------+

After changing to a fixed-length field, key_len is 60 , which is consistent with the prediction.

Summary: Using variable-length fields requires an additional 2 bytes, and using NULL requires an additional 1 byte. Therefore, for indexed fields, it is best to use fixed-length and NOT NULL definitions to improve performance.

The above brief discussion on the calculation method of key_len in MySQL explain is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:

The meaning and calculation method of key_len in mysql explain

<<: Example of how to configure multiple virtual hosts in nginx

>>: Detailed explanation of js closure and garbage collection mechanism examples

Mount the disk in a directory under Ubuntu 18.04

Detailed explanation of several practical solutions for quickly deleting large amounts of data (tens of millions) in MySQL

The author recently encountered a performance bot...

A brief discussion on the calculation method of key_len in mysql explain

Mount the disk in a directory under Ubuntu 18.04

A brief discussion on the underlying principle of mysql join

Automatic backup of MySQL database using shell script

A few steps to easily build a Windows SSH server

MySQL 5.7.19 installation tutorial under Windows 10 How to change the root password of MySQL after forgetting it

How to view nginx configuration file path and resource file path

B2C website user experience detail design reference

Example of how to check the capacity of MySQL database table

Detailed explanation of Vue custom instructions

HTML table tag tutorial (33): cell vertical alignment attribute VALIGN

Recommend

Detailed explanation of setting up DNS server in Linux

Nginx dynamically forwards to upstream according to the path in the URL

Difference between HTML4 and HTML5: How to add focus implementation code to an input

Parsing MySQL binlog

Detailed tutorial on MySQL installation and configuration

Detailed explanation of several practical solutions for quickly deleting large amounts of data (tens of millions) in MySQL

Web form creation skills

Use MySQL master-slave configuration to achieve read-write separation and reduce database pressure

960 Grid System Basic Principles and Usage

VMware Workstation 15 Pro Installation Guide (for Beginners)

What we can learn from Google's new UI (pictures and text)

Some indicators of excellent web front-end design

Usage of if judgment in HTML

Sample code for automatic web page refresh and automatic jump

Vue3.0 handwriting magnifying glass effect