InnoDB Tablespaces, Pages, Row Formats, Clustered/Secondary Indexes, and Record Layout

Learning outcomes

ServiceHub has grown from a few thousand work orders to millions. Query latency is still acceptable, but storage grows faster than expected and every new secondary index is surprisingly expensive. One engineer proposes “compress everything”; another proposes a 36-character UUID primary key on every table. Neither proposal begins with the mechanism that determines the cost: InnoDB organizes table rows through a clustered index, stores index structures in pages inside tablespaces, and carries the clustered-key value in secondary-index entries. Primary-key width and row format therefore propagate into physical storage decisions.

This lesson maps MariaDB SQL objects to InnoDB physical structures without pretending that page internals are a stable application API. A tablespace is an InnoDB storage container, a page is InnoDB’s unit of storage/cache I/O, the clustered index is the B-tree whose leaf records contain the table row, and a secondary index is another B-tree whose entries contain the secondary key plus the clustered-key value needed to reach the row. These mechanisms explain why seemingly “logical” schema choices can alter write amplification, cache footprint, and page-split behavior.

1. Build a storage lab you can inspect

Start with a disposable database that is large enough to create multiple pages but small enough for a laptop. The helper digits table generates 8,000 rows without requiring an external dataset. The notes column is intentionally wider than ordinary OLTP text so DYNAMIC row-format behavior has something meaningful to work with.

DROP DATABASE IF EXISTS servicehub_innodb_lab;CREATE DATABASE servicehub_innodb_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_innodb_lab;CREATE TABLE digits (n TINYINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE TABLE work_orders (  work_order_id BIGINT NOT NULL AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status VARCHAR(20) NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  summary VARCHAR(180) NOT NULL,  notes LONGTEXT NULL,  PRIMARY KEY (work_order_id),  KEY ix_work_orders_status (status, priority, work_order_id),  KEY ix_work_orders_customer (customer_id, opened_at)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;INSERT INTO work_orders(customer_id,status,priority,opened_at,summary,notes)SELECT 1 + (x.n % 250),       ELT(1 + (x.n % 4),'queued','open','closed','cancelled'),       1 + (x.n % 5),       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL x.n SECOND,       CONCAT('ServiceHub order ', x.n),       RPAD(CONCAT('diagnostic-note-',x.n,' '), 1200, 'x')FROM (  SELECT a.n + 10*b.n + 100*c.n + 1000*d.n AS n  FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits d) AS xWHERE x.n BETWEEN 1 AND 8000;

SELECT VERSION() AS server_version;SHOW VARIABLES WHERE Variable_name IN ('innodb_page_size','innodb_default_row_format','innodb_file_per_table');SHOW TABLE STATUS FROM servicehub_innodb_lab LIKE 'work_orders'\GSHOW CREATE TABLE servicehub_innodb_lab.work_orders\GSHOW INDEX FROM servicehub_innodb_lab.work_orders;

The evidence proves which storage engine and row format MariaDB actually created, which indexes exist, and the server’s configured page size. SHOW TABLE STATUS size fields are useful estimates, not byte-exact live allocation ledgers. Filesystem sizes can include preallocation, reusable free space, and background effects, so do not convert one observation into an exact “bytes per row” formula.

2. The clustered index is the table’s physical ordering structure

InnoDB stores table rows at the leaf level of one B-tree called the clustered index. When a table has a primary key, that primary key is the natural clustered key. If no primary key exists, InnoDB can select a suitable unique non-null index; otherwise it creates an internal row identifier. This fallback makes the table function, but it hides row identity from application design and can make future migrations or replication diagnostics harder to reason about.

A secondary index on (status, priority, work_order_id) is not a standalone map to a physical heap row. Its leaf entries contain the secondary key and enough clustered-key information to locate the base row. That is why adding a wide primary key to a table with eight secondary indexes can be more expensive than the primary index alone suggests.

3. Prove index shape before guessing about size

SELECT INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX,       COLUMN_NAME, SUB_PART, INDEX_TYPEFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub_innodb_lab'  AND TABLE_NAME='work_orders'ORDER BY INDEX_NAME, SEQ_IN_INDEX;SELECT TABLE_NAME, ENGINE, ROW_FORMAT,       TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREEFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_innodb_lab'  AND TABLE_NAME='work_orders';

These views give you schema-level evidence and approximate allocation. They do not expose every byte in every B-tree node. Low-level InnoDB dictionary tables and page-parsing tools exist, but their exact columns/formats change across generations. Production automation should prefer documented metadata surfaces unless you are doing a deliberate forensic investigation against a pinned server build.

4. Row formats decide how records and large values are laid out

MariaDB documents four InnoDB row formats: REDUNDANT, COMPACT, DYNAMIC and COMPRESSED. DYNAMIC is the modern default and is the normal baseline for new OLTP tables. COMPACT and REDUNDANT are mostly important when you inherit older tables or perform compatibility work. COMPRESSED can reduce space but introduces CPU/memory/concurrency tradeoffs and is not a blanket “make it smaller” switch.

CREATE TABLE compact_probe (  id BIGINT PRIMARY KEY,  payload TEXT NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPACT;CREATE TABLE dynamic_probe (  id BIGINT PRIMARY KEY,  payload TEXT NOT NULL) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;SHOW TABLE STATUS FROM servicehub_innodb_labWHERE Name IN ('compact_probe','dynamic_probe');

The output confirms the configured row format, not that every value is off-page. InnoDB decides record placement from the actual row and page constraints. The useful mental model is “DYNAMIC can move more of a large value out of the clustered record when needed,” not “every TEXT lives in a separate file.”

5. Pages, splits and insertion locality

B-tree pages must maintain key order. When an insert needs space in a full target page, InnoDB may split/reorganize pages so both old and new entries remain ordered. Sequential surrogate keys often concentrate inserts near the right edge; random keys distribute inserts across the tree. Neither is automatically correct for every architecture: globally generated identifiers may solve distributed ownership problems that outweigh local B-tree costs.

Page splits are one reason “index count” is not the entire write cost. Every INSERT or primary-key-changing operation can touch the clustered tree plus all affected secondary trees. UPDATEs to indexed columns can remove and reinsert index entries. The correct capacity question is therefore workload-specific: how many rows, how wide are keys, how many secondary indexes, what is the update pattern, and how much of those trees stays hot in the buffer pool?

6. Deliberately wrong approach: estimate storage from SQL column widths only

A common spreadsheet model adds declared column lengths and concludes that an index with two columns must be exactly that sum per row. That ignores record headers, variable-length metadata, NULL representation, page fill, clustered-key payloads in secondary indexes, overflow pages, free space, fragmentation and internal structures. The result may look precise while being operationally wrong.

SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREEFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_innodb_lab'  AND TABLE_NAME='work_orders';ALTER TABLE work_orders  ADD KEY ix_summary_prefix (summary(40));ANALYZE TABLE work_orders;SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREEFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_innodb_lab'  AND TABLE_NAME='work_orders';ALTER TABLE work_orders DROP INDEX ix_summary_prefix;

Even this before/after experiment is approximate because allocation occurs in pages/extents and background state can change. The repair is not “find a better magic formula”; it is to combine schema reasoning with measured allocation under representative row counts and distributions.

7. Lab checklist, production judgment, and bridge

1. Reset the lab and record innodb_page_size, row format and innodb_file_per_table.
2. Inventory the clustered/secondary index definitions.
3. Explain which columns appear in each secondary key and which clustered key it ultimately uses as a row locator.
4. Create COMPACT and DYNAMIC probe tables and verify their metadata.
5. Add and remove a disposable prefix index while recording allocation estimates.
6. Write down one schema change that would make every secondary index wider.

The next lesson follows these pages into memory. You will distinguish clean from dirty buffer-pool pages, logical read requests from physical reads, read-ahead from useful demand reads, and learn why a 99.9% hit ratio can coexist with bad latency.