summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/group_min_max.result4
-rw-r--r--mysql-test/main/mdev-25830.result2
-rw-r--r--mysql-test/main/opt_trace_index_merge.result6
-rw-r--r--mysql-test/main/opt_trace_selectivity.result394
-rw-r--r--mysql-test/main/opt_trace_selectivity.test52
-rw-r--r--mysql-test/main/rowid_filter_innodb.result2
6 files changed, 455 insertions, 5 deletions
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index 975b6ee9a86..9fd9ecf6ce2 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -2460,8 +2460,8 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer index a a 10 NULL 15 Using where; Using index
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1_outer.a 1
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2
+1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 3 Using index
2 MATERIALIZED t1 range a a 5 NULL 5 Using where; Using index
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
diff --git a/mysql-test/main/mdev-25830.result b/mysql-test/main/mdev-25830.result
index e62d1ff3f55..2c606205565 100644
--- a/mysql-test/main/mdev-25830.result
+++ b/mysql-test/main/mdev-25830.result
@@ -47,7 +47,7 @@ WHERE task2.`sys_id` LIKE '8e7792a7dbfffb00fff8a345ca961934%'
ORDER BY sysapproval_approver0.`order`
LIMIT 0, 50 ;
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
-1 SIMPLE task2 range PRIMARY,sys_class_name_2,sys_domain_path PRIMARY 96 NULL 1 0.00 98.00 100.00 Using where; Using temporary; Using filesort
+1 SIMPLE task2 range PRIMARY,sys_class_name_2,sys_domain_path PRIMARY 96 NULL 1 0.00 100.00 100.00 Using where; Using temporary; Using filesort
1 SIMPLE task1 ref PRIMARY,task_parent,sys_class_name_2,sys_domain_path task_parent 99 test.task2.sys_id 1 NULL 100.00 NULL Using index condition; Using where
1 SIMPLE sysapproval_approver0 ref sysapproval_approver_ref5,sys_domain_path,sysapproval_approver_CHG1975376 sysapproval_approver_ref5 99 test.task1.sys_id 1 NULL 100.00 NULL Using index condition; Using where
drop table sysapproval_approver,task;
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result
index 885740d59c3..40136fe300a 100644
--- a/mysql-test/main/opt_trace_index_merge.result
+++ b/mysql-test/main/opt_trace_index_merge.result
@@ -193,7 +193,11 @@ explain select * from t1 where a=1 or b=1 {
}
},
{
- "selectivity_for_indexes": [],
+ "selectivity_for_indexes": [
+ {
+ "use_opt_range_condition_rows_selectivity": 0.002
+ }
+ ],
"selectivity_for_columns": [],
"cond_selectivity": 0.002
}
diff --git a/mysql-test/main/opt_trace_selectivity.result b/mysql-test/main/opt_trace_selectivity.result
new file mode 100644
index 00000000000..54f05885049
--- /dev/null
+++ b/mysql-test/main/opt_trace_selectivity.result
@@ -0,0 +1,394 @@
+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;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+select count(*) from t1 where a=2;
+count(*)
+200
+select count(*) from t1 where b=5;
+count(*)
+1100
+select count(*) from t1 where c=5;
+count(*)
+667
+select count(*) from t1 where c=10;
+count(*)
+1667
+select count(*) from t1 where a=2 and b=5;
+count(*)
+20
+select count(*) from t1 where c=10 and b=5;
+count(*)
+433
+select count(*) from t1 where c=5 and b=5;
+count(*)
+334
+set optimizer_trace="enabled=on";
+select count(*) from t1 where a=2 and b=5 and c=10;
+count(*)
+14
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
+[
+
+ [
+
+ {
+ "plan_prefix":
+ [
+ ],
+ "get_costs_for_tables":
+ [
+
+ {
+ "best_access_path":
+ {
+ "table": "t1",
+ "considered_access_paths":
+ [
+
+ {
+ "access_type": "ref",
+ "index": "a",
+ "used_range_estimates": true,
+ "rows": 104,
+ "cost": 104.16562,
+ "chosen": true
+ },
+
+ {
+ "access_type": "ref",
+ "index": "b",
+ "used_range_estimates": true,
+ "rows": 340,
+ "cost": 340.2577963,
+ "chosen": false,
+ "cause": "cost"
+ },
+
+ {
+ "access_type": "ref",
+ "index": "c",
+ "used_range_estimates": true,
+ "rows": 632,
+ "cost": 632.3718449,
+ "chosen": false,
+ "cause": "cost"
+ },
+
+ {
+ "access_type": "index_merge",
+ "resulting_rows": 7,
+ "cost": 2.173416331,
+ "chosen": true
+ }
+ ],
+ "chosen_access_method":
+ {
+ "type": "index_merge",
+ "records": 7,
+ "cost": 2.173416331,
+ "uses_join_buffering": false
+ }
+ }
+ }
+ ]
+ },
+
+ {
+ "plan_prefix":
+ [
+ ],
+ "table": "t1",
+ "rows_for_plan": 7,
+ "cost_for_plan": 3.573416331,
+ "estimated_join_cardinality": 7
+ }
+ ]
+]
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.009454545
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 0.1
+ },
+
+ {
+ "use_opt_range_condition_rows_selectivity": 6.363636e-4
+ }
+ ]
+]
+select count(*) from t1 where a=2 and b=5 and c=5;
+count(*)
+3
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
+[
+
+ [
+
+ {
+ "plan_prefix":
+ [
+ ],
+ "get_costs_for_tables":
+ [
+
+ {
+ "best_access_path":
+ {
+ "table": "t1",
+ "considered_access_paths":
+ [
+
+ {
+ "access_type": "ref",
+ "index": "a",
+ "used_range_estimates": true,
+ "rows": 6,
+ "cost": 6.127343464,
+ "chosen": true
+ },
+
+ {
+ "access_type": "ref",
+ "index": "b",
+ "used_range_estimates": true,
+ "rows": 232,
+ "cost": 232.2156139,
+ "chosen": false,
+ "cause": "cost"
+ },
+
+ {
+ "access_type": "ref",
+ "index": "c",
+ "used_range_estimates": true,
+ "rows": 293,
+ "cost": 293.2394392,
+ "chosen": false,
+ "cause": "cost"
+ },
+
+ {
+ "access_type": "index_merge",
+ "resulting_rows": 0.6,
+ "cost": 2.172957403,
+ "chosen": true
+ }
+ ],
+ "chosen_access_method":
+ {
+ "type": "index_merge",
+ "records": 0.6,
+ "cost": 2.172957403,
+ "uses_join_buffering": false
+ }
+ }
+ }
+ ]
+ },
+
+ {
+ "plan_prefix":
+ [
+ ],
+ "table": "t1",
+ "rows_for_plan": 0.6,
+ "cost_for_plan": 2.292957403,
+ "estimated_join_cardinality": 0.6
+ }
+ ]
+]
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 5.454545e-4
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 0.1
+ }
+ ]
+]
+# Ensure that we only use selectivity from non used index for simple cases
+select count(*) from t1 where (a=2 and b= 5);
+count(*)
+20
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.017545455
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 0.073181818
+ }
+ ]
+]
+# All of the following should have selectivity=1 for index 'b'
+select count(*) from t1 where (a=2 and b between 0 and 100);
+count(*)
+200
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.017545455
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 1
+ }
+ ]
+]
+select count(*) from t1 where (a in (2,3) and b between 0 and 100);
+count(*)
+400
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.035090909
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 1
+ }
+ ]
+]
+select count(*) from t1 where (a>2 and b between 0 and 100);
+count(*)
+10702
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.973909091
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 1
+ }
+ ]
+]
+select count(*) from t1 where (a>=2 and b between 0 and 100);
+count(*)
+10902
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.991454545
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 1
+ }
+ ]
+]
+select count(*) from t1 where (a<=2 and b between 0 and 100);
+count(*)
+298
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.026181818
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 1
+ }
+ ]
+]
+select count(*) from t1 where (a<2 and b between 0 and 100);
+count(*)
+98
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.008636364
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 1
+ }
+ ]
+]
+select count(*) from t1 where (a between 2 and 3 and b between 0 and 100);
+count(*)
+400
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes'))
+[
+
+ [
+
+ {
+ "index_name": "a",
+ "selectivity_from_index": 0.035090909
+ },
+
+ {
+ "index_name": "b",
+ "selectivity_from_index": 1
+ }
+ ]
+]
+drop table t1;
+set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace_selectivity.test b/mysql-test/main/opt_trace_selectivity.test
new file mode 100644
index 00000000000..4d59d4974cd
--- /dev/null
+++ b/mysql-test/main/opt_trace_selectivity.test
@@ -0,0 +1,52 @@
+--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;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+select count(*) from t1 where a=2 and b=5 and c=5;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_indexes')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--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')) 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')) 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')) 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')) 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')) 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')) 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')) 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')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t1;
+set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 74142e3bcb2..7fbdbe1b9ed 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2975,7 +2975,7 @@ id y x
explain extended select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index
-1 SIMPLE t2 ref x,y y 5 const 2 100.00 Using where
+1 SIMPLE t2 ref x,y y 5 const 2 83.33 Using where
Warnings:
Note 1003 select 1 AS `id`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`x` AS `x` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`y` = 2 and `test`.`t2`.`x` = 1
drop table t1, t2;
View Lorry Controller Status