MyISAM storage engine has key compression which makes its indexes much smaller, allowing better fit in caches and so improving performance dramatically. Actually packed indexes not a bit longer rows is frequent reason of MyISAM performing better than Innodb. In this article I’ll get in a bit more details about packed keys and performance implications it causes.
First lets see how key compression works. Key compression applies on the block boundaries – first index value is stored fully while for following value index will contain value of the bytes sames as in previous cases + the all bytes which are different. So lets say you have page starting with index values “aaaaaa” and “aaaaab”. They will be encoded as “aaaaaa”, “<5>b”. The similar compression applies to data pointer for the same key value. It also can be used for numbers as numbers stored with high byte first. This is obviously a bit of simplification but it should be enough to understand performance implications of this approach.
Note trailing spaces for strings are not stored in the index even if it is not packed.
Compressed blocks need to be treated differently. For uncompressed index blocks MySQL can do binary search inside the page – kind of jumping in the middle and looking at the key value and repeating it then repeating it for left or right half. For compressed block this is not going to work and MySQL will need to scan keyblock from the start uncompressing keys to find matching key value. This means the following will apply:
So how you control which indexes are packed and which are not ? Unfortunately at this point you can’t set compression on per index basics. Neither you can set index block size for the keys. So you can’t look at your index and say “OK. This is going to be typically scanned forward, so I can pack it” The only settings available is PACK_KEYS=0|1|DEFAULT. Value 0 disables compression for all keys. Value 1 forces it for all keys. Value DEFAULT will use key compression for character column type but not for numerical and other column types.
Also according to my tests it looks there is something special in how integer primary key is handled. I can’t see the index size difference between DEFAULT and 1 values for such key. However it I change it to “key” the size reduces if PACK_KEYS=1. Might be it is some kind of special hack
The fact MyISAM will by default compress string keys but not integer keys is the biggest reason joins using string keys is so much slower compared to integer keys. Integer keys still best but for Innodb for example difference is not so large.
Let us try couple of benchmarks now. I’ve created simple table with 100.000 rows with columns. And I set id=c to keep it simple:
id int(10) unsigned NOT NULL ,
c varchar(20) NOT NULL default ”,
As we can see difference between 1 and DEFAULT is rather small for this case, as integers are relatively short.
Now lets do some benchmarks. I’m testing 4 types of queries:
So we can see theory goes pretty much in hand with practice here. With DEFAULT configuration lookups for integer keys are fast and forward scans are. It is however slow to do reverse scan on the string index.
It is interesting to see PACK_KEYS=0 does not always improve performance even if data fits in memory. Take a look at Q3 for. The the reason perhaps is – smaller index side, which means less traffic to memory. With modern CPUs it might be actually faster to do a bit of uncompression than to traverse large memory areas. Reverse index scan performance got some 8 times faster and join on string value is almost 3 times faster.
With PACK_KEYS=1 we got join by integer key performing almost 5 times slower than it was – this is something really to watch out for.
Note – these numbers are for CPU bound workload. If your load is disk bound there are more reasons to have keys packed so cache hit is better. Especially if you can get hot indexes in memory by packing them it is almost no brainer
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.