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:
None 
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
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;
[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