Sure do. Also, I set up partitioning using both to_days() and year() with the same results. Here's the data:
MySQL Version 5.1.37 (latest XAMPP)
Update: Found this documentation
http://dev.mysql.com/doc/refman/5.1/en/partitioning-info.html saying the explain statement used to report the correct number of rows but since v5.1.28 it shows total rows. My regular EXPLAIN SELECT statements still show full table scans
Table:
CREATE TABLE `z1` (
`inv_date` datetime NOT NULL,
`value` double(4,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(inv_date))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (732677) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (733042) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (733407) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (733773) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
Data:
insert into z1 (inv_date, value) values ('2009-01-01',1);
insert into z1 (inv_date, value) values ('2008-01-01',2);
insert into z1 (inv_date, value) values ('2007-01-01',3);
insert into z1 (inv_date, value) values ('2006-01-01',4);
insert into z1 (inv_date, value) values ('2005-01-01',5);
Partitioning Using to_days():
alter table z1
partition by range (to_days(inv_date))
(
partition p0 values less than (0),
partition p1 values less than (to_days('2006-01-01')),
partition p2 values less than (to_days('2007-01-01')),
partition p3 values less than (to_days('2008-01-01')),
partition p4 values less than (to_days('2009-01-01')),
partition p5 values less than (MAXVALUE)
)
Partitioning Using year():
alter table z1
partition by range (YEAR(inv_date))
(
partition p0 values less than (0),
partition p1 values less than (YEAR('2006-01-01')),
partition p2 values less than (YEAR('2007-01-01')),
partition p3 values less than (YEAR('2008-01-01')),
partition p4 values less than (YEAR('2009-01-01')),
partition p5 values less than (MAXVALUE)
)
Core Select Statement:
explain partitions
select value
from z1
where inv_date between '2005-01-01' and '2006-01-01';
Explain Partition Using to_days() and year():
1 SIMPLE z1 p1,p2 ALL 6 Using where
*Note: There are only 5 records in the table. The phantom record is created after I run the alter table statement. Also the column used in the partitioning keeps the data in the first row but zeros out the other 4 records.
Can anyone else reproduce this?
Thanks,
Jason
Edited 3 time(s). Last edit at 11/05/2009 02:48PM by Jason Shoulet.