MariaDB Enterprise Server InnoDB Dynamic Row Format

Supported Index Prefix Limits

The limit for indexing column values depends on the innodb_page_size value:

Create an InnoDB Table with the Dynamic Row Format by Default

InnoDB uses the Dynamic row format for new InnoDB tables by default, because the innodb_default_row_format system variable is dynamic by default.

Let's create an InnoDB table after confirming that the default storage engine is InnoDB and that InnoDB's default row format is Dynamic:

1. Connect to the server using MariaDB Client:

   $ mariadb --user=root
   

2. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

   SHOW SESSION VARIABLES
      LIKE 'default_storage_engine';
   

   +------------------------+--------+
   | Variable_name          | Value  |
   +------------------------+--------+
   | default_storage_engine | InnoDB |
   +------------------------+--------+
   

3. Confirm that InnoDB's default row format is Dynamic by checking the innodb_default_row_format system variable using the SHOW GLOBAL VARIABLES statement:

   SHOW GLOBAL VARIABLES
      LIKE 'innodb_default_row_format';
   

   +---------------------------+---------+
   | Variable_name             | Value   |
   +---------------------------+---------+
   | innodb_default_row_format | dynamic |
   +---------------------------+---------+
   

4. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

   CREATE DATABASE hq_sales;
   

5. Create the table using the CREATE TABLE statement:

   CREATE TABLE hq_sales.invoices (
      invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
      branch_id INT NOT NULL,
      customer_id INT,
      invoice_date DATETIME(6),
      invoice_total DECIMAL(13, 2),
      payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
      PRIMARY KEY(invoice_id)
   );
   

6. Confirm that the table uses the Dynamic row format by querying the information_schema.INNODB_SYS_TABLES table:

   SELECT NAME, ROW_FORMAT
   FROM information_schema.INNODB_SYS_TABLES
   WHERE NAME='hq_sales/invoices';
   

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

Create an InnoDB Table with the Dynamic Row Format using ROW_FORMAT

An InnoDB table that uses the Dynamic row format can be created using the the ROW_FORMAT table option.

Let's create an InnoDB table after confirming that the default storage engine is InnoDB and that InnoDB's default row format is not Dynamic:

1. Connect to the server using MariaDB Client:

   $ mariadb --user=root
   

2. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

   SHOW SESSION VARIABLES
      LIKE 'default_storage_engine';
   

   +------------------------+--------+
   | Variable_name          | Value  |
   +------------------------+--------+
   | default_storage_engine | InnoDB |
   +------------------------+--------+
   

3. Confirm that InnoDB's default row format is not Dynamic by checking the innodb_default_row_format system variable using the SHOW GLOBAL VARIABLES statement:

   SHOW GLOBAL VARIABLES
      LIKE 'innodb_default_row_format';
   

   +---------------------------+---------+
   | Variable_name             | Value   |
   +---------------------------+---------+
   | innodb_default_row_format | compact |
   +---------------------------+---------+
   

4. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

   CREATE DATABASE hq_sales;
   

5. Create the table using the CREATE TABLE statement, and specify the Dynamic row format using the ROW_FORMAT table option:

   CREATE TABLE hq_sales.invoices (
      invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
      branch_id INT NOT NULL,
      customer_id INT,
      invoice_date DATETIME(6),
      invoice_total DECIMAL(13, 2),
      payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
      PRIMARY KEY(invoice_id)
   ) ROW_FORMAT = Dynamic;
   

6. Confirm that the table uses the Dynamic row format by querying the information_schema.INNODB_SYS_TABLES table:

   SELECT NAME, ROW_FORMAT
   FROM information_schema.INNODB_SYS_TABLES
   WHERE NAME='hq_sales/invoices';
   

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

Convert InnoDB Tables to the Dynamic Row Format

If your database was physically upgraded from some older version of MariaDB Server or MySQL, then some of your tables may not be using the Dynamic row format. If you want to get the benefits of the Dynamic row format, then those tables will need to be converted to use it.

Let's convert some InnoDB tables to the Dynamic row format:

1. Connect to the server using MariaDB Client:

   $ mariadb --user=root
   

2. Search for InnoDB tables that do not use the Dynamic 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 != 'Dynamic';
   

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

3. Alter the table using the ALTER TABLE statement, and specify the Dynamic row format using the ROW_FORMAT table option:

   ALTER TABLE hq_sales.invoices
      ROW_FORMAT = Dynamic;
   

4. Confirm that the table uses the Dynamic row format by querying the information_schema.INNODB_SYS_TABLES table again:

   SELECT NAME, ROW_FORMAT
   FROM information_schema.INNODB_SYS_TABLES
   WHERE NAME='hq_sales/invoices';
   

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