first partition always

[N N]

In my all tests the very first partition is always read no matter what filter I use on order_date. Can some one help me where the problem is?

CREATE TABLE `orders_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`order_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`,`order_date`)
) ENGINE=MyISAM AUTO_INCREMENT=1150001 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYs(order_date))
(PARTITION 2010_08_01 VALUES LESS THAN (734350) ENGINE = MyISAM,
PARTITION 2010_08_02 VALUES LESS THAN (734351) ENGINE = MyISAM,
PARTITION 2010_08_03 VALUES LESS THAN (734352) ENGINE = MyISAM,
PARTITION 2010_08_04 VALUES LESS THAN (734353) ENGINE = MyISAM,
PARTITION 2010_08_05 VALUES LESS THAN (734354) ENGINE = MyISAM,
PARTITION 2010_08_06 VALUES LESS THAN (734355) ENGINE = MyISAM,
PARTITION 2010_08_07 VALUES LESS THAN (734356) ENGINE = MyISAM,
PARTITION 2010_08_08 VALUES LESS THAN (734357) ENGINE = MyISAM,
PARTITION 2010_08_09 VALUES LESS THAN (734358) ENGINE = MyISAM,
PARTITION 2010_08_10 VALUES LESS THAN (734359) ENGINE = MyISAM,
PARTITION 2010_08_11 VALUES LESS THAN (734361) ENGINE = MyISAM) */

There are only two records in this table

mysql> select * from orders_test;
+----+------+---------------------+
| id | name | order_date |
+----+------+---------------------+
| 1 | test | 2010-08-10 10:49:43 |
| 2 | test | 2010-08-10 10:51:33 |
+----+------+---------------------+
2 rows in set (0.00 sec)

mysql> explain partitions SELECT * FROM orders_test WHERE order_date BETWEEN '2010-08-10 10:49:43' AND '2010-08-10 10:51:33';
+----+-------------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | orders_test | 2010_08_01,2010_08_11 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Why optimizer choose 2010_08_01 parition?



Edited 1 time(s). Last edit at 08/10/2010 03:53AM by N N.