mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
138 lines
4.1 KiB
Text
138 lines
4.1 KiB
Text
--source include/not_embedded.inc
|
|
set @tmp_opt_switch= @@optimizer_switch;
|
|
set optimizer_switch='index_merge_sort_intersection=on';
|
|
set optimizer_trace='enabled=on';
|
|
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (a int, b int, c int, filler char(100),
|
|
key(a), key(b), key(c)) charset=latin1;
|
|
insert into t1 select
|
|
A.a * B.a*10 + C.a*100,
|
|
A.a * B.a*10 + C.a*100,
|
|
A.a,
|
|
'filler'
|
|
from t0 A, t0 B, t0 C;
|
|
|
|
--echo This should use union:
|
|
explain select * from t1 where a=1 or b=1;
|
|
--disable_view_protocol
|
|
select * from information_schema.OPTIMIZER_TRACE;
|
|
--enable_view_protocol
|
|
drop table t0,t1;
|
|
set optimizer_trace="enabled=off";
|
|
set @@optimizer_switch= @tmp_opt_switch;
|
|
|
|
--echo # More tests added index_merge access
|
|
|
|
--enable_warnings
|
|
create table t1
|
|
(
|
|
/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
|
|
st_a int not null default 0,
|
|
swt1a int not null default 0,
|
|
swt2a int not null default 0,
|
|
|
|
st_b int not null default 0,
|
|
swt1b int not null default 0,
|
|
swt2b int not null default 0,
|
|
|
|
/* fields/keys for row retrieval tests */
|
|
key1 int,
|
|
key2 int,
|
|
key3 int,
|
|
key4 int,
|
|
|
|
/* make rows much bigger then keys */
|
|
filler1 char (200),
|
|
filler2 char (200),
|
|
filler3 char (200),
|
|
filler4 char (200),
|
|
filler5 char (200),
|
|
filler6 char (200),
|
|
|
|
/* order of keys is important */
|
|
key sta_swt12a(st_a,swt1a,swt2a),
|
|
key sta_swt1a(st_a,swt1a),
|
|
key sta_swt2a(st_a,swt2a),
|
|
key sta_swt21a(st_a,swt2a,swt1a),
|
|
key st_a(st_a),
|
|
key stb_swt1a_2b(st_b,swt1b,swt2a),
|
|
key stb_swt1b(st_b,swt1b),
|
|
key st_b(st_b),
|
|
|
|
key(key1),
|
|
key(key2),
|
|
key(key3),
|
|
key(key4)
|
|
) ;
|
|
# Fill table
|
|
create table t0 as select * from t1;
|
|
--disable_query_log
|
|
--echo # Printing of many insert into t0 values (....) disabled.
|
|
let $cnt=1000;
|
|
while ($cnt)
|
|
{
|
|
eval insert into t0 values (1, 2, 3, 1, 2, 3, 0, 0, 0, 0, 'data1', 'data2', 'data3', 'data4', 'data5', 'data6');
|
|
dec $cnt;
|
|
}
|
|
--enable_query_log
|
|
|
|
alter table t1 disable keys;
|
|
--disable_query_log
|
|
--echo # Printing of many insert into t1 select .... from t0 disabled.
|
|
let $1=4;
|
|
while ($1)
|
|
{
|
|
let $2=4;
|
|
while ($2)
|
|
{
|
|
let $3=4;
|
|
while ($3)
|
|
{
|
|
eval insert into t1 select $1, $2, $3, $1 ,$2, $3, key1, key2, key3, key4, filler1, filler2, filler3, filler4, filler5, filler6 from t0;
|
|
dec $3;
|
|
}
|
|
dec $2;
|
|
}
|
|
dec $1;
|
|
}
|
|
|
|
--echo # Printing of many insert into t1 (...) values (....) disabled.
|
|
# Row retrieval tests
|
|
# -1 is used for values 'out of any range we are using'
|
|
# insert enough rows for index intersection to be used for (key1,key2)
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 100, 100,'key1-key2-key3-key4');
|
|
let $cnt=400;
|
|
while ($cnt)
|
|
{
|
|
eval insert into t1 (key1, key2, key3, key4, filler1) values (100, -1, 100, -1,'key1-key3');
|
|
dec $cnt;
|
|
}
|
|
let $cnt=400;
|
|
while ($cnt)
|
|
{
|
|
eval insert into t1 (key1, key2, key3, key4, filler1) values (-1, 100, -1, 100,'key2-key4');
|
|
dec $cnt;
|
|
}
|
|
--enable_query_log
|
|
alter table t1 enable keys;
|
|
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
|
|
set optimizer_trace='enabled=on';
|
|
|
|
# Enable after fix MDEV-31408
|
|
--disable_view_protocol
|
|
--echo # 3-way ROR-intersection
|
|
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
--echo # ROR-union(ROR-intersection, ROR-range)
|
|
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
--enable_view_protocol
|
|
|
|
drop table t0,t1;
|
|
set optimizer_trace="enabled=off";
|