The Truth About Keys and Partitioning
I recently discovered that I’d made a huge blunder in the Partitioning Limitations section of the MySQL 5.1 Manual. I’d had the idea that unique keys don’t effect your choice of partitioning columns unless there’s no primary key.
My latest revision of the documentation for this limitation isn’t much better.
Then, as I was sitting here just now trying to persuade my daughter that she really ought to have something for lunch besides marshmallows, it came to me.
It’s very simple, really.
For any user-partitioned table in MySQL 5.1:
- If a table has any unique keys, then all columns used in the partitioning expression must also be part of any and all of these keys.
- By definition, a primary key is a unique key.
- If the table has no unique keys, then you don’t have to worry about this limitation.
- By definition, a primary key is a unique key.
At the moment, I’ve a very important date for tea, but as soon as I get the chance, I’ll update the Manual with this much simpler version of something I’ve tried to make very complicated for a long time.
“By definition, a primary key is a unique key” is a quite general statement, not related to MySQL partitioning.
Hey — I’m not sure about the wording, actually. “any and all” is kind of ambiguous. I foresee comments on the docs saying “does that mean every key, or “some” key…?”
Perhaps “then EVERY column used in the partitioning expression must be part of EVERY key” (if that’s what you mean).
Sir,
You need to spend some quality time with your daughter, give have all of your attention and regard of her needs.
With Regards, Jaroslaw
pabloj,
This is correct.
——
Xaprb,
The wording that appears in my blog is not necessarily how it’ll appear in the Manual.
This being said, thanks for the suggestion, which I believe is (or at least is very close to) the wording that ought to be used in the Manual.
——
jaroslaw,
Thanks for the kind (and correct) words!
Note: I’ve now updated the description of this limitation in the Manual.