PACK_KEYS is the MySQL features used to generate packed index for MYISAM storage engines. PACK_KEYS is an optional option, used with the MySQL CREATE TABLE statement which indicates whether to put the keys.
PACK_KEYS is applicable to only on
MyISAM storage engine. PACK_KEYS means to packs the keys to reduce the size of the index file.With PACK_KEYS,Query performance is quite good. PACK_KEYS keep only the difference from the previous key so its decreases the duplicate keys.
You can set following values for PACK_KEYS:
- Default – is used to pack only CHAR, VARCHAR, BINARY, or VARBINARY columns.
- 0 – is used to disables all packing of keys
- 1 – is used for smaller indexes – slow down the updates and speed up the reading query.
Read about: Table Optimization in MySQL
For example, one field in your table to store PDFs filename, so you will store it name with the extension like ‘mypdf.pdf’ . So it’s waste of space to store the ‘.pdf’ for every node in MySQL. It is a common value which can be a duplicate in each and every row. So, PACK_KEYS will compress the common prefix so that it takes less space for values.
Let’s have a look into CREATE TABLE code with PACK_KEYS
CREATE TABLE <TABLE_NAME> (
`id` INT NOT NULL ,
`name` VARCHAR(250) NULL ,
PRIMARY KEY (`id`) )
PACK_KEYS = 1;
To apply PACK_KEYS on existing tables without doing a dump.
ALTER TABLE table_name PACK_KEYS = 1;
Must Read: MySQL CURSOR Explained
NOTE:If PACK_KEYS option is not used for table, the default value is set for that table and will be apply to only strings, not numbers. You can use PACK_KEYS = 1 to apply compression for string and numbers both.
That’s it.Share your thoughts if you any other simple way to get auto increment value from table.