Monday, November 03, 2008

A quick usability hack with partitioning

A few days ago I was describing a common grievance when using partitions.
When you care at a table, like the following
CREATE TABLE t1 ( d DATE ) 
PARTITION by range (to_days(d))
(
partition p001 VALUES LESS THAN (to_days('2001-01-01'))
, partition p002 VALUES LESS THAN (to_days('2001-02-01'))
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);

Then you have the problem of finding out the original values. SHOW CREATE TABLE doesn't help.
show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851) ENGINE = MyISAM, PARTITION p002 VALUES LESS THAN (730882) ENGINE = MyISAM, PARTITION p003 VALUES LESS THAN (730910) ENGINE = MyISAM) */

I spent some time writing a MySQL Proxy module that, among other things, gives you the original values with SHOW CREATE TABLE. However, I was mostly reinventing the wheel in this case, because there is some useful data in the information_schema. Look:

select
PARTITION_NAME,
PARTITION_DESCRIPTION
from
information_schema.PARTITIONS
where
table_schema ='test' and table_name='t1';
+----------------+-----------------------+
| PARTITION_NAME | PARTITION_DESCRIPTION |
+----------------+-----------------------+
| p001 | 730851 |
| p002 | 730882 |
| p003 | 730910 |
+----------------+-----------------------+

This is as informational as the output of SHOW CREATE TABLE, but having the data in a table, allows us to do the trick.

select
PARTITION_NAME ,
from_days(PARTITION_DESCRIPTION) AS original_value
from
information_schema.PARTITIONS
where
table_schema ='test' and table_name='t1';
+----------------+----------------+
| PARTITION_NAME | original_value |
+----------------+----------------+
| p001 | 2001-01-01 |
| p002 | 2001-02-01 |
| p003 | 2001-03-01 |
+----------------+----------------+

More on this subject when I finish working on my Proxy partition helper.

No comments:

Vote on Planet MySQL