MySQL Forums
Forum List  »  Partitioning

I searched table on one partiton, but MySQL scanning two partitons
Posted by: Yang Bingle
Date: March 03, 2012 04:01AM

| 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
I searched table on one partiton, but MySQL scanning two partitons
2569
March 03, 2012 04:01AM


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.