MySQL Forums
Forum List  »  Partitioning

Re: partitioning dilema
Posted by: Phil Hildebrand
Date: April 17, 2008 08:33AM

If the data has a datetime (or similar date type) column as part of the primary key, then RANGE would be an ideal way to partition the data if those are your needs (hopefully the data is accessed this way for a majority of the queries as well).

There shouldn't be any need to use Java to do the partitioning, however if I understand you correctly. If you are using MySQL 5.1 you should be able to define your partitions when you create your tables.

Dropping a range partition that is no longer needed is very fast, and would be ideal for what your describing, and it would be easy to pre-allocate partitions as well.

You'll need to chose a function to convert your datetime partitioning key (if it's not already an integer that is associated with a date). If you are thinking weekly, then you might try yearwk() or to_days()

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions.html

Ex:

mysql> select mydate ,yearweek(mydate), yearweek(now()) - yearweek(mydate) from dtest;
+---------------------+------------------+------------------------------------+
| mydate | yearweek(mydate) | yearweek(now()) - yearweek(mydate) |
+---------------------+------------------+------------------------------------+
| 2008-04-12 22:44:30 | 200814 | 1 |
| 2008-03-15 00:00:00 | 200810 | 5 |
| 2008-03-15 00:00:00 | 200810 | 5 |
| 2008-03-15 00:00:00 | 200810 | 5 |
| 2008-04-08 22:45:03 | 200814 | 1 |
| 2008-04-07 22:45:06 | 200814 | 1 |
| 2008-04-06 22:45:11 | 200814 | 1 |
| 2008-04-05 22:45:14 | 200813 | 2 |
| 2008-04-04 22:45:18 | 200813 | 2 |
| 2008-04-03 22:45:22 | 200813 | 2 |
| 2008-03-01 00:00:00 | 200808 | 7 |
+---------------------+------------------+------------------------------------+

alter table dtest
partition by range (yearweek(mydate))
(
partition p1 values less than (200809),
partition p2 values less than (200810),
partition p3 values less than (200811),
partition p4 values less than (200812),
partition p5 values less than (200813),
partition p6 values less than (200814),
partition p7 values less than (200815),
partition p8 values less than (200816)
);

mysql> select partition_name,table_rows from information_schema.partitions where table_name = 'dtest';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p1 | 1 |
| p2 | 0 |
| p3 | 3 |
| p4 | 0 |
| p5 | 0 |
| p6 | 3 |
| p7 | 4 |
| p8 | 0 |
+----------------+------------+
8 rows in set (0.00 sec)

then just drop the old partition:

mysql> alter table dtest drop partition p1;
Query OK, 0 rows affected (0.01 sec)

mysql> select partition_name,table_rows from information_schema.partitions where table_name = 'dtest';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p2 | 0 |
| p3 | 3 |
| p4 | 0 |
| p5 | 0 |
| p6 | 3 |
| p7 | 4 |
| p8 | 0 |
+----------------+------------+
7 rows in set (0.01 sec)

I would 'pre-allocate' weekly partitions (or maybe that's what you would be doing with the java, which would work as well)..

when you need a new one, just add it to the end:

mysql> alter table dtest add partition (partition p9 values less than (200817));

You might want to name the partitions with more meaning (IE: partition p_200817)

Options: ReplyQuote


Subject
Views
Written By
Posted
3692
April 17, 2008 04:03AM
Re: partitioning dilema
2483
April 17, 2008 08:33AM


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.