diff options
author | Monty <monty@mariadb.org> | 2021-10-09 16:16:10 +0300 |
---|---|---|
committer | Sergei Petrunia <sergey@mariadb.com> | 2023-02-02 20:49:35 +0300 |
commit | 956980971f98e1eb451e71856fe823470a5d1d32 (patch) | |
tree | 369d47e8f78d05c313d42e24492daaeb15c24e91 /mysql-test/suite/maria | |
parent | 6fa7451759e0832cc13a0e47a8300292b96bdb75 (diff) | |
download | mariadb-git-956980971f98e1eb451e71856fe823470a5d1d32.tar.gz |
Update cost for hash and cached joins
The old code did not't correctly add TIME_FOR_COMPARE to rows that are
part of the scan that will be compared with the attached where clause.
Now the cost calculation for hash join and full join cache join are
identical except for HASH_FANOUT (10%)
The cost for a join with keys is now also uniform.
The total cost for a using a key for lookup is calculated in one place as:
(cost_of_finding_rows_through_key(records) + records/TIME_FOR_COMPARE)*
record_count_of_previous_row_combinations + startup_cost
startup_cost is the cost of a creating a temporary table (if needed)
Best_cost now includes the cost of comparing all WHERE clauses and also
cost of joining with previous row combinations.
Other things:
- Optimizer trace is now printing the total costs, including testing the
WHERE clause (TIME_FOR_COMPARE) and comparing with all previous rows.
- In optimizer trace, include also total cost of query together with the
final join order. This makes it easier to find out where the cost was
calculated.
- Old code used filter even if the cost for it was higher than not using a
filter. This is not corrected.
- When rebasing on 10.11, I noticed some changes to access_cost_factor
calculation. These changes was not picked as the coming changes
to filtering will make that code obsolete.
Diffstat (limited to 'mysql-test/suite/maria')
-rw-r--r-- | mysql-test/suite/maria/mrr.result | 4 |
1 files changed, 2 insertions, 2 deletions
diff --git a/mysql-test/suite/maria/mrr.result b/mysql-test/suite/maria/mrr.result index cc42f81e3c4..066f1a50aab 100644 --- a/mysql-test/suite/maria/mrr.result +++ b/mysql-test/suite/maria/mrr.result @@ -404,8 +404,8 @@ FROM t1 AS table1, t2 AS table2 WHERE table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE table2 ALL NULL NULL NULL NULL 2 -1 SIMPLE table1 ALL PRIMARY,col_varchar_1024_latin1_key NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 SIMPLE table2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE table1 ref PRIMARY,col_varchar_1024_latin1_key col_varchar_1024_latin1_key 1027 test.table2.col_varchar_10_latin1 2 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT count(*) FROM t1 AS table1, t2 AS table2 WHERE |