Before creating a compressed table, make sure the
configuration option is enabled, and
innodb_file_format is set to
Barracuda. You can set these parameters in the
my.ini, or with the
statement without shutting down the MySQL server.
To create a compressed table, you might use statements like these:
SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_file_format=Barracuda; CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
If you specify
ROW_FORMAT=COMPRESSED, you can omit
KEY_BLOCK_SIZE; the default compressed page size of 8KB is used.
If you specify
KEY_BLOCK_SIZE, you can omit
ROW_FORMAT=COMPRESSED; compression is enabled automatically.
To determine the best value for
KEY_BLOCK_SIZE, typically you create several copies of the same table with different values for this clause, then measure the size of the resulting
.ibdfiles and see how well each performs with a realistic workload.
For additional performance-related configuration options, see Section 14.12.3, “Tuning Compression for InnoDB Tables”.
The default uncompressed size of InnoDB data
pages is 16KB. Depending on the
combination of option values, MySQL uses a page size of 1KB, 2KB,
4KB, 8KB, or 16KB for the
.ibd file of the
table. The actual compression algorithm is not affected by the
KEY_BLOCK_SIZE value; the value determines how
large each compressed chunk is, which in turn affects how many
rows can be packed into each compressed page.
KEY_BLOCK_SIZE=16 typically does not
result in much compression, since the normal InnoDB
page size is 16KB. This
setting may still be useful for tables with many long
TEXT columns, because such values
often do compress well, and might therefore require fewer
overflow pages as
described in Section 14.12.5, “How Compression Works for InnoDB Tables”.
All indexes of a table (including the
clustered index) are
compressed using the same page size, as specified in the
CREATE TABLE or
ALTER TABLE statement. Table
attributes such as
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
SHOW CREATE TABLE statement).
Because MySQL versions prior to 5.1 cannot process compressed
tables, using compression requires specifying the configuration
avoid accidentally introducing compatibility issues.
Table compression is also not available for the InnoDB
The system tablespace (space 0, the
files) can contain user data, but it also contains internal system
information, and therefore is never compressed. Thus, compression
applies only to tables (and indexes) stored in their own
tablespaces, that is, created with the
Compression applies to an entire table and all its associated
indexes, not to individual rows, despite the clause name