From 4eac5df3fcebb1adf52e33d9d88dc05bc1e339ce Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 17 Aug 2018 14:27:42 -0700 Subject: MDEV-16934 Query with very large IN clause lists runs slowly This patch introduces support for the system variable eq_range_index_dive_limit that existed in MySQL starting from 5.6. The variable sets a limit for index dives into equality ranges. Index dives are performed by optimizer to estimate the number of rows in range scans. Index dives usually provide good estimate but they are pretty expensive. To estimate the number of rows in equality ranges statistical data on indexes can be employed. Its usage gives not so good estimates but it's cheap. So if the number of equality dives required by an index scan exceeds the set limit no dives for equality ranges are performed by the optimizer for this index. As the new system variable is introduced in a stable version the default value for it is set to a special value meaning there is no limit for the number of index dives performed by the optimizer. The patch partially uses the MySQL code for WL 5957 'Statistics-based Range optimization for many ranges'. --- sql/opt_range.h | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'sql/opt_range.h') diff --git a/sql/opt_range.h b/sql/opt_range.h index c1f7079ce7f..6698c987e78 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -242,7 +242,7 @@ public: Number of children of this element in the RB-tree, plus 1 for this element itself. */ - uint16 elements; + uint32 elements; /* Valid only for elements which are RB-tree roots: Number of times this RB-tree is referred to (it is referred by SEL_ARG::next_key_part or by @@ -1664,6 +1664,8 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param); + #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); #endif -- cgit v1.2.1 From c826b6b8da3c9ee89ab457155bae96310213e5a1 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 24 Aug 2018 20:53:00 -0700 Subject: Added a new parameter for the function eq_ranges_exceeds_limit() introduced in the patch fo MDEV-16934. --- sql/opt_range.h | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'sql/opt_range.h') diff --git a/sql/opt_range.h b/sql/opt_range.h index 6698c987e78..9e0bd3ae9ff 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1664,7 +1664,8 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); -bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param); +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param, + uint limit); #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); -- cgit v1.2.1