Will a composite Primary Key use the 15/16 page fill factor or the 1/2?

Question

I understand that if a PK is a consecutive number, it will fill a page as its maximum which is a factor of 15/16. If it is a random PK the page fill factor will be of 1/2.

I was wondering if my PK was a composite key.

For example, for a blog post table my PK would be (author_id, post_id). What fill factor would that use ?

Making a PK of this form lets me retrieve all the posts of a given author faster. But I/O wise I was wondering if this was a good shot.

Question is for MySQL DB using InnoDB storage engine.

Answer 1

A composite key like that counts as non-sequential ordering and will be splitting around the 1/2 fill factor, although this depends on the actual usage.

If author 1 makes the first 3 blog posts followed by author 2 making the next 3, you'd get keys like: 11 12 13 24 25 26

and innodb will pick a fill rate closer to 15/16 then 1/2, but if they post intermixed: 11 22 13 24 15 26

innodb will pick a fill rate closer to 1/2 rather then 15/16.

The actual fill rate will be entirely dependent on your data.

For your I/O thoughts, I would use the composite key over an auto-increment as it will be less I/O overhead for a single primary composite key vs a auto-increment key and a composite key together. Given it's a key you are not updating, your effective fill rate shouldn't be overly important and if you run an optimize table, it will compact your primary index to 15/16 anyway, so if you actually notice an performance improvement from a higher packed index, you can manually make it so.