diff options
| author | Monty <monty@mariadb.org> | 2023-01-24 16:25:26 +0200 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2023-02-10 12:59:36 +0200 |
| commit | 5e5988dbb88f45d1dfebdc3a71153489b2151717 (patch) | |
| tree | 509aa38f9e1cb849cb9b821ffb43bf3ad02d35ac /mysql-test/main/opt_trace.result | |
| parent | 33af691f23c58bc248dae42a020c4dc293866d38 (diff) | |
| download | mariadb-git-5e5988dbb88f45d1dfebdc3a71153489b2151717.tar.gz | |
Selectivity: apply found_constraint heuristic only to post-join #rows.
matching_candidates_in_table() computes the number of rows one
gets from the current table after applying the WHERE clause on
just this table
The function had a "found_counstraint heuristic" which reduced the
number of rows after WHERE check by 25% if there were comparisons
between key parts in table T and previous tables, like WHERE
T.keyXpartY= func(prev_table.cols)
Note that such comparisons can only be checked when the row of
table T is joined with rows of the previous tables. It is wrong
to apply the selectivity before the join operation.
Fixed by moving the 'found_constraint' code to a separate function
and only reducing the #rows in 'records_out'.
Renamed matching_candidates_in_table() to apply_selectivity_for_table() as
the function now either applies selectivity on the rows (depending
on the value of thd->variables.optimizer_use_condition_selectivity)
or uses the selectivity from the available range conditions.
Diffstat (limited to 'mysql-test/main/opt_trace.result')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 15 |
1 files changed, 9 insertions, 6 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 4ac15c41c27..72e94a929f5 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -12537,7 +12537,7 @@ EXPLAIN "loops": 3, "rows": 10000, "cost": 1.701731924, - "filtered": 4.307688236, + "filtered": 3.230766058, "attached_condition": "t1.b < 5000 and t1.c < 1000" }, "buffer_type": "flat", @@ -12800,7 +12800,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "access_type": "scan_with_join_cache", "rows": 10000, "rows_after_filter": 430.7688, - "rows_out": 430.7688, + "rows_out": 323.0766, "cost": 1.701731924, "index_only": false, "chosen": true @@ -12809,7 +12809,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "chosen_access_method": { "type": "scan", "rows_read": 430.7688, - "rows_out": 430.7688, + "rows_out": 323.0766, "cost": 1.701731924, "uses_join_buffering": true } @@ -12820,8 +12820,11 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and { "plan_prefix": "three", "table": "t1", - "rows_for_plan": 1292.3064, - "cost_for_plan": 1.712236739 + "rows_for_plan": 969.2298, + "cost_for_plan": 1.712236739, + "pushdown_cond_selectivity": 0.75, + "filtered": 3.230766, + "rows_out": 323.0766 } ] }, @@ -12836,7 +12839,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and }, { "best_join_order": ["three", "t1"], - "rows": 1292.3064, + "rows": 969.2298, "cost": 1.712236739 }, { |