MariaDB Enterprise Server InnoDB Row Formats

Overview

The row format used by InnoDB determines how it arranges data in the tablespace file. InnoDB supports several different row formats. The features and limitations supported by InnoDB vary between row formats.

InnoDB Row Formats

InnoDB supports several row formats:

Set the Row Format

To set the row format for an InnoDB table, use the ROW_FORMAT table option.

For new tables, the table option can be specified 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)
)
ENGINE = InnoDB
ROW_FORMAT = Dynamic;

For existing tables, the table option can be specified using the ALTER TABLE statement.

ALTER TABLE hq_sales.invoices
   ROW_FORMAT = Dynamic;

Default Row Format

The default row format for InnoDB tables is determined by the innodb_default_row_format system variable. In all versions of ES, the default InnoDB row format is Dynamic.

The default row format is used when the ROW_FORMAT table option is not set:

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)
)
ENGINE = InnoDB;

Query the Row Format

The row format for InnoDB tables can be queried in multiple ways.

When the row format was explicitly set for a table, the table's row format is shown in the output of SHOW CREATE TABLE:

SHOW CREATE TABLE hq_sales.invoices\G
*************************** 1. row ***************************
       Table: invoices
Create Table: CREATE TABLE `invoices` (
  `invoice_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `invoice_date` datetime(6) DEFAULT NULL,
  `invoice_total` decimal(13,2) DEFAULT NULL,
  `payment_method` enum('NONE','CASH','WIRE_TRANSFER','CREDIT_CARD','GIFT_CARD') DEFAULT NULL,
  PRIMARY KEY (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

However, when the table's row format was implicitly set by innodb_default_row_format, the table's row format is not shown in the output of SHOW CREATE TABLE.

In that case, the table's row format can be shown by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME LIKE 'hq_sales/invoices'\G
*************************** 1. row ***************************
      NAME: hq_sales/invoices
ROW_FORMAT: Dynamic

Feature Summary

Feature

Dynamic

Compressed

Compact

Redundant

Default

Yes

No

No

No

Recommended

Yes

No

No

No

Efficiently stores large columns

Yes

Yes

No

No

Efficiently utilizes buffer pool

Yes

No

Yes

Yes

Supported Page Sizes

  • 64k

  • 32k

  • 16k

  • 8k

  • 4k

  • 16k

  • 8k

  • 4k

  • 64k

  • 32k

  • 16k

  • 8k

  • 4k

  • 64k

  • 32k

  • 16k

  • 8k

  • 4k

Maximum size of indexed column values

  • 3072 bytes (innodb_page_size >= 16k)

  • 1536 bytes (innodb_page_size == 8k)

  • 768 bytes (innodb_page_size == 4k)

  • 3072 bytes (innodb_page_size == 16k)

  • 1536 bytes (innodb_page_size == 8k)

  • 768 bytes (innodb_page_size == 4k)

767 bytes

767 bytes

Supports ADD/DROP column with INSTANT Algorithm

Yes

No

Yes

Yes

Basic Usage

CREATE DATABASE hq_sales;

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)
)
ENGINE = InnoDB
ROW_FORMAT = Dynamic;

SELECT NAME, ROW_FORMAT
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';
+-------------------+------------+
| NAME              | ROW_FORMAT |
+-------------------+------------+
| hq_sales/invoices | Dynamic    |
+-------------------+------------+