Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3, 10.4, 10.5, 10.6
-
None
-
None
Description
create table t1 ( |
id int auto_increment, |
created_at datetime,
|
key1 int, |
key2 int, |
key (key1), |
primary key(id,created_at) |
) partition by range columns(created_at) ( |
PARTITION `p1` VALUES LESS THAN ('2010-01-01 00:00:00') ENGINE = InnoDB, |
PARTITION `p2` VALUES LESS THAN ('2015-01-01 00:00:00') ENGINE = InnoDB, |
PARTITION `p3` VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB, |
PARTITION `p4` VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB |
);
|
insert into t1 (created_at,key1,key2) |
select |
date_add('1995-01-01', interval seq day), |
seq,
|
seq
|
from seq_1_to_10000; |
explain
|
select * |
from t1 ignore index(primary) |
where key1=100 order by id limit 10; |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ |
| 1 | SIMPLE | t1 | ref | key1 | key1 | 5 | const | 4 | Using where; Using filesort | |
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ |
The optimizer can't use index key1 to resolve the ORDER BY.
Try the same without partitioning:
create table t2 ( |
id int auto_increment, |
created_at datetime,
|
key1 int, |
key2 int, |
key (key1), |
primary key(id,created_at) |
);
|
insert into t2 select * from t1; |
explain
|
select * |
from t2 ignore index(primary) |
where key1=100 order by id limit 10; |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+ |
| 1 | SIMPLE | t2 | ref | key1 | key1 | 5 | const | 1 | Using where | |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+ |
There's a workaround: mention the extended key parts explicitly:
 |
create table t3 ( |
id int auto_increment, |
created_at datetime,
|
key1 int, |
key2 int, |
key (key1, id), |
primary key(id,created_at) |
);
|
insert into t3 select * from t1; |
explain
|
select *
|
from t3 ignore index(primary)
|
where key1=100 order by id limit 10;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
| 1 | SIMPLE | t3 | ref | key1 | key1 | 5 | const | 1 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|