Description:
InnoDB has extended keys feature. However, it doesn't work for partitioned tables.
How to repeat:
## Fill the tables
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int);
insert into t2 select A.a + 10 *B.a + 100*C.a from t1 A, t1 B, t1 C;
create table t3 (pk1 int, pk2 int, a int, b int, key(a), primary key(pk1, pk2)) engine=innodb;
insert into t3 select a,a,a,a from t2;
create table t4 (pk1 int, pk2 int, a int, b int, key(a), primary key(pk1, pk2)) partition by key(pk1, pk2) partitions 10;
insert into t4 select * from t3;
## And then run
MySQL [test]> explain select * from t3 where a between 10 and 20 ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | t3 | range | a | a | 5 | NULL | 11 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
MySQL [test]> explain select * from t3 where a between 10 and 20 and pk1 >3 ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | t3 | range | PRIMARY,a | a | 9 | NULL | 11 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
Note that key_len=9 for the second query. This is extended keys feature at work.
However, for the partitioned table, we get:
MySQL [test]> explain select * from t4 where a between 10 and 20 ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t4 | range | a | a | 5 | NULL | 12 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MySQL [test]> explain select * from t4 where a between 10 and 20 and pk1 >3 ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t4 | range | PRIMARY,a | a | 5 | NULL | 12 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Suggested fix:
Add extended keys support to ha_partition.
