Your MySQL index isn’t slow; it’s fragmented :) Databases like MySQL that hold data in B+ trees suffer from index fragmentation, and this severely impacts the performance; hear me out…
Index fragmentation happens when B+ tree index pages contain significant amounts of free space instead of being densely packed with data. But why would that happen?
MySQL’s InnoDB stores data in clustered indexes (organized by primary key). When the clustered index fragments due to random primary key inserts (like UUIDs), data retrieval performance takes a hit.
Because the engine will try to keep the leaves ordered, this leads to a page split to insert the row in the middle. The page splits when there isn’t enough space in a page, or it exceeds the split threshold. Over time, repeated random inserts cause more splits, wasting much space.
Index fragmentation directly impacts query performance and memory utilization. When indexes are fragmented, the database must read more pages from disk to execute the same query, increasing I/O operations and reducing throughput.
By the way, fragmentation is minimal when insertions are sequential, as InnoDB simply creates new pages without splitting existing ones, and placing them along the rightmost path of the tree. This helps in maintaining optimal page density.
You can manage index fragmentation by tuning innodb_fill_factor and/or firing the following query
ALTER TABLE tbl_name FORCE;Hope you found this interesting :)