From 3fa99f0c0e294297557e5edcc31d26d2430d4c3c Mon Sep 17 00:00:00 2001 From: Monty Date: Tue, 27 Dec 2022 14:49:27 +0200 Subject: Change cost for REF to take into account cost for 1 extra key read_next The main difference in code path between EQ_REF and REF is that for REF we have to do an extra read_next on the index to check that there is no more matching rows. Before this patch we added a preference of EQ_REF by ensuring that REF would always estimate to find at least 2 rows. This patch adds the cost of the extra key read_next to REF access and removes the code that limited REF to at least 2 rows. For some queries this can have a big effect as the total estimated rows will be halved for each REF table with 1 rows. multi_range cost calculations are also changed to take into account the difference between EQ_REF and REF. The effect of the patch to the test suite: - About 80 test case changed - Almost all changes where for EXPLAIN where estimated rows for REF where changed from 2 to 1. - A few test cases using explain extended had a change of 'filtered'. This is because of the estimated rows are now closer to the calculated selectivity. - A very few test had a change of table order. This is because the change of estimated rows from 2 to 1 or the small cost change for REF (main.subselect_sj_jcl6, main.group_by, main.dervied_cond_pushdown, main.distinct, main.join_nested, main.order_by, main.join_cache) - No key statistics and the estimated rows are now smaller which cased estimated filtering to be lower. (main.subselect_sj_mat) - The number of total rows are halved. (main.derived_cond_pushdown) - Plans with 1 row changed to use RANGE instead of REF. (main.group_min_max) - ALL changed to REF (main.key_diff) - Key changed from ref + index_only to PRIMARY key for InnoDB, as OPTIMIZER_ROW_LOOKUP_COST + OPTIMIZER_ROW_NEXT_FIND_COST is smaller than OPTIMIZER_KEY_LOOKUP_COST + OPTIMIZER_KEY_NEXT_FIND_COST. (main.join_outer_innodb) - Cost changes printouts (main.opt_trace*) - Result order change (innodb_gis.rtree) --- plugin/type_inet/mysql-test/type_inet/type_inet4.result | 2 +- plugin/type_inet/mysql-test/type_inet/type_inet6.result | 2 +- plugin/type_uuid/mysql-test/type_uuid/type_uuid.result | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) (limited to 'plugin') diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet4.result b/plugin/type_inet/mysql-test/type_inet/type_inet4.result index 7763b28e1fb..30cb6f7fe94 100644 --- a/plugin/type_inet/mysql-test/type_inet/type_inet4.result +++ b/plugin/type_inet/mysql-test/type_inet/type_inet4.result @@ -1337,7 +1337,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1 Using index; Using where SET @@optimizer_switch=DEFAULT; DROP TABLE t1; # diff --git a/plugin/type_inet/mysql-test/type_inet/type_inet6.result b/plugin/type_inet/mysql-test/type_inet/type_inet6.result index 8d52e85cb91..b399400ab87 100644 --- a/plugin/type_inet/mysql-test/type_inet/type_inet6.result +++ b/plugin/type_inet/mysql-test/type_inet/type_inet6.result @@ -1330,7 +1330,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery a a 17 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 17 func 1 Using index; Using where SET @@optimizer_switch=DEFAULT; DROP TABLE t1; # diff --git a/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result index ab30c262148..b3955d09735 100644 --- a/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result +++ b/plugin/type_uuid/mysql-test/type_uuid/type_uuid.result @@ -2524,7 +2524,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a AS a_inner FROM t1 GROUP BY a_inner); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t1 index_subquery a a 17 func 2 Using index; Using where +2 DEPENDENT SUBQUERY t1 index_subquery a a 17 func 1 Using index; Using where SET @@optimizer_switch=DEFAULT; DROP TABLE t1; # -- cgit v1.2.1