Create Packed Indexes with MySQL

I am pretty much sure that you are aware of indexes which we are creating on database fields. Indexes are very useful to make your query search faster and improve the site performance.

But the fact is that Indexes which we are creating on database tables also require some additional space on your hard disk and if you have not properly created these indexes than some times your index size become more than your actual data size.

Now what if you can compress indexes which you have created on your tables? Sounds interesting????

These compressed indexes are applicable to MyISAM tables only. MyISAM storage engine has key compression which makes its indexes much smaller, so improving performance dramatically. MyISAM tables will perform well with these packed indexes.

These compressed indexes are applicable to MyISAM tables only.

How to create packed index?

Packed indexes can be created while creating a table. [code]PACK_KEYS[/code] table option is used to created a compressed indexes. This option can have following three values: 1, 0 and DEFAULT.

Have a look at below create table code for more details:

[cc lang=”mysql”]
CREATE TABLE `test_database`.`new_table` (
`id` INT NOT NULL ,
`address` VARCHAR(45) NULL ,
PRIMARY KEY (`id`) ,
INDEX `address` (`address` ASC) )
PACK_KEYS = 1;
[/cc]

But these packed indexes will make you read faster and updates will become slower :)

Set this value to 1 to pack indexes of all kind of columns. Setting this option to 0 will disable all kind of indexes compression. When you set it to DEFAULT then it will pack only long CHAR, VARCHAR, BINARY, or VARBINARY columns.

If you do not use PACK_KEYS, the default is to pack strings, but not numbers. If you use PACK_KEYS=1, numbers are packed as well.

These packed indexes will make you read faster and updates will become slower.

Conclusion

Here are few points with which I come up after few reading about this topic.

1. PACK_KEYS may give great space savings.
2. PACK_KEYS applies to MyISAM tables only.
3. PACK_KEYS=1 can slow down your integer joins a lot
4. PACK_KEYS=0 as it can blow up your index size.

Guys share your thoughts on this performance optimization tip for the MySQL. It would be great if you have used this earlier and would like to share your some experience with this.

Subscribe to get latest updated right into your inbox, Follow us on twitter and Like us on facebook.