diff options
| author | Monty <monty@mariadb.org> | 2022-12-02 17:18:50 +0200 |
|---|---|---|
| committer | Sergei Petrunia <sergey@mariadb.com> | 2023-02-03 11:53:38 +0300 |
| commit | 8b7c0d69d2503cd3d98aae8c4b62d2df1e7975d3 (patch) | |
| tree | e4c1bc7765736bcecc25787405fe8ba5a3a6f165 /mysql-test/main/innodb_ext_key,off.rdiff | |
| parent | 02f6ba571e930493c51b92b8c23e2e7fadcedbe0 (diff) | |
| download | mariadb-git-8b7c0d69d2503cd3d98aae8c4b62d2df1e7975d3.tar.gz | |
In best_access_path() change record_count to 1.0 if its less than 1.0.
In essence this means that we expect the user query to have at least
one matching row in the end.
This change will not affect the estimated rows for the plan, but will
ensure that the cost for adding a table is not neglected because of
record count being too low.
The reasons for this is that if we have table combination that
together has a very high selectivity then join record_count could
become very low (close to 0)
This would cause costs for all future tables to be so small that they
are irrelevant for the rest of the plan.
This has been shown to be the case in some performance benchmarks and
in a few mtr tests.
There is also still a problem in selectivity calculations as joining two
tables in different order causes a different estimation of total rows.
This can be seen in selectivity_innodb.test, test 'Q20' where joining
nation,supplier is expecting 1.111 rows_out while joining supplier,nation
is expecting 0.04 rows_out.
The reason for 0.04 is that the optimizer estimates 'supplier' to have
10 matching rows, and joining with nation (eq_ref) has 1 row. However
selectivity of n_name = 'UNITED STATES' makes the optimizer things
that there will be only 0.04 matching rows.
This patch avoids this "too low row count" to affect cost
caclulations.
Diffstat (limited to 'mysql-test/main/innodb_ext_key,off.rdiff')
| -rw-r--r-- | mysql-test/main/innodb_ext_key,off.rdiff | 6 |
1 files changed, 2 insertions, 4 deletions
diff --git a/mysql-test/main/innodb_ext_key,off.rdiff b/mysql-test/main/innodb_ext_key,off.rdiff index 8c702e82bb1..ef11f9c05bc 100644 --- a/mysql-test/main/innodb_ext_key,off.rdiff +++ b/mysql-test/main/innodb_ext_key,off.rdiff @@ -265,7 +265,7 @@ drop table t0,t1,t2; # # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' -@@ -770,12 +770,13 @@ +@@ -770,11 +770,12 @@ { "table": { "table_name": "t1", @@ -281,8 +281,7 @@ "loops": 1, "rows": 1, "cost": "COST_REPLACED", - "filtered": 100, -@@ -809,9 +810,9 @@ +@@ -810,8 +811,8 @@ "access_type": "range", "possible_keys": ["k1"], "key": "k1", @@ -293,4 +292,3 @@ "loops": 1, "rows": 1, "cost": "COST_REPLACED", - "filtered": 100, |