| 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: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
Description: a query scans more unnessassary partion. for example, i have these partition: PARTITION p09 VALUES LESS THAN (2010) ENGINE = MyISAM, PARTITION p10 VALUES LESS THAN (2011) ENGINE = MyISAM ...... when i query: explain partitions select * from snapstock where `year`=2010; i got this: ------------------------------------------------- 1 SIMPLE snapstock p09,p10 ref PRIMARY PRIMARY 1 const 19 ------------------------------------------------- which means it scaned the partition p09, but the data was only in partition p10. How to repeat: 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 DELAY_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) */; explain partitions select * from snapstock where `year`=2010;