MySQL Forums
Forum List  »  Partitioning

first partition always
Posted by: N N
Date: August 10, 2010 03:29AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
first partition always
2650
N N
August 10, 2010 03:29AM
1431
August 11, 2010 05:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.