Description:
Partition Pruning doesn't work if partitioning function has some calculation.
How to repeat:
***** good case
mysql> create table ptest_ok (
-> id int auto_increment,
-> created_at datetime,
-> data varchar(100),
-> primary key (id, created_at)
-> )
-> partition by range (to_days(created_at)) (
-> partition p20090101 values less than (to_days('2009-01-01 00:00:00')),
-> partition p20090201 values less than (to_days('2009-02-01 00:00:00')),
-> partition p20090301 values less than (to_days('2009-03-01 00:00:00')),
-> partition p_last values less than maxvalue
-> );
Query OK, 0 rows affected (0.21 sec)
mysql> explain partitions select * from ptest_ok where created_at < '2009-01-15 12:34:56'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ptest_ok
partitions: p20090101,p20090201
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)
***** bad case
mysql> create table ptest_ng_simple (
-> id int auto_increment,
-> created_at datetime,
-> data varchar(100),
-> primary key (id, created_at)
-> )
-> partition by range (to_days(created_at) + 1) ( <===== *here*
-> partition p20090101 values less than (to_days('2009-01-01 00:00:00')),
-> partition p20090201 values less than (to_days('2009-02-01 00:00:00')),
-> partition p20090301 values less than (to_days('2009-03-01 00:00:00')),
-> partition p_last values less than maxvalue
-> );
Query OK, 0 rows affected (0.25 sec)
mysql> explain partitions select * from ptest_ng_simple where created_at < '2009-01-15 12:34:56'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ptest_ng_simple
partitions: p20090101,p20090201,p20090301,p_last <====== *here*
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)
Suggested fix:
Actually, I want such a following partitioned table.
(and mysqld can't optimize it.)
create table ptest_ng (
id int auto_increment,
created_at datetime,
data varchar(100),
primary key (id, created_at)
)
partition by range ((to_days(created_at) - 730000) * 86400 + time_to_sec(created_at)) (
partition p200901010600 values less than
((to_days('2009-01-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-01-01 06:00:00')),
partition p200902010600 values less than
((to_days('2009-02-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-02-01 06:00:00')),
partition p200903010600 values less than
((to_days('2009-03-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-03-01 06:00:00')),
partition p_last values less than maxvalue
);
So suggested fix is one of:
- support unix_timestamp() for partitioning function.
- support pruning partition even if partitioning function has some calcuration.
Description: Partition Pruning doesn't work if partitioning function has some calculation. How to repeat: ***** good case mysql> create table ptest_ok ( -> id int auto_increment, -> created_at datetime, -> data varchar(100), -> primary key (id, created_at) -> ) -> partition by range (to_days(created_at)) ( -> partition p20090101 values less than (to_days('2009-01-01 00:00:00')), -> partition p20090201 values less than (to_days('2009-02-01 00:00:00')), -> partition p20090301 values less than (to_days('2009-03-01 00:00:00')), -> partition p_last values less than maxvalue -> ); Query OK, 0 rows affected (0.21 sec) mysql> explain partitions select * from ptest_ok where created_at < '2009-01-15 12:34:56'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ptest_ok partitions: p20090101,p20090201 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where 1 row in set (0.00 sec) ***** bad case mysql> create table ptest_ng_simple ( -> id int auto_increment, -> created_at datetime, -> data varchar(100), -> primary key (id, created_at) -> ) -> partition by range (to_days(created_at) + 1) ( <===== *here* -> partition p20090101 values less than (to_days('2009-01-01 00:00:00')), -> partition p20090201 values less than (to_days('2009-02-01 00:00:00')), -> partition p20090301 values less than (to_days('2009-03-01 00:00:00')), -> partition p_last values less than maxvalue -> ); Query OK, 0 rows affected (0.25 sec) mysql> explain partitions select * from ptest_ng_simple where created_at < '2009-01-15 12:34:56'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ptest_ng_simple partitions: p20090101,p20090201,p20090301,p_last <====== *here* type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where 1 row in set (0.00 sec) Suggested fix: Actually, I want such a following partitioned table. (and mysqld can't optimize it.) create table ptest_ng ( id int auto_increment, created_at datetime, data varchar(100), primary key (id, created_at) ) partition by range ((to_days(created_at) - 730000) * 86400 + time_to_sec(created_at)) ( partition p200901010600 values less than ((to_days('2009-01-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-01-01 06:00:00')), partition p200902010600 values less than ((to_days('2009-02-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-02-01 06:00:00')), partition p200903010600 values less than ((to_days('2009-03-01 06:00:00') - 730000) * 86400 + time_to_sec('2009-03-01 06:00:00')), partition p_last values less than maxvalue ); So suggested fix is one of: - support unix_timestamp() for partitioning function. - support pruning partition even if partitioning function has some calcuration.