Bug #47594 | a query scans some unnessassary partion | ||
---|---|---|---|
Submitted: | 24 Sep 2009 10:00 | Modified: | 2 Oct 2009 3:25 |
Reporter: | Jun Chen | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
Version: | 5.1.33 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | partition scan, unnessassary |
[24 Sep 2009 10:00]
Jun Chen
[24 Sep 2009 10:07]
Valeriy Kravchuk
I can't repeat this with a newer version, 5.1.39: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.39-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `snapstock` ( -> `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `STOCK_ID` smallint(10) unsigned NOT NULL, -> `YEAR` year(2) NOT NULL, -> PRIMARY KEY (`YEAR`,`ID`) -> ) ENGINE=MyISAM AUTO_INCREMENT=237 DEFAULT CHARSET=latin1 CHECKSUM=1 DELA Y_KEY_WRITE=1 -> ROW_FORMAT=FIXED -> /*!50100 PARTITION BY RANGE (`YEAR`) -> (PARTITION p09 VALUES LESS THAN (2010) ENGINE = MyISAM, -> PARTITION p10 VALUES LESS THAN (2011) ENGINE = MyISAM, -> PARTITION p11 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */; Query OK, 0 rows affected (0.14 sec) mysql> explain partitions -> select * from snapstock where `year`=2010; +----+-------------+-------+------------+------+---------------+------+--------- +------+------+-----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+------+--------- +------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------------+------+---------------+------+--------- +------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into snapstock(year,stock_id) values(2010,1); Query OK, 1 row affected (0.01 sec) mysql> insert into snapstock(year,stock_id) values(2011,1); Query OK, 1 row affected (0.00 sec) mysql> insert into snapstock(year,stock_id) values(2009,2); Query OK, 1 row affected (0.00 sec) mysql> explain partitions -> select * from snapstock where `year`=2010; +----+-------------+-----------+------------+------+---------------+---------+-- -------+-------+------+-------+ | id | select_type | table | partitions | type | possible_keys | key | k ey_len | ref | rows | Extra | +----+-------------+-----------+------------+------+---------------+---------+-- -------+-------+------+-------+ | 1 | SIMPLE | snapstock | p09 | ref | PRIMARY | PRIMARY | 1 | const | 1 | | +----+-------------+-----------+------------+------+---------------+---------+-- -------+-------+------+-------+ 1 row in set (0.00 sec) Please, check.
[25 Sep 2009 8:59]
Jun Chen
sorry, i missed one thing which i think is another bug: if the the data-field was defined as : year(2) , when you insert a record like '2011,1', it will be stored into p09 because the data base recognize it as '11' which was less than 2010. in order to see which partitions mysql have scaned, let us change year(2) to year(4) when create table.
[25 Sep 2009 9:22]
Valeriy Kravchuk
OK, still only one partition is scanned: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql --comments -uroot -proot -P331 0 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.39-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table snapstock; Query OK, 0 rows affected (0.09 sec) mysql> CREATE TABLE `snapstock` ( -> `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `STOCK_ID` smallint(10) unsigned NOT NULL, -> `YEAR` year(4) NOT NULL, -> PRIMARY KEY (`YEAR`,`ID`) -> ) ENGINE=MyISAM AUTO_INCREMENT=237 DEFAULT CHARSET=latin1 CHECKSUM=1 DELA Y_KEY_WRITE=1 -> ROW_FORMAT=FIXED -> /*!50100 PARTITION BY RANGE (`YEAR`) -> (PARTITION p09 VALUES LESS THAN (2010) ENGINE = MyISAM, -> PARTITION p10 VALUES LESS THAN (2011) ENGINE = MyISAM, -> PARTITION p11 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */; Query OK, 0 rows affected (0.14 sec) mysql> insert into snapstock(year,stock_id) values(2010,1); Query OK, 1 row affected (0.06 sec) mysql> insert into snapstock(year,stock_id) values(2011,1); Query OK, 1 row affected (0.00 sec) mysql> insert into snapstock(year,stock_id) values(2009,2); Query OK, 1 row affected (0.00 sec) mysql> explain partitions -> select * from snapstock where `year`=2010\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: snapstock partitions: p10 type: system possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 1 Extra: 1 row in set (0.03 sec) I'd really suggest you to test 5.1.39.
[26 Sep 2009 10:58]
Jun Chen
Thank you ! maybe this only appear in 5.1.33; i've tryed in your version, it not happens again.
[28 Sep 2009 5:24]
Sveta Smirnova
Thank you for the feedback. Closed as "Can't repeat" because last comment.
[2 Oct 2009 3:25]
Jun Chen
no longger exists in 5.1.39