MariaDB Enterprise Server InnoDB Row Formats

This page is part of MariaDB's Documentation.

The parent of this page is: InnoDB Architecture for MariaDB Enterprise Server

Topics on this page:

Overview
InnoDB Row Formats
Set the Row Format
Default Row Format
Query the Row Format
Feature Summary
Basic Usage

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    |
+-------------------+------------+