mysql按时间字段进行分区 ，后跨分区查询问题请教

[zxwbb]

MYSQL版本:MySQL5.1.7-noinstall-beta(win32)
--建表语句
DROP TABLE IF EXISTS MD_TEST;
CREATE TABLE MD_TEST
(
STAT_TIME DATETIME NOT NULL,
VALUE INT UNSIGNED NOT NULL
)
PARTITION BY RANGE (DAYOFYEAR(STAT_TIME)) (
PARTITION p1 VALUES LESS THAN (1),
PARTITION p2 VALUES LESS THAN (2),
PARTITION p3 VALUES LESS THAN MAXVALUE);
--插入测试数据库
INSERT INTO `md_test` (`STAT_TIME`, `VALUE`) VALUES
('2007-01-01',100),
('2007-01-02',200),
('2007-01-21',120);
--跨分区查询
SELECT STAT_TIME,SUM(VALUE) FROM md_test
WHERE STAT_TIME>=DATE'2007-01-01' AND STAT_TIME<=DATE'2007-01-03'
GROUP BY STAT_TIME
结果
STAT_TIME SUM(VALUE)
2007-1-1 0:00 100
查询结果与期望不符合
是我的建表还是查询语句有问题，百思不得其解，望高手不吝赐教！万分感谢。

[zxwbb]

我将MYSQL版本改为:MySQL5.1.14-noinstall-beta(win32)，问题解决。
可能是 MySQL5.1.7在这种情况下查询有问题。以后大家遇到类似问题升级MYSQL版本即可：）

[zxwbb]

这种情况如何结贴？请高手指教！

[hy2003fly]

等有人进来顶了,你就可以结贴了.

[懒得去死]

那就接点分吧。

[zxwbb]

各位大侠指教后一定结分了：）

[zxwbb]

按时间分区优化问题
--方案1
DROP TABLE IF EXISTS TEST_DATA_SPEED;
CREATE TABLE TEST_DATA_SPEED
(
STAT_TIME DATETIME NOT NULL,
VALUE INT NOT NULL DEFAULT 0
)
PARTITION BY HASH(TO_DAYS(STAT_TIME))
PARTITIONS 100;

EXPLAIN PARTITIONS
SELECT STAT_TIME,COUNT(*) FROM TEST_DATA_SPEED WHERE
((STAT_TIME>=ADDDATE('2007-01-27 10:00:00',-1) AND STAT_TIME<=STR_TO_DATE('2007-01-27 10:00:00','%Y-%m-%d %H:%i:%S')))
GROUP BY STAT_TIME
结果不能在指定的分区上查询，是全表检索。

--方案2
DROP TABLE IF EXISTS TEST_DATA_SPEED2;
CREATE TABLE TEST_DATA_SPEED2
(
STAT_TIME DATETIME NOT NULL,
VALUE INT NOT NULL DEFAULT 0
)
PARTITION BY RANGE (TO_DAYS(STAT_TIME)) (
PARTITION p1 VALUES LESS THAN (to_days('2007-01-20')),
PARTITION p2 VALUES LESS THAN (to_days('2007-01-21')),
PARTITION p3 VALUES LESS THAN (to_days('2007-01-22')),
PARTITION p4 VALUES LESS THAN (to_days('2007-01-23')),
PARTITION p5 VALUES LESS THAN (to_days('2007-01-24')),
PARTITION p6 VALUES LESS THAN (to_days('2007-01-25')),
PARTITION p7 VALUES LESS THAN (to_days('2007-01-26')),
PARTITION p8 VALUES LESS THAN (to_days('2007-01-27')),
PARTITION p9 VALUES LESS THAN (to_days('2007-01-28')),
PARTITION p10 VALUES LESS THAN (to_days('2007-01-29')),
PARTITION p11 VALUES LESS THAN (to_days('2007-01-30')),
PARTITION p12 VALUES LESS THAN MAXVALUE );

EXPLAIN PARTITIONS
SELECT STAT_TIME,COUNT(*) FROM TEST_DATA_SPEED2 WHERE
((STAT_TIME>=ADDDATE('2007-01-27 10:00:00',-1) AND STAT_TIME<=STR_TO_DATE('2007-01-27 10:00:00','%Y-%m-%d %H:%i:%S')))
GROUP BY STAT_TIME
结果可在指定的分区上(p8,p9)查询。可是该方案不能循环利用分区。

是否有即可循环利用分区，又可跨分区查询指定分区的分区实现方法，请版主、高手们指教！

[zxwbb]

请各位版主、大侠们不吝赐教呀。该方案急需确定急盼明示！