Description:
Using a range partition like the one described in a recent article ( http://dev.mysql.com/tech-resources/articles/performance-partitioning.html ), queries on specific ranges (e.g. date_column between x and y) succeed while queries asking for "less than" x fail.
e.g.
select * from test_partition;
+------+--------------------+------------+
| c1 | c2 | c3 |
+------+--------------------+------------+
| 1 | testing partitions | 1995-07-17 |
| 3 | testing partitions | 1995-07-31 |
| 5 | testing partitions | 1995-08-13 |
| 7 | testing partitions | 1995-08-26 |
| 9 | testing partitions | 1995-09-09 |
| 0 | testing partitions | 2000-07-10 |
| 2 | testing partitions | 2000-07-23 |
| 4 | testing partitions | 2000-08-05 |
| 6 | testing partitions | 2000-08-19 |
| 8 | testing partitions | 2000-09-01 |
+------+--------------------+------------+
select count(*) from test_partition where c3 between '1996-12-31' and '2000-12-31';
+----------+
| count(*) |
+----------+
| 5 |
+----------+
select count(*) from test_partition where c3 < '2000-12-31';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
# test_myisam has the same records as test_partition (see below)
select count(*) from test_myisam where c3 < '2000-12-31';
+----------+
| count(*) |
+----------+
| 10 |
+----------+
The same behavior is noted using different engines.
How to repeat:
create database if not exists test;
use test ;
DROP TABLE IF EXISTS `test_myisam`;
CREATE TABLE `test_myisam` (
`c1` int(11) default NULL,
`c2` varchar(30) default NULL,
`c3` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `test_partition`;
CREATE TABLE `test_partition` (
`c1` int(11) default NULL,
`c2` varchar(30) default NULL,
`c3` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (1997),
PARTITION p3 VALUES LESS THAN (1998),
PARTITION p4 VALUES LESS THAN (1999),
PARTITION p5 VALUES LESS THAN (2000),
PARTITION p6 VALUES LESS THAN (2001),
PARTITION p7 VALUES LESS THAN (2002),
PARTITION p8 VALUES LESS THAN (2003),
PARTITION p9 VALUES LESS THAN (2004),
PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE
);
INSERT INTO `test_partition` VALUES
(0,'testing partitions','2000-07-10'),
(1,'testing partitions','1995-07-17'),
(2,'testing partitions','2000-07-23'),
(3,'testing partitions','1995-07-31'),
(4,'testing partitions','2000-08-05'),
(5,'testing partitions','1995-08-13'),
(6,'testing partitions','2000-08-19'),
(7,'testing partitions','1995-08-26'),
(8,'testing partitions','2000-09-01'),
(9,'testing partitions','1995-09-09');
INSERT INTO test_myisam select * from test_partition;
select count(*) from test_myisam where c3 > '1994-01-01' and c3 < '2000-12-31';
select count(*) from test_myisam where c3 < '2000-12-31';
select count(*) from test_partition where c3 > '1994-01-01' and c3 < '2000-12-31';
select count(*) from test_partition where c3 < '2000-12-31';
