mirror of
https://github.com/MariaDB/server.git
synced 2025-11-05 13:26:14 +01:00
93 lines
3.7 KiB
Text
93 lines
3.7 KiB
Text
#
|
|
# MDEV-36132 Optimizer support for functional indexes: handle GROUP/ORDER BY
|
|
#
|
|
create table t (c int, key (c));
|
|
insert into t select seq from seq_1_to_10000;
|
|
alter table t
|
|
add column vc int as (c + 1),
|
|
add index(vc);
|
|
explain select c from t order by c;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index
|
|
explain select vc from t order by vc;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL vc 5 NULL 10000 Using index
|
|
explain select vc from t order by vc limit 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL vc 5 NULL 10 Using index
|
|
explain select c + 1 from t order by c + 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
|
|
explain select c + 1 from t order by vc;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
|
|
explain select vc from t order by c + 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
|
|
explain select vc from t order by c;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index
|
|
explain select c from t order by vc;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
|
|
explain select c from t order by c + 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
|
|
explain select vc from t order by c + 1 limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL vc 5 NULL 2
|
|
explain select c + 1 from t order by c + 1 limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL vc 5 NULL 2
|
|
explain select c + 1 from t order by vc limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL vc 5 NULL 2
|
|
drop table t;
|
|
create table t (c int, key (c));
|
|
insert into t select seq from seq_1_to_10000;
|
|
alter table t
|
|
add column vc1 int as (c + 1),
|
|
add index(vc1);
|
|
alter table t
|
|
add column vc2 int as (vc1 * 2),
|
|
add index(vc2);
|
|
explain select c from t order by vc2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
|
|
explain select vc2 from t order by vc1 * 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
|
|
explain select vc2 from t order by vc1 * 2 limit 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t index NULL vc2 5 NULL 2
|
|
drop table t;
|
|
create table t (c int, vc int generated always as (1 + 1) virtual, key (c));
|
|
insert into t values (42, default), (83, default);
|
|
explain select vc from t order by vc;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 2 Using filesort
|
|
select vc from t order by vc;
|
|
vc
|
|
2
|
|
2
|
|
drop table t;
|
|
create table t (c int);
|
|
insert into t select seq from seq_1_to_10000;
|
|
alter table t
|
|
add column vc1 int as (c + 1);
|
|
alter table t
|
|
add column vc2 int as (1 - c),
|
|
add index(vc1, vc2);
|
|
explain select vc1, vc2 from t order by c + 1, 1 - c;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t ALL NULL NULL NULL NULL 10000 Using filesort
|
|
drop table t;
|
|
create table t (c int, key (c));
|
|
insert into t select seq from seq_1_to_10000;
|
|
alter table t
|
|
add column vc1 int as (c + 1),
|
|
add index(vc1);
|
|
alter table t
|
|
add column vc2 int as (1 - c),
|
|
add index(vc2);
|
|
drop table t;
|