One of the main reasons given for using auto-increment PK in MySQL is that it guarantees all inserts into the clustered PK index will be in order and hence fast. I understand that.
But what about secondary indexes? Say my table has a secondary index. Inserts will be in-order with respect to the PK clustered index, but out-of-order with respect to the secondary index B+ Tree.
So wouldn't the inserts still be slow because MySQL needs to be constantly re-arranging the secondary index B+ Tree as inserts are coming in?
I just wondered if using auto-increment here really is buying me anything in terms of insert performance. Would greatly appreciate some clarifications here.
The primary key will be clustered, which means that it directly points to the data on disk. Having to rearrange that data means that full records must be moved around. For a secondary index, it is really just a bunch of pointers to locations on disk. The secondary index has nothing to do with the ordering of the records, so having to shift pointers around in a secondary index is just that, moving pointers. This is a much faster operation than having to move full records.