diff options
Diffstat (limited to 'mysql-test/main/opt_trace_index_merge.test')
-rw-r--r-- | mysql-test/main/opt_trace_index_merge.test | 133 |
1 files changed, 133 insertions, 0 deletions
diff --git a/mysql-test/main/opt_trace_index_merge.test b/mysql-test/main/opt_trace_index_merge.test new file mode 100644 index 00000000000..73240b6a9e2 --- /dev/null +++ b/mysql-test/main/opt_trace_index_merge.test @@ -0,0 +1,133 @@ +--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)); +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; +select * from information_schema.OPTIMIZER_TRACE; +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'; + +--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')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) 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')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +drop table t0,t1; +set optimizer_trace="enabled=off"; |