Description:
Our fine manual says (http://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html):
"This type of optimization can be applied whenever the partitioning expression consists of an equality or a range which can be reduced to a set of equalities, or when the partitioning expression represents an increasing or decreasing relationship. Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. In addition, in MySQL 5.5, pruning can be applied for such tables when the partitioning expression uses the TO_SECONDS() function."
One can conclude from the above that pruning will not happen when table is partitioned on DATE column if any other function is used in partitioning expression. This is not true, in simple case pruning works for tables partitioned by MONTH(date_column). See "How to repeat".
How to repeat:
mysql> create table tp(c1 int, c2 date, c3 int, primary key(c1, c2))
-> partition by range(month(c2)) (
-> partition p0 values less than (2),
-> partition p1 values less than (3),
-> partition p2 values less than (4),
-> partition p3 values less than (5),
-> partition p4 values less than (6),
-> partition p5 values less than (7),
-> partition p6 values less than (8),
-> partition p7 values less than (9),
-> partition p8 values less than (10),
-> partition p9 values less than (11),
-> partition p10 values less than (12),
-> partition p11 values less than (13));
Query OK, 0 rows affected (2.67 sec)
mysql> insert into tp values
-> (1, '2012-01-01', 1),
-> (2, '2012-02-01', 2),
-> (3, '2012-03-01', 3),
-> (4, '2012-04-01', 4),
-> (5, '2012-05-01', 5),
-> (6, '2012-06-01', 6),
-> (7, '2012-07-01', 7),
-> (8, '2012-08-01', 8),
-> (9, '2012-09-01', 9),
-> (10, '2012-10-01', 10),
-> (11, '2012-11-01', 11),
-> (12, '2012-12-01', 12);
Query OK, 12 rows affected (0.26 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> explain partitions select count(*) from tp where c2 = '2012-09-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tp
partitions: p8
type: index
possible_keys: NULL
key: PRIMARY
key_len: 7
ref: NULL
rows: 2
Extra: Using where; Using index
1 row in set (0.07 sec)
mysql> explain partitions select count(*) from tp where c2 = curdate()\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tp
partitions: p9
type: index
possible_keys: NULL
key: PRIMARY
key_len: 7
ref: NULL
rows: 2
Extra: Using where; Using index
1 row in set (0.03 sec)
As you can see, pruning happens in both cases above. It does not happen for ranges even of 1 row and just 10 days (with 12 partitions) no matter what we do:
mysql> explain partitions select count(*) from tp where c2 > curdate()\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tp
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
type: index
possible_keys: NULL
key: PRIMARY
key_len: 7
ref: NULL
rows: 12
Extra: Using where; Using index
1 row in set (0.02 sec)
mysql> explain partitions select count(*) from tp where c2 > curdate() and c2 <=
'2012-11-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tp
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
type: index
possible_keys: NULL
key: PRIMARY
key_len: 7
ref: NULL
rows: 12
Extra: Using where; Using index
1 row in set (0.02 sec)
mysql> select count(*) from tp where c2 > curdate() and c2 <='2012-11-01'\G
*************************** 1. row ***************************
count(*): 1
1 row in set (0.05 sec)
mysql> analyze table pt;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.pt | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.09 sec)
mysql> explain partitions select count(*) from tp where c2 > curdate() and c2 <=
'2012-11-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tp
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
type: index
possible_keys: NULL
key: PRIMARY
key_len: 7
ref: NULL
rows: 12
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.28 |
+-----------+
1 row in set (0.03 sec)
but that is a topic for another bug report probably :)
Suggested fix:
Fix the manual to include MONTH() into the list of supported functions for partition pruning.
