diff options
author | Monty <monty@mariadb.org> | 2022-06-02 19:47:23 +0300 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2022-06-18 15:23:13 +0200 |
commit | 08b13c4fd68a919771ecf6f0b01d0b98d2cf603f (patch) | |
tree | 30647c31e65af7d5e2d6f9af097f890a4959b42d /mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result | |
parent | f85c65d3c1ee397a9cef6b2c9c3c65666f219a7b (diff) | |
download | mariadb-git-08b13c4fd68a919771ecf6f0b01d0b98d2cf603f.tar.gz |
Added EQ_REF chaining to the greedy_optimizer
MDEV-28073 Slow query performance in MariaDB when using many table
The idea is to prefer and chain EQ_REF tables (tables that uses an
unique key to find a row) when searching for the best table combination.
This significantly reduces row combinations that has to be examined.
This is optimization is enabled when setting optimizer_prune_level=2
(which is now default).
Implementation:
- optimizer_prune_level has a new level, 2, which enables EQ_REF
optimization in addition to the pruning done by level 1.
Level 2 is now default.
- Added JOIN::eq_ref_tables that contains bits of tables that could use
potentially use EQ_REF access in the query. This is calculated
in sort_and_filter_keyuse()
Under optimizer_prune_level=2:
- When the greedy_optimizer notices that the preceding table was an
EQ_REF table, it tries to add an EQ_REF table next. If an EQ_REF
table exists, only this one will be considered at this level.
We also collect all EQ_REF tables chained by the next levels and these
are ignored on the starting level as we have already examined these.
If no EQ_REF table exists, we continue as normal.
This optimization speeds up the greedy_optimizer combination test with
~25%
Other things:
- I ported the changes in MySQL 5.7 to greedy_optimizer.test to MariaDB
to be able to ensure we can handle all cases that MySQL can do.
- I have run all tests with --mysqld=--optimizer_prune_level=1 to verify that
there where no test changes.
Diffstat (limited to 'mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result')
-rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result | 4 |
1 files changed, 2 insertions, 2 deletions
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 27f15844b33..f1250977286 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2435,9 +2435,9 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space. Meaning: 0 - do not apply any heuristic, thus perform exhaustive search; 1 - prune plans based on number of retrieved rows +VARIABLE_COMMENT Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space. Meaning: 0 - do not apply any heuristic, thus perform exhaustive search: 1 - prune plans based on cost and number of retrieved rows eq_ref: 2 - prune also if we find an eq_ref chain NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 1 +NUMERIC_MAX_VALUE 2 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO |