mysql> alter table t1 partition by hash (to_days(localDateTime)) partitions 6;Query OK, 22 rows affected (0.12 sec)
Records: 22 Duplicates: 0 Warnings: 0
I believe this explains it a little:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html
"...Pruning can be used only on integer columns of tables partitioned by HASH or KEY. ... However, if the table stores year values in an INT column, then a query having WHERE year_col >= 2001 AND year_col <= 2005 can be pruned. ..."
In otherwords, it won't prune between in a hash partition unless it's an integer (it appears to be the same in 6.0). I think this will probably change at some point... (seems like we could evaluate the hash and apply pruning...)
The other issue you have is using a timestamp instead of datetime (need the datetime to prune with between as well)
Example:
mysql> explain partitions select localDateTime from t1 where localDateTime between '2008-05-22 00:00:00' and '2008-05-23 04:00:00' ;
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | p0,p1,p2,p3,p4,p5 | index | NULL | PRIMARY | 12 | NULL | 22 | Using where; Using index |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Modify to datetime, with hash it still scans all partitions because it's not 'really' an int:
mysql> alter table t1 modify column localdatetime datetime;
Query OK, 22 rows affected (0.07 sec)
Records: 22 Duplicates: 0 Warnings: 0
mysql> explain partitions select localDateTime from t1 where localDateTime between '2008-05-22 00:00:00' and '2008-05-23 04:00:00' ;
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | p0,p1,p2,p3,p4,p5 | index | NULL | PRIMARY | 12 | NULL | 22 | Using where; Using index |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Modify to a range partition, and then the between works:
mysql> alter table t1 PARTITION BY range (to_days(localDateTime))
( partition p1 values less than (to_days('2008-05-25')),
partition p2 values less than (to_days('2008-05-27')),
partition p3 values less than (to_days('2008-05-29')),
partition p4 values less than (to_days('2008-05-31')),
partition p5 values less than maxvalue );
Query OK, 22 rows affected (0.09 sec)
Records: 22 Duplicates: 0 Warnings: 0
mysql> explain partitions select localDateTime from t1 where localDateTime between '2008-05-22 00:00:00' and '2008-05-23 04:00:00' ;+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | p1 | index | NULL | PRIMARY | 12 | NULL | 7 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Modify to timestamp, however, and we're back to scanning all partitions:
mysql> alter table t1 modify column localdatetime timestamp;Query OK, 22 rows affected (0.09 sec)
Records: 22 Duplicates: 0 Warnings: 0
mysql> explain partitions select localDateTime from t1 where localDateTime between '2008-05-22 00:00:00' and '2008-05-23 04:00:00' ;
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | p1,p2,p3,p4,p5 | index | NULL | PRIMARY | 8 | NULL | 23 | Using where; Using index |
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
I tested this with 5.1.24 and 6.0, so the same limitations exist there with respect to hash partitions vs range partitions and between pruning. I think it's a reasonable feature request, however... seems we should be able to evaluate the hash of to_days(some date) quickly in the optimizer for purposes of partition pruning. Maybe it's a work item in forge ?