| 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: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)
Description: I have a HASH partitioned table. There is an indexed column in the table. I want to retrieve the min/max value for the indexed column for a specific partition. For the query of the following form, I expect to read only one row (actually just one index entry) from one partition: select MIN|MAX(indexed_column) from partitioned_table where partition_key = CONSTANT In reality, a full table scan is made over the partition (example is below). How to repeat: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` text CHARACTER SET utf8 NOT NULL, `imdb_index` varchar(12) CHARACTER SET utf8 DEFAULT NULL, `kind_id` int(11) NOT NULL, `production_year` int(11) DEFAULT NULL, `imdb_id` int(11) DEFAULT NULL, `phonetic_code` varchar(5) CHARACTER SET utf8 DEFAULT NULL, `episode_of_id` int(11) DEFAULT NULL, `season_nr` int(11) DEFAULT NULL, `episode_nr` int(11) DEFAULT NULL, `series_years` varchar(49) CHARACTER SET utf8 DEFAULT NULL, `title_crc32` int(10) unsigned DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1543721 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (kind_id) PARTITIONS 7 */ explain select max(id) from t1 where kind_id = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 475122 Extra: Using where 1 row in set (0.00 sec) Notice the type: ALL I would expect a plan equivalent to the following plan: explain select id from t1 where kind_id = 1 order by id desc limit 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: NULL key: id key_len: 4 ref: NULL rows: 1 Extra: Using where 1 row in set (0.00 sec) Suggested fix: Fix MIN/MAX over an entire partition, so that it works like it does when examining an entire table. MIN/MAX over the entire partitioned table is fast, so it should be fast for entire partitions: -- max(id) over whole table does not ALL scan partitions explain select max(id) from t1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: 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)