Range Partition - behavior not expected
My table:
CREATE TABLE part_table (
unit_number INTEGER UNSIGNED NOT NULL,
date_field DATETIME NOT NULL,
latitude FLOAT NOT NULL,
longitude FLOAT NOT NULL,
description VARCHAR(50) NOT NULL,
PRIMARY KEY (unit_number, date_field)
)ENGINE = MYISAM
PARTITION BY RANGE (month(date_field)) (
PARTITION p0 VALUES LESS THAN (2),
PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN (4),
PARTITION p3 VALUES LESS THAN (5),
PARTITION p4 VALUES LESS THAN (6),
PARTITION p5 VALUES LESS THAN (7),
PARTITION p6 VALUES LESS THAN (8),
PARTITION p7 VALUES LESS THAN (9),
PARTITION p8 VALUES LESS THAN (10),
PARTITION p9 VALUES LESS THAN (11),
PARTITION p10 VALUES LESS THAN (12),
PARTITION p11 VALUES LESS THAN MAXVALUE,
);
I filled it with 20 million entries.
When I run:
EXPLAIN SELECT count(*)
FROM part_table
WHERE date_field BETWEEN '2009-01-15' AND '2009-03-10'
the result shows that all 20 million records will be scanned. Shouldn't it only be scanning the partitions where the data could logically exist (partitions 0, 1 and 2)?