When A Page Fills Up
Each btree or table page has limited room. When an insert or update needs space on a full page, the engine performs a page split: it allocates a new page and moves roughly half the rows over, then updates parent pointers. Splits cause extra writes and fragment the index.
The Fill Factor
The fill factor is the percentage of a page the engine fills when first building or rebuilding an index. A fill factor of one hundred packs pages completely, while a lower value leaves slack.
- High fill factor packs more rows per page, so reads touch fewer pages but inserts split sooner.
- Lower fill factor reserves room for future inserts and updates, reducing splits at the cost of larger indexes.
Choosing A Value
- For append only keys like an autoincrement id, a high fill factor is fine since inserts land at the end.
- For random key inserts or frequently updated rows, leaving slack avoids constant splitting.
Updates that grow a variable length row can also trigger splits or move the row, so updates count, not just inserts.
Key idea
Page splits happen when a full page must grow; the fill factor reserves free space up front to trade index size against how often those expensive splits occur.