InnoDB Plugin 1.0 for MySQL 5.1 User's Guide  /  InnoDB Data Compression  /  Enabling Compression for a Table

3.2. Enabling Compression for a Table

The usual (uncompressed) size of InnoDB data pages is 16KB. Beginning with the InnoDB Plugin, you can use the attributes ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE, or both in the CREATE TABLE and ALTER TABLE statements to enable table compression. Depending on the combination of option values, InnoDB attempts to compress each page to 1KB, 2KB, 4KB, 8KB, or 16KB.


The term KEY_BLOCK_SIZE does not refer to a key, but simply specifies the size of compressed pages to use for the table. Likewise, in the InnoDB Plugin, compression is applicable to tables, not to individual rows, despite the option name ROW_FORMAT. Because the InnoDB storage engine cannot add syntax to SQL statements, the InnoDB Plugin re-uses the clauses originally defined for MyISAM.

To create a compressed table, you might use a statement like this:

 (column1 INT PRIMARY KEY) 

If you specify ROW_FORMAT=COMPRESSED but not KEY_BLOCK_SIZE, the default compressed page size of 8KB is used. If KEY_BLOCK_SIZE is specified, you can omit the attribute ROW_FORMAT=COMPRESSED.

Setting KEY_BLOCK_SIZE=16 most often does not result in much compression, since the normal InnoDB page size is 16KB. However, this setting may be useful for tables with many long BLOB, VARCHAR or TEXT columns, because such values often do compress well, and might therefore require fewer overflow pages as described later in this section.

Note that compression is specified on a table-by-table basis. All indexes of a table (including the clustered index) are compressed using the same page size, as specified on the CREATE TABLE or ALTER TABLE statement. Table attributes such as ROW_FORMAT and KEY_BLOCK_SIZE are not part of the CREATE INDEX syntax, and are ignored if they are specified (although you see them in the output of the SHOW CREATE TABLE statement).

User Comments
Sign Up Login You must be logged in to post a comment.