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.

1 comment:

Unknown said...

Excellent help. I hope i could get some answer because i have a big problem with my huge table.
I have partition a table by month (to_days) according the guideness. But when i run a query from ie '2014-03-01' to '2014-06-30' everything works find but if i expand my query from '2014-03-01' to '2014-07-30' or from '2014-04-01' to '2014-08-30' nothing works. Explain shows me the right partitions but the rows info is the amount of total rows (like if it were scanning the whole table). Im looking for several resources and i have read everyone, and i dont know where else to find because neither on spanish nor on english i could have found a solution.
By the way, i have 19 partitions from 2014/03-2015/06.
I have indexed by date_load
I have a composite constraint (id --autoincrement, and date_load)
I have tried to make index after or before partition and nothing works
I have tried changing the engine and nothing
My Mysqlserver version is 5.1 ...and something (not remember it very well).
Server is on Debian