Pruning problem.
Hi to all.
I have created this table:
CREATE TABLE test (
id int(10) unsigned NOT NULL auto_increment,
date_ril date NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id, date_ril)
) ENGINE=InnoDB AUTO_INCREMENT=124408 DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(date_ril) )
SUBPARTITION BY HASH( TO_DAYS(date_ril) )
SUBPARTITIONS 53 (
PARTITION y09 VALUES LESS THAN (2010),
PARTITION y10 VALUES LESS THAN (2011),
PARTITION y11 VALUES LESS THAN (2012),
PARTITION y VALUES LESS THAN MAXVALUE
);
When i try this query, all work correctly:
EXPLAIN PARTITIONS
SELECT * FROM test
WHERE date_ril = '2008-08-07';
RETURN
1, 'SIMPLE', 'test', 'y09_y09sp0', 'index', '', 'PRIMARY', '7', '', 212, 'Using where; Using index'
The data are take from the right subpartition
But when i try this query or similar (also with use of < and >):
EXPLAIN PARTITIONS
SELECT * FROM test
WHERE date_ril BETWEEN '2008-08-07' AND '2008-08-10';
RETURN:
1, 'SIMPLE', 'test', 'y09_y09sp0,y09_y09sp1,y09_y09sp2,y09_y09sp3,y09_y09sp4,y09_y09sp5,y09_y09sp6,y09_y09sp7,y09_y09sp8,y09_y09sp9,y09_y09sp10,y09_y09sp11,y09_y09sp12,y09_y09sp13,y09_y09sp14,y09_y09sp15,y09_y09sp16,y09_y09sp17,y09_y09sp18,y09_y09sp19,y09_y09sp20,y09_y09sp21,y09_y09sp22,y09_y09sp23,y09_y09sp24,y09_y09sp25,y09_y09sp26,y09_y09sp27,y09_y09sp28,y09_y09sp29,y09_y09sp30,y09_y09sp31,y09_y09sp32,y09_y09sp33,y09_y09sp34,y09_y09sp35,y09_y09sp36,y09_y09sp37,y09_y09sp38,y09_y09sp39,y09_y09sp40,y09_y09sp41,y09_y09sp42,y09_y09sp43,y09_y09sp44,y09_y09sp45,y09_y09sp46,y09_y09sp47,y09_y09sp48,y09_y09sp49,y09_y09sp50,y09_y09sp51,y09_y09sp52', 'index', '', 'PRIMARY', '7', '', 212, 'Using where; Using index'
The search involve all the 53 subpartition. I work on a mysql db version 5.1.31.
Actually I can't use partitions.
Thanks for yuor reply.
Vladimiro Puggelli.