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.
Packed indexes can be created while creating a table. PACK_KEYS 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:
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.
Here are few points with which I come up after few reading about this topic.
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.
Avinash Zala currently working at a leading web development company as a Sr. PHP Web Developer. He is the founder of the Xpert Developer. He has completed B.E. in Information Technology. Born and brought up in Ahmedabad. He loves to code and coding is all about passion for him. Follow him on twitter at Avinash Zala & Xpert Developer.
View all posts by: Avinash