MySQL Forums
Forum List  »  Partitioning

Pruning problem.
Posted by: Vladimiro Puggelli
Date: September 21, 2010 04:03AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Pruning problem.
2538
September 21, 2010 04:03AM
1422
September 22, 2010 07:57PM
1379
September 23, 2010 03:16AM
1375
September 23, 2010 08:40AM
1305
September 24, 2010 02:24AM
1511
September 28, 2010 10:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.