TO LEARN MYSQL PACK_KEYS WITH EXAMPLE

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:

1. Default – is used to pack only CHAR, VARCHAR, BINARY, or VARBINARY columns.
2. 0 – is used to disables all packing of keys
3. 1 – is used for smaller indexes – slow down the updates and speed up the reading query.

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

PHP

1 2 3 4 5 6 7

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.

PHP

1 2 3

ALTER TABLE table_name PACK_KEYS = 1;

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.