Storage-Engine Independent Column Compression

Storage-engine independent column compression enables TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, VARCHAR and VARBINARY columns to be compressed.

This is performed by means of a new COMPRESSED column attribute:COMPRESSED[=<compression_method>]

Currently the only supported compression method is zlib.

Field Length Compatibility

When using the COMPRESSED attribute, note that FIELD LENGTH is reduced by 1; for example, a BLOB has a length of 65535, while BLOB COMPRESSED has 65535-1. See .

New System Variables

column_compression_threshold

• Description: Minimum column data length eligible for compression.

• Command line: --column-compression-threshold=#

• Scope: Global, Session

• Dynamic: Yes

column_compression_zlib_level

• Description: zlib compression level (1 gives best speed, 9 gives best compression).

• Command line: --column-compression-zlib-level=#

• Scope: Global, Session

• Dynamic: Yes

column_compression_zlib_strategy

• Description: The strategy parameter is used to tune the compression algorithm. Use the value DEFAULT_STRATEGY for normal data, FILTERED for data produced by a filter (or predictor), HUFFMAN_ONLY to force Huffman encoding only (no string match), or RLE to limit match distances to one (run-length encoding). Filtered data consists mostly of small values with a somewhat random distribution. In this case, the compression algorithm is tuned to compress them better. The effect of FILTERED is to force more Huffman coding and less string matching; it is somewhat intermediate between DEFAULT_STRATEGY and HUFFMAN_ONLY. RLE is designed to be almost as fast as HUFFMAN_ONLY

column_compression_zlib_wrap

• Description: If set to 1 (0 is default), generate zlib header and trailer and compute adler32 check value. It can be used with storage engines that don't provide data integrity verification to detect data corruption.

• Command line: --column-compression-zlib-wrap{=0|1}

• Scope: Global, Session

New Status Variables

Column_compressions

• Description: Incremented each time field data is compressed.

• Scope: Global, Session

• Data Type: numeric

Column_decompressions

• Description: Incremented each time field data is decompressed.

• Scope: Global, Session

• Data Type: numeric

Limitations

• The only supported method currently is zlib.

• The storage engine stores data uncompressed on-disk even if the COMPRESSED attribute is present.

• It is not possible to create indexes over compressed columns.

Comparison with InnoDB Page Compression

Storage-independent column compression is different to in a number of ways.

• It is storage engine independent, while InnoDB page compression applies to InnoDB only.

• By being specific to a column, one can access non-compressed fields without the decompression overhead.

• Only zlib is available, while InnoDB page compression can offer alternative compression algorithms.

• It is not recommended to use multiple forms of compression over the same data.

Examples

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}