[+/-]
The usual (uncompressed) size of InnoDB data pages is 16KB.
Beginning with the InnoDB storage engine, you can use the attributes
ROW_FORMAT=COMPRESSED
and/or KEY_BLOCK_SIZE
in the
CREATE TABLE
and ALTER TABLE
commands 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 storage engine,
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 storage engine
re-uses the clauses originally defined for
MyISAM
.
To create a compressed table, you might use a command like this:
CREATE TABLEname
(column1 INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
;
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 in
Section 3.4.2.2, “Compressing BLOB, VARCHAR and TEXT Columns”.
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
command. 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
command).
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .