Subpartition Pruning
Hi all,
Is there any way to make sure pruning on HASH sub partition?
CREATE TABLE `table` (
-------------------
-------------------
-------------------
-------------------
) PARTITION BY RANGE (TO_DAYS(time_stamp))
SUBPARTITION BY HASH( HOUR(time_stamp) )
SUBPARTITIONS 8 (
PARTITION p1 VALUES LESS THAN (to_days('2009-10-01')),
PARTITION p2 VALUES LESS THAN (to_days('2009-10-02')),
PARTITION p3 VALUES LESS THAN (to_days('2009-10-03')),
PARTITION p4 VALUES LESS THAN (to_days('2009-10-04')),
PARTITION p5 VALUES LESS THAN (to_days('2009-10-05')),
PARTITION p6 VALUES LESS THAN (to_days('2009-10-06')),
PARTITION p7 VALUES LESS THAN (to_days('2009-10-07')),
PARTITION p8 VALUES LESS THAN (to_days('2009-10-08')),
PARTITION p9 VALUES LESS THAN (to_days('2009-10-09')),
PARTITION p10 VALUES LESS THAN (to_days('2009-10-10')),
PARTITION p11 VALUES LESS THAN (to_days('2009-10-11')),
PARTITION p12 VALUES LESS THAN (to_days('2009-10-12')),
PARTITION p13 VALUES LESS THAN (to_days('2009-10-13')),
PARTITION p14 VALUES LESS THAN (to_days('2009-10-14')),
PARTITION p15 VALUES LESS THAN (to_days('2009-10-15')),
PARTITION p16 VALUES LESS THAN (to_days('2009-10-16')),
PARTITION p17 VALUES LESS THAN (to_days('2009-10-17')),
PARTITION p18 VALUES LESS THAN (to_days('2009-10-18')),
PARTITION p19 VALUES LESS THAN (to_days('2009-10-19')),
PARTITION p20 VALUES LESS THAN (to_days('2009-10-20')),
PARTITION p21 VALUES LESS THAN (to_days('2009-10-21')),
PARTITION p22 VALUES LESS THAN (to_days('2009-10-22')),
PARTITION M VALUES LESS THAN MAXVALUE );
explain partitions * FROM cdrs_hash WHERE time_stamp between '09-10-14 15:00:00' AND '09-10-14 17:14:00' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: table
partitions: p15_p15sp0,p15_p15sp1,p15_p15sp2,p15_p15sp3,p15_p15sp4,p15_p15sp5,p15_p15sp6,p15_p15sp7
type: range
possible_keys: time_event_key,ori_key
key: ori_key
key_len: 23
ref: NULL
rows: 8
Extra: Using where
1 row in set (0.00 sec)
How to make sure it goes only to sub partitions 0,1 and 7?
thanks,
Mathuvathanan Mou.