Detailed explanation of PACK_KEYS in mysql

In the myisam engine of mysql, there is one that is easy to ignore, called compressed index PACK_KEYS.

myISAM uses prefix compression to reduce the index size. By default, strings will be compressed, and integers can also be compressed.

You can use PACK_KEYS to control the index compression method when creating table.

PACK_KEYS is described as follows in the MySQL manual:

If you want the index to be smaller, set this option to 1. Doing so usually makes updates slower and reading faster. Setting the option to 0 disables all keyword compression. When this option is set to DEFAULT, the storage engine only compresses long CHAR or VARCHAR columns (MyISAM only).

If you do not use PACK_KEYS, the default operation is to compress only strings, but not numbers. If you use PACK_KEYS=1, numbers are also compressed.

When compressing binary numeric keywords, MySQL uses prefix compression:

o                                                                               MySQL uses prefix compression: The next keyword is the same.

o                   Pointers to rows are stored after the keyword in high-endian first order to improve compression.

This means that if there are many identical keywords in two consecutive lines, the subsequent "identical" keywords usually only occupy two bytes (including the pointer to the row). In contrast, under normal circumstances, subsequent keys occupy storage_size_for_key + pointer_size (pointer size is usually 4). However, prefix compression is only beneficial if many numbers are identical. If all keywords are completely different, and keywords cannot contain NULL values, one more byte will be used for each keyword. (In this case, the byte that stores the length of the compressed keyword is the same byte used to mark whether the keyword is NULL.

For example, there is a field to save the file name, such as "abc.pdf", the pdf is the same for everyone, so you can

use compressed index according to this field:

CREATE  TABLE <TABLE_NAME> (
`id` INT NOT NULL ,
`name` VARCHAR(250) NULL ,
PRIMARY KEY (`id`) )
PACK_KEYS = 1;
ALTER TABLE table_name PACK_KEYS = 1;

The above is the detailed explanation of PACK_KEYS in mysql, more related content Please pay attention to PHP Chinese website (m.sbmmt.com)!