Gaps in AUTO_INCREMENT sequences — why they happen and why to leave them

Updated August 2026 · First published 2007 by David J. Lake

Part of the classic SQL Help series first published on this domain by David J. Lake (guelphdad). Rewritten from scratch and updated for MySQL 8 — same address, modern SQL.

"My ids go 1, 2, 3, 7, 12 — how do I fix the gaps?"

Short answer: you don't. There's nothing to fix.

Where gaps come from

Gaps are a normal by-product of how MySQL hands out auto-increment values. You'll get them from:

Deleted rows — the classic cause. Delete row 5 and there will never be another 5.
Rolled-back transactions — a value once claimed is not returned, even if the insert never commits.
INSERT IGNORE, ON DUPLICATE KEY UPDATE and failed inserts — values can be burned without a row appearing.
Bulk and multi-row inserts — InnoDB may reserve a block of values up front and not use them all.

None of these are errors. The engine's only promise is that each value is handed out once — not that the sequence is dense. (Since MySQL 8.0 the counter also survives server restarts, so even a crash won't cause values to be reused.)

Why renumbering is the wrong fix

A surrogate key's one job is to identify a row, forever. The moment you renumber rows to close gaps:

every foreign key referencing those rows must be rewritten in lockstep, or your data is silently corrupted;
every external reference — bookmarked URLs, invoices, log files, emails mentioning "order #1042" — now points at the wrong thing;
and the very next delete puts a gap right back.

If something in your application breaks because ids aren't consecutive, the application is misusing the id. Users should never be shown row counts via ids — that's what COUNT(*) is for — and "sequence number 5 of 12" is a display-time calculation (ROW_NUMBER() in MySQL 8), not a storage concern.

What ids are for (and not for)

Use an auto-increment surrogate key when the table has no natural, stable, unique column to serve as the primary key — which in practice is most tables. Treat the value as an opaque handle: fine to use in joins, foreign keys and URLs, meaningless as data. If you need a gapless, human-facing sequence — invoice numbers are the textbook case, sometimes a legal requirement — generate that as its own column with explicit logic and locking, and let the primary key be a boring internal id underneath.