Today's Question:  What are you most afraid of as a programmer?        GIVE A SHOUT

Technical Article => Database =>  MySQL

Create Packed Indexes with MySQL

  Avinash      2012-04-24 07:42:18      2,143    0    0

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. 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:

Copy
  CREATE  TABLE `test_database`.`new_table` (
  `id` INT NOT NULL ,
  `address` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `address` (`address` ASC) )
  PACK_KEYS = 1;

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.

Source : http://www.xpertdeveloper.com/2012/04/mysql-packed-indexes/

MYSQL COMPRESSED INDEX PACK_KEYS

  SAVE AS PDF   MARK AS READ   MARK AS IMPORTANT

Share on Facebook  Share on Twitter  Share on Google+  Share on Weibo  Share on Reddit  Share on Digg  Share on Tumblr    Delicious

  RELATED


  0 COMMENT


No comment for this article.


  WRITE ARTICLE

RUBY, how fast can you run?

By sonic0002
Will this RUBY car perform better than a PYTHON car if there is any? Is the boss of this car manufacturer a Ruby programmer before?   Great thanks to the image author Sina Weibo user @yutopipp for his authorization