Uncovering MySQL RowID: Detect, Relate to Primary Keys, and Spot Bottlenecks

Problem 1 – Detecting the hidden rowid

In InnoDB tables without an explicit primary key MySQL creates a hidden 6‑byte column named _rowid. It can be queried directly: SELECT _rowid FROM redis_backup_result LIMIT 5; Example output shows sequential values 117‑121. Combining _rowid with COUNT(*) demonstrates that the first row drives the count when a query lacks GROUP BY:

SELECT backup_date, COUNT(*) FROM redis_backup_result; -- missing GROUP BY leads to a single aggregated row
SELECT backup_date, COUNT(*) FROM redis_backup_result GROUP BY backup_date;

Problem 2 – Relationship between rowid and primary key

InnoDB stores rows in primary‑key order (clustered index). The primary key can be:

Explicitly defined with PRIMARY KEY.

Implicitly derived from a non‑NULL UNIQUE index.

If neither exists, InnoDB generates a hidden 6‑byte BIGINT UNSIGNED value that becomes the _rowid.

Secondary indexes store the primary‑key value, so a wide primary key inflates secondary‑index size.

Problem 3 – Hidden risks of using an auto‑increment primary key

Auto‑increment keys often have no business meaning. Over‑reliance on them encourages adding many auxiliary indexes, increasing storage and maintenance cost. Moreover, a long primary key enlarges every secondary index because InnoDB includes the primary key in secondary‑index entries.

Primary keys do not have to be sequential integers; they can embed meaningful attributes (e.g., order numbers, identification codes) to reduce auxiliary indexes.

Problem 4 – Potential rowid overflow and debugging

The hidden _rowid is a 6‑byte unsigned integer, so its maximum value is 2^48 = 281474976710656. To illustrate overflow:

Create a table without any index: CREATE TABLE test_inc(id INT) ENGINE=InnoDB; Insert a few rows: INSERT INTO test_inc VALUES (1),(2),(3); Attach to the MySQL server process with gdb and set the internal row_id to 2^48:

gdb -p <pid> -ex 'p dict_sys->row_id=281474976710656' -batch

Insert additional rows: INSERT INTO test_inc VALUES (4),(5),(6); Query the table: SELECT * FROM test_inc; Result shows that rows with id=1 and id=2 have been overwritten, demonstrating that when _rowid wraps around, new inserts can collide with existing rows.

In practice reaching the 48‑bit limit requires an astronomically large number of inserts, but the behavior is a design consideration. Defining an explicit primary key eliminates this hidden overflow risk because the primary‑key value is controlled by the application.

Reference: rowid debugging techniques were inspired by Ding Lin’s blog https://www.iteye.com/blog/dinglin-1878783.