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.

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.

share|improve this question

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

share|improve this answer

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.