Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Mongo's ObjectID is defined like this:

ObjectId is a 12-byte BSON type, constructed using:

a 4-byte value representing the seconds since the Unix epoch,

a 3-byte machine identifier,

a 2-byte process id,

and a 3-byte counter, starting with a random value.

Assuming the PRIMARY key was BINARY(12), will this work as well as an auto incremented INT? Does it count as sequential for InnoDB's clustered index? Would gaps in the timestamp be a problem?

share|improve this question
    
Id be interested in this too. Have you tested it out? –  NightWolf Jun 6 '13 at 6:29
    
Just make the primary key "INT" and you will be fine. You can mask out your bits from the left for sure. –  Niko Kiiskinen Jun 12 '13 at 20:21

1 Answer 1

up vote 1 down vote accepted

I would believe that it won't work as well and here's why.

  1. 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.

  2. 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.

share|improve this answer
    
Can you elaborate more on the leaf splits? –  Luke Jun 7 '13 at 15:31
    
The time-stamp is in MS, but lets say we have many inserts per millisecond, is the randomness of the PID and Machine ID useful in that it helps to avoid hot spots. I.e. always dumping everything to the end of the table is going to result in a page lock? I know this was an issue with some other DBs like SQL Server 08. –  NightWolf Jun 8 '13 at 4:53
    
InnoDB locks are much finer then per page. There are three index lock types that are used. Innodb uses a gap lock for prepending/appending records. This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. So no, there are no issues with hot spots, as it actually locks the future position and no the entire page –  kormoc Jun 8 '13 at 19:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.