| Bug #52287 | mysql should search only in partition p3,but it search all part | ||
|---|---|---|---|
| Submitted: | 23 Mar 2010 0:46 | Modified: | 23 Mar 2010 8:49 |
| Reporter: | zhang jiqing | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S5 (Performance) |
| Version: | GA 5.1.36 | OS: | Other (64bit centos4.6) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | partition | ||
[23 Mar 2010 0:46]
zhang jiqing
[23 Mar 2010 4:30]
Valeriy Kravchuk
In recent versions you can NOT partition by timestamp column this way, but if you change column data type to datetime only partition p3 is searched:
77-52-28-202:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.46-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `score_actions_2011` (
-> `account_id` varchar(18) NOT NULL,
-> `account_name` varchar(20) NOT NULL,
-> `action_score` int(11) NOT NULL,
-> `tax` int(11) NOT NULL DEFAULT '0',
-> `action_code` char(3) NOT NULL,
-> `action_desc` varchar(100) NOT NULL,
-> `action_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `account_type` char(1) NOT NULL,
-> `ass_account_id` varchar(18) NOT NULL,
-> KEY `account_name_3` (`account_name`,`action_date`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY RANGE (year(action_date))
-> (PARTITION p1 VALUES LESS THAN (1992) ENGINE = InnoDB,
-> PARTITION p2 VALUES LESS THAN (2003) ENGINE = InnoDB,
-> PARTITION p3 VALUES LESS THAN (2011) ENGINE = InnoDB,
-> PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql> CREATE TABLE `score_actions_2011` ( `account_id` varchar(18) NOT NULL, `account_name` varchar(20) NOT NULL, `action_score` int(11) NOT NULL, `tax` int(11) NOT NULL DEFAULT '0', `action_code` char(3) NOT NULL, `action_desc` varchar(100) NOT NULL, `action_date` datetime NOT NULL, `account_type` char(1) NOT NULL, `ass_account_id` varchar(18) NOT NULL, KEY `account_name_3` (`account_name`,`action_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (year(action_date)) (PARTITION p1 VALUES LESS THAN (1992) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (2003) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (2011) ENGINE = InnoDB, PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.22 sec)
mysql> explain partitions select * from score_actions_2011 where action_date between
-> '2010-03-01' and '2010-03-31';
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | score_actions_2011 | p3 | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
See also bug #42849.
[23 Mar 2010 7:58]
zhang jiqing
thank Valeriy Kravchuk I change column data type to datetime,it search only partition p,but i have new problem that if i partition the table month not year,it search all partition. repeat: | score_actions_2011 | CREATE TABLE `score_actions_2011` ( `account_id` varchar(18) NOT NULL, `account_name` varchar(20) NOT NULL, `action_score` int(11) NOT NULL, `tax` int(11) NOT NULL DEFAULT '0', `action_code` char(3) NOT NULL, `action_desc` varchar(100) NOT NULL, `action_date` datetime NOT NULL, `account_type` char(1) NOT NULL, `ass_account_id` varchar(18) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (month(action_date)) (PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (8) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (9) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB, PARTITION p11 VALUES LESS THAN (12) ENGINE = InnoDB, PARTITION p12 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ | mysql> explain partitions select * from score_actions_2011 where action_date between '2010-03-01' and '2010-03-31'; +----+-------------+--------------------+----------------------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+----------------------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | score_actions_2011 | p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 | ALL | NULL | NULL | NULL | NULL | 12 | Using where | +----+-------------+--------------------+----------------------------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
[23 Mar 2010 8:49]
Valeriy Kravchuk
Your initial problem is a duplicate of bug #47176 by the way. What you reported in the last comment is repeatable and know problem. We do not do pruning yet when MONTH() is used. Read http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html: "Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function." So this is not a bug formally. Sorry.
