Bug #75085 Partition pruning on key partioning with ENUM
Submitted: 3 Dec 2014 9:19 Modified: 8 Aug 2022 8:24
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.6.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: enum, partitioning

[3 Dec 2014 9:19] Daniël van Eeden
Description:
When partitioning on KEY(enum_col) no pruning is done (e.g. all partitions are scanned).

As key seems to be the only option for partitioning on enum (Bug #60288 and Bug #39548) for some unknown reason handing key partitioning a bit better would be very welcome.

How to repeat:
mysql> create table t1 (id char(40) not null, revision char(10) not null, `type` enum('one','two','three') not null, val varchar(100), primary key(id,revision,type)) partition by key(`type`) partitions 4;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id char(40) not null, revision char(10) not null, `type` enum('one','two','three') not null, val varchar(100), primary key(id,revision,type)) partition by key(`revision`) partitions 4;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t3 (id char(40) not null, revision char(10) not null, `type` enum('one','two','three') not null, val varchar(100), primary key(id,revision,type)) partition by key(`id`) partitions 4;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values('x','y','one','xyz');                                                                                                
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values('x','y','one','xyz');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values('x','y','one','xyz');
Query OK, 1 row affected (0.00 sec)

mysql> explain partitions select val from t1 where id='x' and revision='y' and type='one';
+----+-------------+-------+-------------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | partitions  | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+-------+-------------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | t1    | p0,p1,p2,p3 | const | PRIMARY       | PRIMARY | 51      | const,const,const |    1 | NULL  |
+----+-------------+-------+-------------+-------+---------------+---------+---------+-------------------+------+-------+
1 row in set (0.00 sec)

mysql> explain partitions select val from t2 where id='x' and revision='y' and type='one';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | t2    | p0         | const | PRIMARY       | PRIMARY | 51      | const,const,const |    1 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
1 row in set (0.01 sec)

mysql> explain partitions select val from t3 where id='x' and revision='y' and type='one';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | t3    | p1         | const | PRIMARY       | PRIMARY | 51      | const,const,const |    1 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
1 row in set (0.00 sec)

Suggested fix:
1. Make sure pruning works for key partitioning on enum
2. Update documentation to explain partitioning options and limitations for enum.
[3 Dec 2014 9:30] Daniël van Eeden
There is no information about partition selection in both in json explain and the optimizer trace other than the names of the used partitions in the json explain.

It looks like fields_ok_for_partition_index() in sql/opt_range.cc blacklists ENUMs for some unknown reason.
[3 Dec 2014 14:48] Daniël van Eeden
Patching fields_ok_for_partition_index helps:

mysql> select * from t1;
+----+----------+-------+------+
| id | revision | type  | val  |
+----+----------+-------+------+
| x  | y        | one   | xyz  |
| x  | y        | three | xyz  |
| x  | y        | two   | xyz  |
+----+----------+-------+------+
3 rows in set (0.00 sec)

mysql> explain partitions select * from t1 where type='one';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from t1 where type='two';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | p3         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from t1 where type='three';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | p2         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` char(40) NOT NULL,
  `revision` char(10) NOT NULL,
  `type` enum('one','two','three') NOT NULL,
  `val` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`,`revision`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (`type`)
PARTITIONS 4 */
1 row in set (0.00 sec)

mysql>
[3 Dec 2014 14:49] Daniël van Eeden
Patch to allow partition pruning with ENUM

Attachment: 0001-Allow-partition-pruning-for-ENUM.patch (text/x-diff), 749 bytes.

[3 Dec 2014 17:37] MySQL Verification Team
This is a fully valid feature request, and hence, it is verified.
[3 Dec 2014 21:47] Daniël van Eeden
From https://github.com/mysql/mysql-server/blob/5.6/sql/opt_range.cc#L4048

"It is not known if we could process ENUM fields, so they are disabled to be
on the safe side."

So it seems nobody knows exactly why ENUM fields are disabled for partition pruning.
[9 Nov 2018 9:57] Federico Razzoli
4 years passed. Is there a chance for this patch to be approved?
[8 Aug 2022 8:24] Daniël van Eeden
Updated link:

https://github.com/mysql/mysql-server/blob/fbdaa4def30d269bc4de5b85de61de34b11c0afc/sql/ra...