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 page size value is used, which is half the
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.
KEY_BLOCK_SIZEvalue is treated as a hint; a different size could be used by
InnoDBif necessary. A value of 0 represents the default compressed page size, which is half of the
KEY_BLOCK_SIZEcan only be less than or equal to the
innodb_page_sizevalue. If you specify a value greater than the
innodb_page_sizevalue, the specified value is ignored, a warning is issued, and
KEY_BLOCK_SIZEis set to half of the
innodb_strict_mode=ON, specifying an invalid
KEY_BLOCK_SIZEvalue returns an error.
For additional performance-related configuration options, see Section 14.9.3, “Tuning Compression for InnoDB Tables”.
The default uncompressed size of
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 equal to the
size does not typically result in much compression. For
would not result in much compression, since the normal
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.9.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 for
InnoDB tables, and are ignored if they are
specified (although you see them in the output of the
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