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
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.
1 2 3 | 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.
If you have any query or question,tell us in the comment section :). Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.
