Schema optimization and indexing

MyISAM uses prefix compression to reduce the size of the index, so that more indexes can be put into memory and in some cases performance can be greatly improved. The default is a compressed string, but you can specify it to compress the integer value.

The MyISAM compresses each index block by storing the first value of the block, and then stores each additional value in the block by recording the byte number of the same prefix, plus the actual data of the different suffixes. For example, if the first value is "perform" and the second value is "performance", the second value is stored as "7,ance". MyISAM also prefix compression of adjacent row pointers.

Compressed blocks use less space, but they slow down the main operation. Because the compression prefix for each value depends on its previous value, MyISAM cannot use a binary search in the block to find the desired item and must scan the entire block from scratch. Sequential scans may perform well, but reverse scans-such as order by desc--are not very good. To find a separate line operation in the middle of the block, you need to scan the average, about half a block.

Our benchmark tests show CPU limitations (Cpu-bound), and the compressed key in the MyISAM table slows index lookups because the scans require random lookups. The key to reverse scan compression is even slower. There is a trade-off between CPU resources, one of the memory resources, and hard disk resources.

The compressed index is one-tenth of the original, and if you have an IO limit (io-bound) workload, they can reduce the cost of the primary operation.