I searched table on one partiton, but MySQL scanning two partitons
| se_keywords_log | CREATE TABLE `se_keywords_log` (
`id` bigint(20) NOT NULL,
`domain` varchar(50) NOT NULL,
`encode` varchar(8) NOT NULL,
`seType` varchar(8) NOT NULL,
`serIP` varchar(50) NOT NULL,
`uriStem` varchar(256) NOT NULL,
`uriQuery` varchar(512) NOT NULL,
`Referer` varchar(1024) NOT NULL,
`wd` varchar(256) NOT NULL,
`pn` int(11) NOT NULL,
`member_id` varchar(10) NOT NULL,
`id_id` varchar(10) NOT NULL,
`datetime` datetime NOT NULL,
`isSag` tinyint(4) NOT NULL,
KEY `i_index` (`id`),
KEY `d_index` (`domain`),
KEY `st_index` (`seType`),
KEY `dt_index` (`datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(datetime))
(PARTITION pmin VALUES LESS THAN (734623) ENGINE = MyISAM,
PARTITION p201105 VALUES LESS THAN (734654) ENGINE = MyISAM,
PARTITION p201106 VALUES LESS THAN (734684) ENGINE = MyISAM,
PARTITION p201107 VALUES LESS THAN (734715) ENGINE = MyISAM,
PARTITION p201108 VALUES LESS THAN (734746) ENGINE = MyISAM,
PARTITION p201109 VALUES LESS THAN (734776) ENGINE = MyISAM,
PARTITION p201110 VALUES LESS THAN (734807) ENGINE = MyISAM,
PARTITION p201111 VALUES LESS THAN (734837) ENGINE = MyISAM,
PARTITION p201112 VALUES LESS THAN (734868) ENGINE = MyISAM,
PARTITION p201201 VALUES LESS THAN (734899) ENGINE = MyISAM,
PARTITION p201202 VALUES LESS THAN (734928) ENGINE = MyISAM,
PARTITION p201203 VALUES LESS THAN (734959) ENGINE = MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ |
After, I insert into the table 10,000,000 record,and the datetime from 2011-05 to 2012-03.
After, I make query with EXPLAIN ()
mysql> explain partitions select * from se_keywords_log where datetime between '2011-05-01' and '2011-05-28';
or explain partitions select * from se_keywords_log where datetime > '2011-05-01' and datetime < '2011-05-28'
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | se_keywords_log | pmin,p201105 | ALL | dt_index | NULL | NULL | NULL | 4968806 | Using where |
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
rezultat from "partitons" is "pmin,p201105";why?
Please answer me if my question is clearly, this is very Important for me.