MariaDB Enterprise Server InnoDB Compact Row Format

Overview

The Compact row format was the default in MariaDB Server 10.1 and older versions. Some details about the Compact row format:

  • If you performed a physical upgrade from older version of MariaDB Server or MySQL to a newer MariaDB Server version, then some of your tables may still use this row format.

  • The Compact row format does not store large columns as efficiently as the Dynamic row format.

  • The Compact row format limits indexing column values to 767 bytes, which is significant smaller than the Dynamic row format.

List InnoDB Tables that use the Compact Row Format

If your database was physically upgraded from some older version of MariaDB Server or MySQL, then some of your tables may be using the Compact row format. It is easy to check.

Let's find InnoDB tables that use the Compact row format:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  2. List InnoDB tables that use the Compact row format by querying the information_schema.INNODB_SYS_TABLES table:

    SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME NOT LIKE 'SYS_%'
AND ROW_FORMAT = 'Compact';

    +-------------------+------------+
| NAME              | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Compact    |
+-------------------+------------+

  3. If any tables are found, consider converting them to the Dynamic row format:

    ALTER TABLE hq_sales.invoices
   ROW_FORMAT = Dynamic;