maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08582
Re: Step#6: MDEV-7950 Item_func::type() takes 0.26% in OLTP RO
Hi Sergei,
Sending an updated version, according to our discussion on the phone.
On 05/19/2015 01:49 PM, Sergey Vojtovich wrote:
Hi Alexander,
looks good, just a few minor suggestions inline.
Some measures on my computer:
add_key_fields 0.20% -> out of radar
Item_func_between::add_key_fields -> 0.05%
Item_equal::add_key_fields -> 0.03%
Item_equal::select_optimize 0.01% -> out of radar
Item_func_between::select_optimize 0.00% -> out of radar
Item_func_eq::functype 0.04% -> 0.03%
Item_equal::functype 0.04% -> 0.02%
Item_func_between::functype 0.03% -> 0.02%
Item_func::functype 0.00% -> 0.00%
Item_func::type 0.14% -> 0.11%
Item_field::type 0.08% -> 0.08%
Item_int::type 0.04% -> 0.03%
Item_sum::type 0.02% -> 0.01%
Total saving: 0.22%
On Fri, May 15, 2015 at 06:58:38PM +0400, Alexander Barkov wrote:
Hi Sergey,
Please review the next iteration for MDEV-7950.
This one splits the function add_key_fields() into a method in Item.
This change removes about 3 virtual calls item->type(), as well as some
virtual calls item_func->functype(), and adds one virtual call
item->add_key_fields() instead.
Thanks.
...skip...
diff --git a/sql/item_geofunc.h b/sql/item_geofunc.h
index 2dbb2cd..b8cbed6 100644
--- a/sql/item_geofunc.h
+++ b/sql/item_geofunc.h
@@ -404,7 +404,6 @@ class Item_func_isempty: public Item_bool_func
public:
Item_func_isempty(Item *a): Item_bool_func(a) {}
longlong val_int();
- optimize_type select_optimize() const { return OPTIMIZE_NONE; }
const char *func_name() const { return "st_isempty"; }
void fix_length_and_dec() { maybe_null= 1; }
};
Hmm... does select_optimize() make sense at all now? I mean won't code become
simpler if we move it's logic to appropriate callers?
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 83bf487..947592a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4602,240 +4603,283 @@ is_local_field (Item *field)
...skip...
+void
+Item_bool_func::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
+ /* If item is of type 'field op field/constant' add it to key_fields */
+ switch (select_optimize()) {
+ case Item_func::OPTIMIZE_NONE:
+ break;
case Item_func::OPTIMIZE_OP:
{
- bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC ||
- cond_func->functype() == Item_func::EQUAL_FUNC);
+ bool equal_func= (functype() == Item_func::EQ_FUNC ||
+ functype() == Item_func::EQUAL_FUNC);
- if (is_local_field (cond_func->arguments()[0]))
+ if (is_local_field(args[0]))
{
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- (Item_field*) (cond_func->arguments()[0])->
- real_item(),
- equal_func,
- cond_func->arguments()+1, 1, usable_tables,
- sargables);
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ (Item_field*) args[0]->real_item(), equal_func,
+ args + 1, 1, usable_tables, sargables);
}
- if (is_local_field (cond_func->arguments()[1]) &&
- cond_func->functype() != Item_func::LIKE_FUNC)
+ if (is_local_field(args[1]))
{
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- (Item_field*) (cond_func->arguments()[1])->
- real_item(),
- equal_func,
- cond_func->arguments(),1,usable_tables,
- sargables);
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ (Item_field*) args[1]->real_item(), equal_func,
+ args, 1, usable_tables, sargables);
}
break;
}
+ case Item_func::OPTIMIZE_KEY:
case Item_func::OPTIMIZE_NULL:
- /* column_name IS [NOT] NULL */
- if (is_local_field (cond_func->arguments()[0]) &&
- !(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
- {
- Item *tmp=new Item_null;
- if (unlikely(!tmp)) // Should never be true
- return;
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- (Item_field*) (cond_func->arguments()[0])->
- real_item(),
- cond_func->functype() == Item_func::ISNULL_FUNC,
- &tmp, 1, usable_tables, sargables);
- }
- break;
case Item_func::OPTIMIZE_EQUAL:
- Item_equal *item_equal= (Item_equal *) cond;
- Item *const_item= item_equal->get_const();
- Item_equal_fields_iterator it(*item_equal);
- if (const_item)
+ DBUG_ASSERT(0);
+ break;
+ }
+}
+
+
I'd better do:
if (select_optimize() == Item_func::OPTIMIZE_OP)
{
...
return;
}
DBUG_ASSERT(select_optimize() == Item_func::OPTIMIZE_NONE);
Also OPTIMIZE_OP is only set by Item_bool_func2 and Item_func_spatial_rel. Why
not to create methods for them instead?
Thanks,
Sergey
diff --git a/sql/item.h b/sql/item.h
index a02a4e2..56b2edc 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -59,6 +59,9 @@ struct TABLE_LIST;
void item_init(void); /* Init item functions */
class Item_field;
class user_var_entry;
+class JOIN;
+struct KEY_FIELD;
+struct SARGABLE_PARAM;
static inline uint32
@@ -1137,6 +1140,13 @@ class Item: public Type_std_attributes
}
virtual COND *remove_eq_conds(THD *thd, Item::cond_result *cond_value,
bool top_level);
+ virtual void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level,
+ table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+ {
+ return;
+ }
/*
Checks whether the item is:
- a simple equality (field=field_item or field=constant_item), or
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 11358ca..03bf6c3 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -122,6 +122,10 @@ class Arg_comparator: public Sql_alloc
class Item_bool_func :public Item_int_func
{
+protected:
+ void add_key_fields_optimize_op(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables, bool equal_func);
public:
Item_bool_func() :Item_int_func() {}
Item_bool_func(Item *a) :Item_int_func(a) {}
@@ -434,6 +438,13 @@ class Item_bool_rowready_func2 :public Item_bool_func2
return min_max_arg_item->field->can_optimize_group_min_max(this,
const_item);
}
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+ {
+ return add_key_fields_optimize_op(join, key_fields, and_level,
+ usable_tables, sargables, false);
+ }
};
/**
@@ -512,6 +523,9 @@ class Item_func_trig_cond: public Item_bool_func
const char *func_name() const { return "trigcond"; };
bool const_item() const { return FALSE; }
bool *get_trig_var() { return trig_var; }
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables);
};
class Item_func_not_all :public Item_func_not
@@ -567,6 +581,13 @@ class Item_func_eq :public Item_bool_rowready_func2
COND *build_equal_items(THD *thd, COND_EQUAL *inherited,
bool link_item_fields,
COND_EQUAL **cond_equal_ref);
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+ {
+ return add_key_fields_optimize_op(join, key_fields, and_level,
+ usable_tables, sargables, true);
+ }
bool check_equality(THD *thd, COND_EQUAL *cond, List<Item> *eq_list);
/*
- If this equality is created from the subquery's IN-equality:
@@ -591,6 +612,13 @@ class Item_func_equal :public Item_bool_rowready_func2
cond_result eq_cmp_result() const { return COND_TRUE; }
const char *func_name() const { return "<=>"; }
Item *neg_transformer(THD *thd) { return 0; }
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+ {
+ return add_key_fields_optimize_op(join, key_fields, and_level,
+ usable_tables, sargables, true);
+ }
};
@@ -656,6 +684,8 @@ class Item_func_ne :public Item_bool_rowready_func2
optimize_type select_optimize() const { return OPTIMIZE_KEY; }
const char *func_name() const { return "<>"; }
Item *negated_item();
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
+ table_map usable_tables, SARGABLE_PARAM **sargables);
};
@@ -712,6 +742,9 @@ class Item_func_between :public Item_func_opt_neg
bool eval_not_null_tables(uchar *opt_arg);
void fix_after_pullout(st_select_lex *new_parent, Item **ref);
bool count_sargable_conds(uchar *arg);
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables);
};
@@ -1392,6 +1425,8 @@ class Item_func_in :public Item_func_opt_neg
}
optimize_type select_optimize() const
{ return OPTIMIZE_KEY; }
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
+ table_map usable_tables, SARGABLE_PARAM **sargables);
virtual void print(String *str, enum_query_type query_type);
enum Functype functype() const { return IN_FUNC; }
const char *func_name() const { return " IN "; }
@@ -1436,6 +1471,8 @@ class Item_func_null_predicate :public Item_bool_func
public:
Item_func_null_predicate(Item *a) :Item_bool_func(a) { sargable= true; }
optimize_type select_optimize() const { return OPTIMIZE_NULL; }
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
+ table_map usable_tables, SARGABLE_PARAM **sargables);
CHARSET_INFO *compare_collation() const
{ return args[0]->collation.collation; }
void fix_length_and_dec() { decimals=0; max_length=1; maybe_null=0; }
@@ -1587,6 +1624,8 @@ class Item_func_like :public Item_bool_func2
*/
return compare_collation() == &my_charset_bin ? COND_TRUE : COND_OK;
}
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
+ table_map usable_tables, SARGABLE_PARAM **sargables);
const char *func_name() const { return "like"; }
bool fix_fields(THD *thd, Item **ref);
void cleanup();
@@ -1781,6 +1820,9 @@ class Item_cond :public Item_bool_func
COND_EQUAL **cond_equal_ref);
COND *remove_eq_conds(THD *thd, Item::cond_result *cond_value,
bool top_level);
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables);
virtual void print(String *str, enum_query_type query_type);
void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields);
friend int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves,
@@ -1971,6 +2013,9 @@ class Item_equal: public Item_bool_func
COND *build_equal_items(THD *thd, COND_EQUAL *inherited,
bool link_item_fields,
COND_EQUAL **cond_equal_ref);
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables);
bool walk(Item_processor processor, bool walk_subquery, uchar *arg);
Item *transform(Item_transformer transformer, uchar *arg);
virtual void print(String *str, enum_query_type query_type);
@@ -2125,6 +2170,8 @@ class Item_cond_and :public Item_cond
COND *build_equal_items(THD *thd, COND_EQUAL *inherited,
bool link_item_fields,
COND_EQUAL **cond_equal_ref);
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
+ table_map usable_tables, SARGABLE_PARAM **sargables);
};
inline bool is_cond_and(Item *item)
diff --git a/sql/item_geofunc.h b/sql/item_geofunc.h
index 2dbb2cd..97001af 100644
--- a/sql/item_geofunc.h
+++ b/sql/item_geofunc.h
@@ -288,6 +288,13 @@ class Item_func_spatial_rel: public Item_bool_func
enum Functype rev_functype() const { return spatial_rel; }
bool is_null() { (void) val_int(); return null_value; }
optimize_type select_optimize() const { return OPTIMIZE_OP; }
+ void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+ {
+ return add_key_fields_optimize_op(join, key_fields, and_level,
+ usable_tables, sargables, false);
+ }
};
@@ -404,7 +411,6 @@ class Item_func_isempty: public Item_bool_func
public:
Item_func_isempty(Item *a): Item_bool_func(a) {}
longlong val_int();
- optimize_type select_optimize() const { return OPTIMIZE_NONE; }
const char *func_name() const { return "st_isempty"; }
void fix_length_and_dec() { maybe_null= 1; }
};
@@ -418,7 +424,6 @@ class Item_func_issimple: public Item_bool_func
public:
Item_func_issimple(Item *a): Item_bool_func(a) {}
longlong val_int();
- optimize_type select_optimize() const { return OPTIMIZE_NONE; }
const char *func_name() const { return "st_issimple"; }
void fix_length_and_dec() { maybe_null= 1; }
};
@@ -428,7 +433,6 @@ class Item_func_isclosed: public Item_bool_func
public:
Item_func_isclosed(Item *a): Item_bool_func(a) {}
longlong val_int();
- optimize_type select_optimize() const { return OPTIMIZE_NONE; }
const char *func_name() const { return "st_isclosed"; }
void fix_length_and_dec() { maybe_null= 1; }
};
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 83bf487..dccad7a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -75,7 +75,7 @@ static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
JOIN_TAB *join_tab,
uint tables, COND *conds,
table_map table_map, SELECT_LEX *select_lex,
- st_sargable_param **sargables);
+ SARGABLE_PARAM **sargables);
static bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse,
bool skip_unprefixed_keyparts);
static int sort_keyuse(KEYUSE *a,KEYUSE *b);
@@ -3381,12 +3381,12 @@ static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select,
We form a bitmap of indexes that can be used for sargable predicates.
Only such indexes are involved in range analysis.
*/
-typedef struct st_sargable_param
+struct SARGABLE_PARAM
{
Field *field; /* field against which to check sargability */
Item **arg_value; /* values of potential keys for lookups */
uint num_values; /* number of values in the above array */
-} SARGABLE_PARAM;
+};
/**
@@ -4127,7 +4127,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
*****************************************************************************/
/// Used when finding key fields
-typedef struct key_field_t {
+struct KEY_FIELD {
Field *field;
Item_func *cond;
Item *val; ///< May be empty if diff constant
@@ -4141,7 +4141,8 @@ typedef struct key_field_t {
bool null_rejecting;
bool *cond_guard; /* See KEYUSE::cond_guard */
uint sj_pred_no; /* See KEYUSE::sj_pred_no */
-} KEY_FIELD;
+};
+
/**
Merge new key definitions to old ones, remove those not used in both.
@@ -4602,240 +4603,269 @@ is_local_field (Item *field)
operation
*/
-static void
-add_key_fields(JOIN *join, KEY_FIELD **key_fields, uint *and_level,
- COND *cond, table_map usable_tables,
- SARGABLE_PARAM **sargables)
+
+void
+Item_cond_and::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
{
- if (cond->type() == Item_func::COND_ITEM)
- {
- List_iterator_fast<Item> li(*((Item_cond*) cond)->argument_list());
- KEY_FIELD *org_key_fields= *key_fields;
+ List_iterator_fast<Item> li(*argument_list());
+ KEY_FIELD *org_key_fields= *key_fields;
- if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
- {
- Item *item;
- while ((item=li++))
- add_key_fields(join, key_fields, and_level, item, usable_tables,
- sargables);
- for (; org_key_fields != *key_fields ; org_key_fields++)
- org_key_fields->level= *and_level;
- }
- else
- {
- (*and_level)++;
- add_key_fields(join, key_fields, and_level, li++, usable_tables,
- sargables);
- Item *item;
- while ((item=li++))
- {
- KEY_FIELD *start_key_fields= *key_fields;
- (*and_level)++;
- add_key_fields(join, key_fields, and_level, item, usable_tables,
- sargables);
- *key_fields=merge_key_fields(org_key_fields,start_key_fields,
- *key_fields,++(*and_level));
- }
- }
- return;
+ Item *item;
+ while ((item=li++))
+ item->add_key_fields(join, key_fields, and_level, usable_tables,
+ sargables);
+ for (; org_key_fields != *key_fields ; org_key_fields++)
+ org_key_fields->level= *and_level;
+}
+
+
+void
+Item_cond::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
+ List_iterator_fast<Item> li(*argument_list());
+ KEY_FIELD *org_key_fields= *key_fields;
+
+ (*and_level)++;
+ (li++)->add_key_fields(join, key_fields, and_level, usable_tables,
+ sargables);
+ Item *item;
+ while ((item=li++))
+ {
+ KEY_FIELD *start_key_fields= *key_fields;
+ (*and_level)++;
+ item->add_key_fields(join, key_fields, and_level, usable_tables,
+ sargables);
+ *key_fields= merge_key_fields(org_key_fields,start_key_fields,
+ *key_fields, ++(*and_level));
}
+}
+
+void
+Item_func_trig_cond::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
/*
Subquery optimization: Conditions that are pushed down into subqueries
are wrapped into Item_func_trig_cond. We process the wrapped condition
but need to set cond_guard for KEYUSE elements generated from it.
*/
+ if (!join->group_list && !join->order &&
+ join->unit->item &&
+ join->unit->item->substype() == Item_subselect::IN_SUBS &&
+ !join->unit->is_union())
{
- if (cond->type() == Item::FUNC_ITEM &&
- ((Item_func*)cond)->functype() == Item_func::TRIG_COND_FUNC)
+ KEY_FIELD *save= *key_fields;
+ args[0]->add_key_fields(join, key_fields, and_level, usable_tables,
+ sargables);
+ // Indicate that this ref access candidate is for subquery lookup:
+ for (; save != *key_fields; save++)
+ save->cond_guard= get_trig_var();
+ }
+}
+
+
+void
+Item_func_between::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
+ /*
+ Build list of possible keys for 'a BETWEEN low AND high'.
+ It is handled similar to the equivalent condition
+ 'a >= low AND a <= high':
+ */
+ Item_field *field_item;
+ bool equal_func= false;
+ uint num_values= 2;
+
+ bool binary_cmp= (args[0]->real_item()->type() == Item::FIELD_ITEM)
+ ? ((Item_field*) args[0]->real_item())->field->binary()
+ : true;
+ /*
+ Additional optimization: If 'low = high':
+ Handle as if the condition was "t.key = low".
+ */
+ if (!negated && args[1]->eq(args[2], binary_cmp))
+ {
+ equal_func= true;
+ num_values= 1;
+ }
+
+ /*
+ Append keys for 'field <cmp> value[]' if the
+ condition is of the form::
+ '<field> BETWEEN value[1] AND value[2]'
+ */
+ if (is_local_field(args[0]))
+ {
+ field_item= (Item_field *) (args[0]->real_item());
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ field_item, equal_func, &args[1],
+ num_values, usable_tables, sargables);
+ }
+ /*
+ Append keys for 'value[0] <cmp> field' if the
+ condition is of the form:
+ 'value[0] BETWEEN field1 AND field2'
+ */
+ for (uint i= 1; i <= num_values; i++)
+ {
+ if (is_local_field(args[i]))
{
- Item *cond_arg= ((Item_func*)cond)->arguments()[0];
- if (!join->group_list && !join->order &&
- join->unit->item &&
- join->unit->item->substype() == Item_subselect::IN_SUBS &&
- !join->unit->is_union())
- {
- KEY_FIELD *save= *key_fields;
- add_key_fields(join, key_fields, and_level, cond_arg, usable_tables,
- sargables);
- // Indicate that this ref access candidate is for subquery lookup:
- for (; save != *key_fields; save++)
- save->cond_guard= ((Item_func_trig_cond*)cond)->get_trig_var();
- }
- return;
+ field_item= (Item_field *) (args[i]->real_item());
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ field_item, equal_func, args,
+ 1, usable_tables, sargables);
}
}
+}
- /* If item is of type 'field op field/constant' add it to key_fields */
- if (cond->type() != Item::FUNC_ITEM)
- return;
- Item_func *cond_func= (Item_func*) cond;
- switch (cond_func->select_optimize()) {
- case Item_func::OPTIMIZE_NONE:
- break;
- case Item_func::OPTIMIZE_KEY:
+
+void
+Item_func_in::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
+ if (is_local_field(args[0]) && !(used_tables() & OUTER_REF_TABLE_BIT))
+ {
+ DBUG_ASSERT(arg_count != 2);
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ (Item_field*) (args[0]->real_item()), false,
+ args + 1, arg_count - 1, usable_tables, sargables);
+ }
+}
+
+
+void
+Item_func_ne::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
+ if (is_local_field(args[0]) && !(used_tables() & OUTER_REF_TABLE_BIT))
+ {
+ Item **values= is_local_field(args[1]) ? args : args + 1;
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ (Item_field*) (args[0]->real_item()), false,
+ values, 1, usable_tables, sargables);
+ }
+}
+
+
+void
+Item_func_like::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
+ if (is_local_field(args[0]) &&
+ Item_func_like::select_optimize() == OPTIMIZE_OP)
{
- Item **values;
/*
- Build list of possible keys for 'a BETWEEN low AND high'.
- It is handled similar to the equivalent condition
- 'a >= low AND a <= high':
+ SELECT * FROM t1 WHERE field LIKE const_pattern
+ const_pattern starts with a non-wildcard character
*/
- if (cond_func->functype() == Item_func::BETWEEN)
- {
- Item_field *field_item;
- bool equal_func= FALSE;
- uint num_values= 2;
- values= cond_func->arguments();
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ (Item_field*) args[0]->real_item(), false,
+ args + 1, 1, usable_tables, sargables);
+ }
+}
- bool binary_cmp= (values[0]->real_item()->type() == Item::FIELD_ITEM)
- ? ((Item_field*)values[0]->real_item())->field->binary()
- : TRUE;
- /*
- Additional optimization: If 'low = high':
- Handle as if the condition was "t.key = low".
- */
- if (!((Item_func_between*)cond_func)->negated &&
- values[1]->eq(values[2], binary_cmp))
- {
- equal_func= TRUE;
- num_values= 1;
- }
+void
+Item_bool_func::add_key_fields_optimize_op(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level,
+ table_map usable_tables,
+ SARGABLE_PARAM **sargables,
+ bool equal_func)
+{
+ /* If item is of type 'field op field/constant' add it to key_fields */
+ if (is_local_field(args[0]))
+ {
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ (Item_field*) args[0]->real_item(), equal_func,
+ args + 1, 1, usable_tables, sargables);
+ }
+ if (is_local_field(args[1]))
+ {
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ (Item_field*) args[1]->real_item(), equal_func,
+ args, 1, usable_tables, sargables);
+ }
+}
- /*
- Append keys for 'field <cmp> value[]' if the
- condition is of the form::
- '<field> BETWEEN value[1] AND value[2]'
- */
- if (is_local_field(values[0]))
- {
- field_item= (Item_field *) (values[0]->real_item());
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- field_item, equal_func, &values[1],
- num_values, usable_tables, sargables);
- }
- /*
- Append keys for 'value[0] <cmp> field' if the
- condition is of the form:
- 'value[0] BETWEEN field1 AND field2'
- */
- for (uint i= 1; i <= num_values; i++)
- {
- if (is_local_field(values[i]))
- {
- field_item= (Item_field *) (values[i]->real_item());
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- field_item, equal_func, values,
- 1, usable_tables, sargables);
- }
- }
- } // if ( ... Item_func::BETWEEN)
- // IN, NE
- else if (is_local_field (cond_func->key_item()) &&
- !(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
- {
- values= cond_func->arguments()+1;
- if (cond_func->functype() == Item_func::NE_FUNC &&
- is_local_field (cond_func->arguments()[1]))
- values--;
- DBUG_ASSERT(cond_func->functype() != Item_func::IN_FUNC ||
- cond_func->argument_count() != 2);
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- (Item_field*) (cond_func->key_item()->real_item()),
- 0, values,
- cond_func->argument_count()-1,
- usable_tables, sargables);
- }
- break;
- }
- case Item_func::OPTIMIZE_OP:
+void
+Item_func_null_predicate::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level,
+ table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
+ /* column_name IS [NOT] NULL */
+ if (is_local_field(args[0]) && !(used_tables() & OUTER_REF_TABLE_BIT))
{
- bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC ||
- cond_func->functype() == Item_func::EQUAL_FUNC);
+ Item *tmp= new Item_null;
+ if (unlikely(!tmp)) // Should never be true
+ return;
+ add_key_equal_fields(join, key_fields, *and_level, this,
+ (Item_field*) args[0]->real_item(),
+ functype() == Item_func::ISNULL_FUNC,
+ &tmp, 1, usable_tables, sargables);
+ }
+}
- if (is_local_field (cond_func->arguments()[0]))
- {
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- (Item_field*) (cond_func->arguments()[0])->
- real_item(),
- equal_func,
- cond_func->arguments()+1, 1, usable_tables,
- sargables);
- }
- if (is_local_field (cond_func->arguments()[1]) &&
- cond_func->functype() != Item_func::LIKE_FUNC)
+
+void
+Item_equal::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
+ uint *and_level, table_map usable_tables,
+ SARGABLE_PARAM **sargables)
+{
+ Item *const_item= get_const();
+ Item_equal_fields_iterator it(*this);
+ if (const_item)
+ {
+
+ /*
+ For each field field1 from item_equal consider the equality
+ field1=const_item as a condition allowing an index access of the table
+ with field1 by the keys value of field1.
+ */
+ while (it++)
{
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- (Item_field*) (cond_func->arguments()[1])->
- real_item(),
- equal_func,
- cond_func->arguments(),1,usable_tables,
- sargables);
+ Field *equal_field= it.get_curr_field();
+ add_key_field(join, key_fields, *and_level, this, equal_field,
+ TRUE, &const_item, 1, usable_tables, sargables);
}
- break;
}
- case Item_func::OPTIMIZE_NULL:
- /* column_name IS [NOT] NULL */
- if (is_local_field (cond_func->arguments()[0]) &&
- !(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
- {
- Item *tmp=new Item_null;
- if (unlikely(!tmp)) // Should never be true
- return;
- add_key_equal_fields(join, key_fields, *and_level, cond_func,
- (Item_field*) (cond_func->arguments()[0])->
- real_item(),
- cond_func->functype() == Item_func::ISNULL_FUNC,
- &tmp, 1, usable_tables, sargables);
- }
- break;
- case Item_func::OPTIMIZE_EQUAL:
- Item_equal *item_equal= (Item_equal *) cond;
- Item *const_item= item_equal->get_const();
- Item_equal_fields_iterator it(*item_equal);
- if (const_item)
- {
- /*
- For each field field1 from item_equal consider the equality
- field1=const_item as a condition allowing an index access of the table
- with field1 by the keys value of field1.
- */
- while (it++)
+ else
+ {
+ /*
+ Consider all pairs of different fields included into item_equal.
+ For each of them (field1, field1) consider the equality
+ field1=field2 as a condition allowing an index access of the table
+ with field1 by the keys value of field2.
+ */
+ Item_equal_fields_iterator fi(*this);
+ while (fi++)
+ {
+ Field *field= fi.get_curr_field();
+ Item *item;
+ while ((item= it++))
{
Field *equal_field= it.get_curr_field();
- add_key_field(join, key_fields, *and_level, cond_func, equal_field,
- TRUE, &const_item, 1, usable_tables, sargables);
- }
- }
- else
- {
- /*
- Consider all pairs of different fields included into item_equal.
- For each of them (field1, field1) consider the equality
- field1=field2 as a condition allowing an index access of the table
- with field1 by the keys value of field2.
- */
- Item_equal_fields_iterator fi(*item_equal);
- while (fi++)
- {
- Field *field= fi.get_curr_field();
- Item *item;
- while ((item= it++))
+ if (!field->eq(equal_field))
{
- Field *equal_field= it.get_curr_field();
- if (!field->eq(equal_field))
- {
- add_key_field(join, key_fields, *and_level, cond_func, field,
- TRUE, &item, 1, usable_tables,
- sargables);
- }
+ add_key_field(join, key_fields, *and_level, this, field,
+ TRUE, &item, 1, usable_tables,
+ sargables);
}
- it.rewind();
}
+ it.rewind();
}
- break;
}
}
@@ -5120,8 +5150,8 @@ static void add_key_fields_for_nj(JOIN *join, TABLE_LIST *nested_join_table,
tables |= table->table->map;
}
if (nested_join_table->on_expr)
- add_key_fields(join, end, and_level, nested_join_table->on_expr, tables,
- sargables);
+ nested_join_table->on_expr->add_key_fields(join, end, and_level, tables,
+ sargables);
}
@@ -5244,8 +5274,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
if (cond)
{
KEY_FIELD *saved_field= field;
- add_key_fields(join_tab->join, &end, &and_level, cond, normal_tables,
- sargables);
+ cond->add_key_fields(join_tab->join, &end, &and_level, normal_tables,
+ sargables);
for (; field != end ; field++)
{
@@ -5268,9 +5298,10 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
into account as well.
*/
if (*join_tab[i].on_expr_ref)
- add_key_fields(join_tab->join, &end, &and_level,
- *join_tab[i].on_expr_ref,
- join_tab[i].table->map, sargables);
+ (*join_tab[i].on_expr_ref)->add_key_fields(join_tab->join, &end,
+ &and_level,
+ join_tab[i].table->map,
+ sargables);
}
/* Process ON conditions for the nested joins */
References
