Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this table definition:

CREATE TABLE ClientInfo (
    IPAddr VARCHAR(16),
    RepDate DATETIME NOT NULL
)ENGINE=MyISAM;

PARTITION BY RANGE(TO_DAYS(RepDate)) 
(PARTITION pNULL VALUES LESS THAN (0),
PARTITION p201201 VALUES LESS THAN (TO_DAYS('2012-02-01')),
PARTITION p201202 VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION p201203 VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION p201204 VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p201205 VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p201206 VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION p201207 VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION p201208 VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION p201209 VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION p201210 VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION p201211 VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION p201212 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION p201301 VALUES LESS THAN (TO_DAYS('2013-02-01')),
PARTITION pMAX VALUES LESS THAN MAXVALUE
);

When I am executing following query,

EXPLAIN PARTITIONS SELECT COUNT(*) FROM ClientInfo
WHERE RepDate > '2012-01-01 00:00:00' AND RepDate < '2012-02-01 00:00:00';

Partition pruning is selecting partition pNULL, p201201 for searching whereas, it is clear from partition schema that it should select p201201 only. Also selection of a date directly without range does not select the pNULL partition, but it selects the exact partition.

Can anyone point out the reason behind it? What should I do to avoid this? Is it related to how partitioning handles NULL values?

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.