From 9b475ee3c1855c23fc716ba83a261c238d9b6f83 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Wed, 5 Aug 2015 20:43:25 +0300 Subject: MDEV-8289: Semijoin inflates number of rows in query result - Make semi-join optimizer not to choose LooseScan when 1) the index is not covered and 2) full index scan will be required. - Make sure that the code in make_join_select() that may change full index scan into a range scan is not invoked when the table uses full scan. --- sql/opt_subselect.h | 13 ++++++++++--- 1 file changed, 10 insertions(+), 3 deletions(-) (limited to 'sql/opt_subselect.h') diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h index 3da94d05521..0fb1a931e36 100644 --- a/sql/opt_subselect.h +++ b/sql/opt_subselect.h @@ -194,8 +194,6 @@ public: PREV_BITS(key_part_map, max_loose_keypart+1) && // (3) !key_uses_partial_cols(s->table->s, key)) { - /* Ok, can use the strategy */ - part1_conds_met= TRUE; if (s->quick && s->quick->index == key && s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE) { @@ -204,6 +202,12 @@ public: } DBUG_PRINT("info", ("Can use LooseScan scan")); + if (found_part & 1) + { + /* Can use LooseScan on ref access if the first key part is bound */ + part1_conds_met= TRUE; + } + /* Check if this is a special case where there are no usable bound IN-equalities, i.e. we have @@ -211,11 +215,13 @@ public: outer_expr IN (SELECT innertbl.key FROM ...) and outer_expr cannot be evaluated yet, so it's actually full - index scan and not a ref access + index scan and not a ref access. + We can do full index scan if it uses index-only. */ if (!(found_part & 1 ) && /* no usable ref access for 1st key part */ s->table->covering_keys.is_set(key)) { + part1_conds_met= TRUE; DBUG_PRINT("info", ("Can use full index scan for LooseScan")); /* Calculate the cost of complete loose index scan. */ @@ -383,6 +389,7 @@ public: bool create_sj_weedout_tmp_table(THD *thd); }; +int setup_semijoin_loosescan(JOIN *join); int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, uint no_jbuf_after); void destroy_sj_tmp_tables(JOIN *join); -- cgit v1.2.1