Re: Partition Management in MySQL 5.1
> I am not sure however why you ORDER BY on more than a,b since it was the
> primary key and thus should be unique so there is only one record to sort on
> c,d.
My bad. I was in error. I was thinking of an old setup (since changed) to get around some MySQL optimizer and MyISAM problems. Should I find the time to better explain them, maybe I'll suggest fixing them in another forum (InnoDB has less of the issue).
Anyway!
I think if an order by on the field that is used to decide partitioning would be good enough.
For a real world thing: We partition in the application for a forum message table, based on the (forumid) split ten ways, based on the last digit. Our maintenance schedule does a weekly ORDER BY optimization on each table on (forumid, rootmessageid), where the PK is (forumid,messageid).
So the partitioning is on (forumid),the PK is (forumid,messageid), and the order by is on (forumid, rootmessageid).
Doing the ORDER BY has miraculous consequences. Nowadays, we use RAID 10 over 8 15K RPM disks for each server, so we could probably do it monthly rather than weekly. But being able to have the data clumped together on the disk when you are getting many BIG records, really makes a difference after the table has been used for a while. Its why we use MySQL, and why we use MyISAM instead of InnoDB.