mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
86 lines
3.1 KiB
Text
86 lines
3.1 KiB
Text
--source include/have_sequence.inc
|
|
--source include/not_embedded.inc
|
|
|
|
#
|
|
# Test changes in calculate_cond_selectivity_for_table()
|
|
#
|
|
create or replace table t1 (a int, b int, c int, key(a,c), key(b,c), key (c,b)) engine=aria;
|
|
insert into t1 select seq/100+1, mod(seq,10), mod(seq,15) from seq_1_to_10000;
|
|
insert into t1 select seq/100+1, mod(seq,10), 10 from seq_1_to_1000;
|
|
optimize table t1;
|
|
|
|
select count(*) from t1 where a=2;
|
|
select count(*) from t1 where b=5;
|
|
select count(*) from t1 where c=5;
|
|
select count(*) from t1 where c=10;
|
|
select count(*) from t1 where a=2 and b=5;
|
|
select count(*) from t1 where c=10 and b=5;
|
|
select count(*) from t1 where c=5 and b=5;
|
|
|
|
set optimizer_trace="enabled=on";
|
|
select count(*) from t1 where a=2 and b=5 and c=10;
|
|
|
|
set @trace=(select trace from INFORMATION_SCHEMA.OPTIMIZER_TRACE);
|
|
|
|
# The second JSON_EXTRACT is for --view-protocol which wraps every select:
|
|
select
|
|
JSON_DETAILED(
|
|
JSON_EXTRACT(
|
|
JSON_EXTRACT(@trace, '$**.considered_execution_plans'),
|
|
'$[0]'
|
|
)
|
|
) as JS;
|
|
|
|
select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.selectivity_for_indexes')) as JS;
|
|
|
|
select count(*) from t1 where a=2 and b=5 and c=5;
|
|
set @trace=(select trace from INFORMATION_SCHEMA.OPTIMIZER_TRACE);
|
|
|
|
# The second JSON_EXTRACT is for --view-protocol which wraps every select:
|
|
select
|
|
JSON_DETAILED(
|
|
JSON_EXTRACT(
|
|
JSON_EXTRACT(@trace, '$**.considered_execution_plans'),
|
|
'$[0]'
|
|
)
|
|
) as JS;
|
|
select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.selectivity_for_indexes')) as JS;
|
|
|
|
--echo # Ensure that we only use selectivity from non used index for simple cases
|
|
|
|
|
|
select count(*) from t1 where (a=2 and b= 5);
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) as JS
|
|
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
--echo # All of the following should have selectivity=1 for index 'b'
|
|
select count(*) from t1 where (a=2 and b between 0 and 100);
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) as JS
|
|
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
select count(*) from t1 where (a in (2,3) and b between 0 and 100);
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) as JS
|
|
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
select count(*) from t1 where (a>2 and b between 0 and 100);
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) as JS
|
|
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
select count(*) from t1 where (a>=2 and b between 0 and 100);
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) as JS
|
|
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
select count(*) from t1 where (a<=2 and b between 0 and 100);
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) as JS
|
|
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
select count(*) from t1 where (a<2 and b between 0 and 100);
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) as JS
|
|
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
select count(*) from t1 where (a between 2 and 3 and b between 0 and 100);
|
|
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) as JS
|
|
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
|
|
drop table t1;
|
|
set optimizer_trace='enabled=off';
|