Bug #66187 | Wrong query plan for min/max over an entire partition | ||
---|---|---|---|
Submitted: | 3 Aug 2012 18:07 | Modified: | 4 Aug 2012 9:09 |
Reporter: | Justin Swanhart | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
Version: | 5.1.63, 5.5.25a, 5.5.28 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[3 Aug 2012 18:07]
Justin Swanhart
[3 Aug 2012 18:24]
Justin Swanhart
I guess I can understand this for hash partitions, as it depends on data distribution. In this case, there is a 1:1 mapping between partition_keys and the number of values in the partitioning. This table could be partitioned with LIST or RANGE partitioning instead: mysql> alter table t1 partition by list (kind_id) (partition p0 values in (1), partition p1 values in(2), partition p2 values in(3), partition p3 values in (4), partition p4 values in(5), partition p5 values in(6), partition p6 values in(7)); Query OK, 1543719 rows affected (53.22 sec) Records: 1543719 Duplicates: 0 Warnings: 0 -- there can only be kind_id=1 in p0 so we shouldn't need to do an FTS mysql> explain partitions select max(id) from t1 where kind_id = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p0 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 478041 Extra: Using where 1 row in set (0.00 sec) mysql> alter table t1 add key (kind_id,id); Query OK, 0 rows affected (18.45 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain partitions select max(id) from t1 where kind_id = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Select tables optimized away 1 row in set (0.00 sec)
[4 Aug 2012 9:09]
Valeriy Kravchuk
Thank you for the problem report. Same problem confirmed with PARTITION BY LIST on current mysql-5.5.
[2 Feb 2013 9:48]
Michael Gorelik
The same is also true for range partitioning on columns for 5.5.25
[30 May 2013 8:44]
Jervin R
This looks to have been solved on 5.5.30, on my table below t_id is actually unique copied from h_id which was PRIMARY KEY. mysql [localhost] {msandbox} (test) > show create table history \G *************************** 1. row *************************** Table: history Create Table: CREATE TABLE `history` ( `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `h_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `t_id` bigint(20) unsigned NOT NULL, KEY `h_id` (`h_id`), KEY `t_id` (`t_id`) ) ENGINE=InnoDB AUTO_INCREMENT=22896060 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (t_id) PARTITIONS 8 */ mysql (test) > EXPLAIN PARTITIONS SELECT MAX(h_date) FROM history WHERE t_id = 10947385 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: history partitions: p1 type: ref possible_keys: t_id key: t_id key_len: 8 ref: const rows: 1 Extra: 1 row in set (0.00 sec)
[30 May 2013 9:07]
Jervin R
Scratch that last comment, I had a key on t_id which is not supposed to be there and the point of this bug report. If I remove the key on t_id, it does a full table scan instead: mysql (test) > EXPLAIN PARTITIONS SELECT MAX(h_date) FROM history WHERE t_id = 10947385 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: history partitions: p1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1974747 Extra: Using where 1 row in set (0.00 sec)