Before creating a compressed table, it is usually better to make sure that we are using the InnoDB strict mode. The reason for this is that InnoDB performs more checks on the CREATE TABLE statements when the strict mode is on. If something is wrong with the table definition, the table will not be created and an error will be produced instead. This prevents us from creating tables that are different from what we expect. However, the existing applications rely on the SQL statements they execute, and the strict mode might break them. For this reason, the strict mode is disabled by default, and it is possible to enable it at the session level. To enable the InnoDB strict mode globally, execute the following command:

SET @@global.innodb_strict_mode = ON;

After checking the innodb_file_per_table and innodb_file_format server variables as explained previously, we can create an InnoDB compressed table. To do so, we just need to specify the COMPRESSED row format:

CREATE TABLE comp_table (
  id INT UNSIGNED NOT NULL PRIMARY KEY,
  c1 VARCHAR(255),
  c2 VARCHAR(255)
) ENGINE = InnoDB,
  ROW_FORMAT = COMPRESSED;

For the compressed tables, the size of the index blocks (pages) can be configured on a per-table basis. To do this, we can use the KEY_BLOCK_SIZE option. Since this option only makes sense for compressed tables, in this case, specifying the row format is not necessary:

CREATE TABLE comp_table (
  id INT UNSIGNED NOT NULL PRIMARY KEY,
  c1 VARCHAR(255),
  c2 VARCHAR(255)
) ENGINE = InnoDB,
  KEY_BLOCK_SIZE = 8;

The size of the key blocks does not affect the compression level, which cannot be set per table. However, by changing the page size, we determine how many rows a page can contain. The allowed sizes, expressed in KBs, are 16, 8 (the default), 4, 2, and 1. The value 0 specifies the default value, but in this case, table compression is not used. Normally, KEY_BLOCK_SIZE is lower than innodb_page_size (whose default value is 16). However, if a table contains TEXT or BLOB values, a 16 KB memory size might allow the storage of many values in normal pages, so that they are not stored in the offset pages (as explained in Chapter 6, Caches).

Sometimes, the value we try to assign to KEY_BLOCK_SIZE is not adequate for the table indexes. In this case, having the InnoDB strict mode enabled is very important, because it forces MariaDB to produce an error. If the strict mode is disabled, the table will be created, but when we try to insert or modify a row, an error might occur.

The best way to determine the optimal value for KEY_BLOCK_SIZE is to create several copies of the table, each one using a different size, and then testing their performance on a realistic workload. The performance of a compressed table can be monitored by querying some tables in information_schema, as explained later in this chapter.

It is possible to change the key block size for an existing compressed table using an ALTER TABLE statement:

ALTER TABLE comp_table KEY_BLOCK_SIZE = 16;

It is even possible to compress an existing table:

MariaDB [test]> CREATE TABLE non_comp (
    ->   id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->   c1 VARCHAR(255),
    ->   c2 VARCHAR(255)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.36 sec)
MariaDB [test]> ALTER TABLE non_comp KEY_BLOCK_SIZE = 8;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

In both cases, ALTER TABLE can take time but will use the in-place algorithm with no locks.