version: 5.1.34
table definition:
CREATE TABLE `t_pgv_v1_map_url_id` (
`ftime` date NOT NULL,
`map_id` int(4) NOT NULL,
`map_name` varchar(256),
PRIMARY KEY (`ftime`,`map_id`)
) ENGINE=MyISAM
PARTITION BY RANGE (to_days(ftime))
(PARTITION p200903 VALUES LESS THAN (to_days('2009-04-01')) ,
PARTITION p200904 VALUES LESS THAN (to_days('2009-05-01')) ,
PARTITION p200905 VALUES LESS THAN (to_days('2009-06-01')) ,
PARTITION p200906 VALUES LESS THAN (to_days('2009-07-01')) ,
PARTITION p200907 VALUES LESS THAN (to_days('2009-08-01')) ,
PARTITION p200908 VALUES LESS THAN (to_days('2009-09-01')) ,
PARTITION p200909 VALUES LESS THAN (to_days('2009-10-01')) ,
PARTITION p200910 VALUES LESS THAN (to_days('2009-11-01'))
);
record format:
(2009-06-01,1,
http://www.google.com)
(2009-06-01,2,
http://www.cnn.org)
...
(2009-07-01,1,
http://www.google.com)
(2009-07-01,2,
http://www.cnn.org)
...
(2009-08-01,1,
http://www.google.com)
(2009-08-01,2,
http://www.cnn.org)
...
When using select statement with "in", MYSQL just scan 2 rows.
mysql> explain select * from t_pgv_v1_map_url_id where ftime in ('2009-08-01', '2009-09-01') and map_id = 1003200;
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t_pgv_v1_map_url_id | range | PRIMARY | PRIMARY | 7 | NULL | 2 | Using where |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
when using select statement with "between", MYSQL scan the whole table!
mysql> explain select * from t_pgv_v1_map_url_id where ftime between '2009-08-01' and '2009-09-01' and map_id = 1003200;;
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | t_pgv_v1_map_url_id | ALL | PRIMARY | NULL | NULL | NULL | 88269762 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+----------+-------------+
it's a bug?