|
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 |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
|