From b729896d00e022f6205399376c0cc107e1ee0704 Mon Sep 17 00:00:00 2001 From: Monty Date: Tue, 10 May 2022 11:47:20 +0300 Subject: MDEV-28073 Query performance degradation in newer MariaDB versions when using many tables The issue was that best_extension_by_limited_search() had to go through too many plans with the same cost as there where many EQ_REF tables. Fixed by shortcutting EQ_REF (AND REF) when the result only contains one row. This got the optimization time down from hours to sub seconds. The only known downside with this patch is that in some cases a table with ref and 1 record may be used before on EQ_REF table. The faster optimzation phase should compensate for this. --- sql/sql_select.h | 2 ++ 1 file changed, 2 insertions(+) (limited to 'sql/sql_select.h') diff --git a/sql/sql_select.h b/sql/sql_select.h index e854792b073..4a2929207a5 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -989,6 +989,8 @@ public: */ enum sj_strategy_enum sj_strategy; + /* Type of join (EQ_REF, REF etc) */ + enum join_type type; /* Valid only after fix_semijoin_strategies_for_picked_join_order() call: if sj_strategy!=SJ_OPT_NONE, this is the number of subsequent tables that -- cgit v1.2.1