MySQL Forums
Forum List  »  Partitioning

Re: Partition Management in MySQL 5.1
Posted by: Steven Roussey
Date: November 07, 2005 07:36PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
14679
October 06, 2005 06:41PM
Re: Partition Management in MySQL 5.1
3167
November 07, 2005 07:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.