Would Mongo's ObjectID work well in InnoDB's clustered index?
I would believe that it won't work as well and here's why.


it's larger then a int (4 bytes) or bigint (8 bytes). Remember, every other index references the primary key and so it bloats the other indexes. This might not be a concern, but it's something to be aware of.
While the timestamp is sequential, the machine id, process id, and random value are not always going to be, so anything that happens in the same second isn't going to always be in ascending order.


So in conclusion, I don't believe that the Mongo ObjectID is going to perform as well as a smaller, ascending key.

That said, you still can use it as a primary key. It doesn't need to be ascending, it just means that you might have index leaf splits on inserts, which if they're close enough to the end, won't be overly performance impacting. And you can always rebuild the table to compact the indexs.

So, more about the leaf splits. By default, innodb likes a 15/16 fill, leaving 1/16th free for future changes. When the data is inserted in a sequential order, the indexes end up always filled to 15/16.

When the data is inserted randomly/out of order, innodb keeps the pages between 1/2 to 15/16 full, depending on what it believes the end fill rate of the index page will be. This obviously wastes disk space and memory when the index is loaded into memory.