maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #00758
Rev 2727: MWL#17: Table elimination in file:///home/psergey/dev/maria-5.1-table-elim-r10/
At file:///home/psergey/dev/maria-5.1-table-elim-r10/
------------------------------------------------------------
revno: 2727
revision-id: psergey@xxxxxxxxxxxx-20090816121708-v42h3mehvoy4c7yu
parent: psergey@xxxxxxxxxxxx-20090816091549-da84w3nlmx8prmvm
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
branch nick: maria-5.1-table-elim-r10
timestamp: Sun 2009-08-16 15:17:08 +0300
message:
MWL#17: Table elimination
- Address review feedback: change expression analyzer used to be a copy-paste
of ref analyzer. ref analyzer, besides doing ref analysis, also collected
info about keys which had sargable predicates. We didn't need that part here.
=== modified file 'sql/opt_table_elimination.cc'
--- a/sql/opt_table_elimination.cc 2009-08-16 09:15:49 +0000
+++ b/sql/opt_table_elimination.cc 2009-08-16 12:17:08 +0000
@@ -233,11 +233,10 @@
uint *and_level, Item *cond,
table_map usable_tables);
static
-void add_eq_dep(Table_elimination *te,
- Equality_module **eq_dep, uint and_level,
- Item_func *cond, Field *field,
- bool eq_func, Item **value,
- uint num_values, table_map usable_tables);
+void add_eq_dep(Table_elimination *te, Equality_module **eq_dep,
+ uint and_level,
+ Item_func *cond, Item *left, Item *right,
+ table_map usable_tables);
static
Equality_module *merge_func_deps(Equality_module *start, Equality_module *new_fields,
Equality_module *end, uint and_level);
@@ -314,87 +313,54 @@
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:
- {
- Item **values;
- // BETWEEN, IN, NE
- if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM &&
- !(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
- {
- values= cond_func->arguments()+1;
- if (cond_func->functype() == Item_func::NE_FUNC &&
- cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
- !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
- values--;
- DBUG_ASSERT(cond_func->functype() != Item_func::IN_FUNC ||
- cond_func->argument_count() != 2);
- add_eq_dep(te, fdeps, *and_level, cond_func,
- ((Item_field*)(cond_func->key_item()->real_item()))->field,
- 0, values,
- cond_func->argument_count()-1,
- usable_tables);
- }
- if (cond_func->functype() == Item_func::BETWEEN)
- {
- values= cond_func->arguments();
- for (uint i= 1 ; i < cond_func->argument_count() ; i++)
- {
- Item_field *field_item;
- if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM
- &&
- !(cond_func->arguments()[i]->used_tables() & OUTER_REF_TABLE_BIT))
- {
- field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
- add_eq_dep(te, fdeps, *and_level, cond_func,
- field_item->field, 0, values, 1, usable_tables);
- }
- }
- }
- break;
- }
- case Item_func::OPTIMIZE_OP:
- {
- bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC ||
- cond_func->functype() == Item_func::EQUAL_FUNC);
+ Item **args= cond_func->arguments();
+ Item *fld;
- if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
- !(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
- {
- add_eq_dep(te, fdeps, *and_level, cond_func,
- ((Item_field*)(cond_func->arguments()[0])->real_item())->field,
- equal_func,
- cond_func->arguments()+1, 1, usable_tables);
- }
- if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
- cond_func->functype() != Item_func::LIKE_FUNC &&
- !(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT))
- {
- add_eq_dep(te, fdeps, *and_level, cond_func,
- ((Item_field*)(cond_func->arguments()[1])->real_item())->field,
- equal_func,
- cond_func->arguments(),1,usable_tables);
- }
- break;
- }
- case Item_func::OPTIMIZE_NULL:
- /* column_name IS [NOT] NULL */
- if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
- !(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
- {
- Item *tmp=new Item_null;
- if (unlikely(!tmp)) // Should never be true
- return;
- add_eq_dep(te, fdeps, *and_level, cond_func,
- ((Item_field*)(cond_func->arguments()[0])->real_item())->field,
- cond_func->functype() == Item_func::ISNULL_FUNC,
- &tmp, 1, usable_tables);
- }
- break;
- case Item_func::OPTIMIZE_EQUAL:
+ switch (cond_func->functype()) {
+ case Item_func::IN_FUNC:
+ {
+ if (cond_func->argument_count() == 2)
+ {
+ add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1],
+ usable_tables);
+ add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0],
+ usable_tables);
+ }
+ }
+ case Item_func::BETWEEN:
+ {
+ if (!((Item_func_between*)cond)->negated &&
+ args[1]->eq(args[2], ((Item_field*)fld)->field->binary()))
+ {
+ add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1],
+ usable_tables);
+ add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0],
+ usable_tables);
+ }
+ break;
+ }
+ case Item_func::EQ_FUNC:
+ case Item_func::EQUAL_FUNC:
+ {
+ add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1],
+ usable_tables);
+ add_eq_dep(te, fdeps, *and_level, cond_func, args[1], args[0],
+ usable_tables);
+ break;
+ }
+ case Item_func::ISNULL_FUNC:
+ {
+ Item *tmp=new Item_null;
+ if (unlikely(!tmp)) // Should never be true
+ return;
+ add_eq_dep(te, fdeps, *and_level, cond_func, args[0], args[1],
+ usable_tables);
+ break;
+ }
+ case Item_func::MULT_EQUAL_FUNC:
+ {
Item_equal *item_equal= (Item_equal *) cond;
Item *const_item= item_equal->get_const();
Item_equal_iterator it(*item_equal);
@@ -408,8 +374,8 @@
*/
while ((item= it++))
{
- add_eq_dep(te, fdeps, *and_level, cond_func, item->field,
- TRUE, &const_item, 1, usable_tables);
+ add_eq_dep(te, fdeps, *and_level, cond_func, item, const_item,
+ usable_tables);
}
}
else
@@ -424,12 +390,13 @@
while ((item= fi++))
{
Field *field= item->field;
- while ((item= it++))
+ Item_field *item2;
+ while ((item2= it++))
{
- if (!field->eq(item->field))
+ if (!field->eq(item2->field))
{
- add_eq_dep(te, fdeps, *and_level, cond_func, field,
- TRUE, (Item **) &item, 1, usable_tables);
+ add_eq_dep(te, fdeps, *and_level, cond_func, item, item2,
+ usable_tables);
}
}
it.rewind();
@@ -437,6 +404,9 @@
}
break;
}
+ default:
+ break;
+ }
}
@@ -567,75 +537,40 @@
static
void add_eq_dep(Table_elimination *te, Equality_module **eq_dep,
- uint and_level, Item_func *cond, Field *field,
- bool eq_func, Item **value, uint num_values,
- table_map usable_tables)
+ uint and_level, Item_func *cond,
+ Item *left, Item *right, table_map usable_tables)
{
- if (!(field->table->map & usable_tables))
- return;
-
- for (uint i=0; i<num_values; i++)
- {
- if ((value[i])->used_tables() & RAND_TABLE_BIT)
- return;
- }
-
- /*
- Save the following cases:
- Field op constant
- Field LIKE constant where constant doesn't start with a wildcard
- Field = field2 where field2 is in a different table
- Field op formula
- Field IS NULL
- Field IS NOT NULL
- Field BETWEEN ...
- Field IN ...
- */
-
- /*
- We can't always use indexes when comparing a string index to a
- number. cmp_type() is checked to allow compare of dates to numbers.
- eq_func is NEVER true when num_values > 1
- */
- if (!eq_func)
- {
- /*
- Additional optimization: if we're processing "t.key BETWEEN c1 AND c1"
- then proceed as if we were processing "t.key = c1".
- */
- if ((cond->functype() != Item_func::BETWEEN) ||
- ((Item_func_between*) cond)->negated ||
- !value[0]->eq(value[1], field->binary()))
- return;
- eq_func= TRUE;
- }
-
- if (field->result_type() == STRING_RESULT)
- {
- if ((*value)->result_type() != STRING_RESULT)
- {
- if (field->cmp_type() != (*value)->result_type())
- return;
- }
- else
- {
- /*
- We can't use indexes if the effective collation
- of the operation differ from the field collation.
- */
- if (field->cmp_type() == STRING_RESULT &&
- ((Field_str*)field)->charset() != cond->compare_collation())
- return;
- }
- }
-
- DBUG_ASSERT(eq_func);
- /* Store possible eq field */
- (*eq_dep)->type= Module_dep::MODULE_EXPRESSION; //psergey-todo;
- (*eq_dep)->field= get_field_value(te, field);
- (*eq_dep)->expression= *value;
- (*eq_dep)->level= and_level;
- (*eq_dep)++;
+ if ((left->used_tables() & usable_tables) &&
+ !(right->used_tables() & RAND_TABLE_BIT) &&
+ left->real_item()->type() == Item::FIELD_ITEM)
+ {
+ Field *field= ((Item_field*)left->real_item())->field;
+ if (field->result_type() == STRING_RESULT)
+ {
+ if (right->result_type() != STRING_RESULT)
+ {
+ if (field->cmp_type() != right->result_type())
+ return;
+ }
+ else
+ {
+ /*
+ We can't use indexes if the effective collation
+ of the operation differ from the field collation.
+ */
+ if (field->cmp_type() == STRING_RESULT &&
+ ((Field_str*)field)->charset() != cond->compare_collation())
+ return;
+ }
+ }
+
+ /* Store possible eq field */
+ (*eq_dep)->type= Module_dep::MODULE_EXPRESSION; //psergey-todo;
+ (*eq_dep)->field= get_field_value(te, field);
+ (*eq_dep)->expression= right;
+ (*eq_dep)->level= and_level;
+ (*eq_dep)++;
+ }
}
@@ -1150,6 +1085,12 @@
{
Outer_join_module *outer_join_dep= (Outer_join_module*)bound_modules;
mark_as_eliminated(te->join, outer_join_dep->table_list);
+ if (!--te->n_outer_joins)
+ {
+ DBUG_PRINT("info", ("Table elimination eliminated everything"
+ " it theoretically could"));
+ return;
+ }
break;
}
case Module_dep::MODULE_MULTI_EQUALITY:
