sql_optimizer.cc | sql_optimizer.cc | |||
---|---|---|---|---|
skipping to change at line 6376 | skipping to change at line 6376 | |||
{ | { | |||
/* | /* | |||
Remove this assert when we support semijoin on non-IN subqueries. | Remove this assert when we support semijoin on non-IN subqueries. | |||
*/ | */ | |||
DBUG_ASSERT((*el1)->substype() == Item_subselect::IN_SUBS && | DBUG_ASSERT((*el1)->substype() == Item_subselect::IN_SUBS && | |||
(*el2)->substype() == Item_subselect::IN_SUBS); | (*el2)->substype() == Item_subselect::IN_SUBS); | |||
return ((*el1)->sj_convert_priority < (*el2)->sj_convert_priority) ? 1 : | return ((*el1)->sj_convert_priority < (*el2)->sj_convert_priority) ? 1 : | |||
( ((*el1)->sj_convert_priority == (*el2)->sj_convert_priority)? 0 : -1); | ( ((*el1)->sj_convert_priority == (*el2)->sj_convert_priority)? 0 : -1); | |||
} | } | |||
static TABLE_LIST *alloc_join_nest(THD *thd) | ||||
{ | ||||
TABLE_LIST *tbl; | ||||
if (!(tbl= (TABLE_LIST*) thd->calloc(ALIGN_SIZE(sizeof(TABLE_LIST))+ | ||||
sizeof(NESTED_JOIN)))) | ||||
return NULL; | ||||
tbl->nested_join= (NESTED_JOIN*) ((uchar*)tbl + | ||||
ALIGN_SIZE(sizeof(TABLE_LIST))); | ||||
return tbl; | ||||
} | ||||
static void fix_list_after_tbl_changes(st_select_lex *parent_select, | static void fix_list_after_tbl_changes(st_select_lex *parent_select, | |||
st_select_lex *removed_select, | st_select_lex *removed_select, | |||
List<TABLE_LIST> *tlist) | List<TABLE_LIST> *tlist) | |||
{ | { | |||
List_iterator<TABLE_LIST> it(*tlist); | List_iterator<TABLE_LIST> it(*tlist); | |||
TABLE_LIST *table; | TABLE_LIST *table; | |||
while ((table= it++)) | while ((table= it++)) | |||
{ | { | |||
if (table->join_cond()) | if (table->join_cond()) | |||
table->join_cond()->fix_after_pullout(parent_select, removed_select); | table->join_cond()->fix_after_pullout(parent_select, removed_select); | |||
skipping to change at line 6499 | skipping to change at line 6488 | |||
The sj-nest will be tblX's "sibling", i.e. another child of its | The sj-nest will be tblX's "sibling", i.e. another child of its | |||
parent. This is ok because tblX is joined as an inner join. | parent. This is ok because tblX is joined as an inner join. | |||
*/ | */ | |||
emb_tbl_nest= subq_pred->embedding_join_nest->embedding; | emb_tbl_nest= subq_pred->embedding_join_nest->embedding; | |||
if (emb_tbl_nest) | if (emb_tbl_nest) | |||
emb_join_list= &emb_tbl_nest->nested_join->join_list; | emb_join_list= &emb_tbl_nest->nested_join->join_list; | |||
} | } | |||
else if (!subq_pred->embedding_join_nest->nested_join) | else if (!subq_pred->embedding_join_nest->nested_join) | |||
{ | { | |||
TABLE_LIST *outer_tbl= subq_pred->embedding_join_nest; | TABLE_LIST *outer_tbl= subq_pred->embedding_join_nest; | |||
TABLE_LIST *wrap_nest; | ||||
/* | /* | |||
We're dealing with | We're dealing with | |||
... LEFT JOIN tbl ON (on_expr AND subq_pred) ... | ... LEFT JOIN tbl ON (on_expr AND subq_pred) ... | |||
we'll need to convert it into: | we'll need to convert it into: | |||
... LEFT JOIN ( tbl SJ (subq_tables) ) ON (on_expr AND subq_pred) ... | ... LEFT JOIN ( tbl SJ (subq_tables) ) ON (on_expr AND subq_pred) ... | |||
| | | | | | |||
|<----- wrap_nest ---->| | |<----- wrap_nest ---->| | |||
Q: other subqueries may be pointing to this element. What to do? | Q: other subqueries may be pointing to this element. What to do? | |||
A1: simple solution: copy *subq_pred->embedding_join_nest= *parent_ nest. | A1: simple solution: copy *subq_pred->embedding_join_nest= *parent_ nest. | |||
But we'll need to fix other pointers. | But we'll need to fix other pointers. | |||
A2: Another way: have TABLE_LIST::next_ptr so the following | A2: Another way: have TABLE_LIST::next_ptr so the following | |||
subqueries know the table has been nested. | subqueries know the table has been nested. | |||
A3: changes in the TABLE_LIST::outer_join will make everything work | A3: changes in the TABLE_LIST::outer_join will make everything work | |||
automatically. | automatically. | |||
*/ | */ | |||
if (!(wrap_nest= alloc_join_nest(thd))) | TABLE_LIST *const wrap_nest= | |||
{ | TABLE_LIST::new_nested_join(thd->mem_root, "(sj-wrap)", | |||
DBUG_RETURN(TRUE); | outer_tbl->embedding, outer_tbl->join_l | |||
} | ist, | |||
wrap_nest->embedding= outer_tbl->embedding; | parent_lex); | |||
wrap_nest->join_list= outer_tbl->join_list; | if (wrap_nest == NULL) | |||
wrap_nest->alias= (char*) "(sj-wrap)"; | DBUG_RETURN(true); | |||
wrap_nest->nested_join->join_list.empty(); | ||||
wrap_nest->nested_join->join_list.push_back(outer_tbl); | wrap_nest->nested_join->join_list.push_back(outer_tbl); | |||
outer_tbl->embedding= wrap_nest; | outer_tbl->embedding= wrap_nest; | |||
outer_tbl->join_list= &wrap_nest->nested_join->join_list; | outer_tbl->join_list= &wrap_nest->nested_join->join_list; | |||
/* | /* | |||
wrap_nest will take place of outer_tbl, so move the outer join flag | wrap_nest will take place of outer_tbl, so move the outer join flag | |||
and on_expr | and join condition. | |||
*/ | */ | |||
wrap_nest->outer_join= outer_tbl->outer_join; | wrap_nest->outer_join= outer_tbl->outer_join; | |||
outer_tbl->outer_join= 0; | outer_tbl->outer_join= 0; | |||
wrap_nest->set_join_cond(outer_tbl->join_cond()); | wrap_nest->set_join_cond(outer_tbl->join_cond()); | |||
outer_tbl->set_join_cond(NULL); | outer_tbl->set_join_cond(NULL); | |||
List_iterator<TABLE_LIST> li(*wrap_nest->join_list); | List_iterator<TABLE_LIST> li(*wrap_nest->join_list); | |||
TABLE_LIST *tbl; | TABLE_LIST *tbl; | |||
while ((tbl= li++)) | while ((tbl= li++)) | |||
skipping to change at line 6562 | skipping to change at line 6548 | |||
} | } | |||
/* | /* | |||
Ok now wrap_nest 'contains' outer_tbl and we're ready to add the | Ok now wrap_nest 'contains' outer_tbl and we're ready to add the | |||
semi-join nest into it | semi-join nest into it | |||
*/ | */ | |||
emb_join_list= &wrap_nest->nested_join->join_list; | emb_join_list= &wrap_nest->nested_join->join_list; | |||
emb_tbl_nest= wrap_nest; | emb_tbl_nest= wrap_nest; | |||
} | } | |||
} | } | |||
TABLE_LIST *sj_nest; | TABLE_LIST *const sj_nest= | |||
NESTED_JOIN *nested_join; | TABLE_LIST::new_nested_join(thd->mem_root, "(sj-nest)", | |||
if (!(sj_nest= alloc_join_nest(thd))) | emb_tbl_nest, emb_join_list, parent_lex); | |||
{ | if (sj_nest == NULL) | |||
DBUG_RETURN(TRUE); | DBUG_RETURN(true); | |||
} | ||||
nested_join= sj_nest->nested_join; | NESTED_JOIN *const nested_join= sj_nest->nested_join; | |||
sj_nest->join_list= emb_join_list; | ||||
sj_nest->embedding= emb_tbl_nest; | ||||
sj_nest->alias= (char*) "(sj-nest)"; | ||||
/* Nests do not participate in those 'chains', so: */ | /* Nests do not participate in those 'chains', so: */ | |||
/* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL* / | /* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL* / | |||
emb_join_list->push_back(sj_nest); | emb_join_list->push_back(sj_nest); | |||
/* | /* | |||
nested_join->used_tables and nested_join->not_null_tables are | nested_join->used_tables and nested_join->not_null_tables are | |||
initialized in simplify_joins(). | initialized in simplify_joins(). | |||
*/ | */ | |||
/* | /* | |||
skipping to change at line 7493 | skipping to change at line 7476 | |||
/* | /* | |||
first_inner is the X in queries like: | first_inner is the X in queries like: | |||
SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON X | SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON X | |||
*/ | */ | |||
JOIN_TAB *const first_inner_tab= tab->first_inner; | JOIN_TAB *const first_inner_tab= tab->first_inner; | |||
const table_map used_tables= tab->prefix_tables(); | const table_map used_tables= tab->prefix_tables(); | |||
const table_map current_map= tab->added_tables(); | const table_map current_map= tab->added_tables(); | |||
bool use_quick_range=0; | bool use_quick_range=0; | |||
Item *tmp; | Item *tmp; | |||
if (tab->type == JT_REF && tab->quick && | /* | |||
(uint) tab->ref.key == tab->quick->index && | Heuristic: Switch from 'ref' to 'range' access if 'range' | |||
tab->ref.key_length < tab->quick->max_used_key_length) | access can utilize more keyparts than 'ref' access. Conditions | |||
for doing switching: | ||||
1) Current decision is to use 'ref' access | ||||
2) 'ref' access depends on a constant, not a value read from a | ||||
table earlier in the join sequence. | ||||
Rationale: if 'ref' depends on a value from another table, | ||||
the join condition is not used to limit the rows read by | ||||
'range' access (that would require dynamic range - 'Range | ||||
checked for each record'). In other words, if 'ref' depends | ||||
on a value from another table, we have a query with | ||||
conditions of the form | ||||
this_table.idx_col1 = other_table.col AND <<- used by 'ref' | ||||
this_table.idx_col1 OP <const> AND <<- used by 'range' | ||||
this_table.idx_col2 OP <const> AND ... <<- used by 'range' | ||||
and an index on (idx_col1,idx_col2,...). But the fact that | ||||
'range' access uses more keyparts does not mean that it is | ||||
more selective than 'ref' access because these access types | ||||
utilize different parts of the query condition. We | ||||
therefore trust the cost based choice made by | ||||
best_access_path() instead of forcing a heuristic choice | ||||
here. | ||||
3) Range access is possible, and it is less costly than | ||||
table/index scan | ||||
4) 'ref' access and 'range' access uses the same index | ||||
5) 'range' access uses more keyparts than 'ref' access | ||||
@todo: This decision should rather be made in best_access_path() | ||||
*/ | ||||
if (tab->type == JT_REF && // 1) | ||||
!tab->ref.depend_map && // 2) | ||||
tab->quick && // 3) | ||||
(uint) tab->ref.key == tab->quick->index && // 4) | ||||
tab->ref.key_length < tab->quick->max_used_key_length) // 5) | ||||
{ | { | |||
/* | ||||
Range uses longer key; Use this instead of ref on key | ||||
Todo: This decision should rather be made in | ||||
best_access_path() | ||||
*/ | ||||
Opt_trace_object wrapper(trace); | Opt_trace_object wrapper(trace); | |||
Opt_trace_object (trace, "access_type_changed"). | Opt_trace_object (trace, "access_type_changed"). | |||
add_utf8_table(tab->table). | add_utf8_table(tab->table). | |||
add_utf8("index", tab->table->key_info[tab->ref.key].name). | add_utf8("index", tab->table->key_info[tab->ref.key].name). | |||
add_alnum("old_type", "ref"). | add_alnum("old_type", "ref"). | |||
add_alnum("new_type", "range"). | add_alnum("new_type", "range"). | |||
add_alnum("cause", "uses_more_keyparts"); | add_alnum("cause", "uses_more_keyparts"); | |||
tab->type=JT_ALL; | tab->type=JT_ALL; | |||
use_quick_range=1; | use_quick_range=1; | |||
skipping to change at line 7721 | skipping to change at line 7734 | |||
tmp_order ; | tmp_order ; | |||
tmp_order=tmp_order->next) | tmp_order=tmp_order->next) | |||
{ | { | |||
Item *item= (*tmp_order->item)->real_item(); | Item *item= (*tmp_order->item)->real_item(); | |||
if (item->type() != Item::FIELD_ITEM) | if (item->type() != Item::FIELD_ITEM) | |||
{ | { | |||
recheck_reason= DONT_RECHECK; | recheck_reason= DONT_RECHECK; | |||
break; | break; | |||
} | } | |||
/* | ||||
No index can provide the necessary order if ordering | ||||
on fields that do not belong to 'tab' (the first | ||||
non-const table) | ||||
*/ | ||||
Item_field *fld_item= static_cast<Item_field*>(item); | ||||
if (fld_item->field->table != tab->table) | ||||
{ | ||||
recheck_reason= DONT_RECHECK; | ||||
break; | ||||
} | ||||
if ((interesting_order != ORDER::ORDER_NOT_RELEVANT) && | if ((interesting_order != ORDER::ORDER_NOT_RELEVANT) && | |||
(interesting_order != tmp_order->direction)) | (interesting_order != tmp_order->direction)) | |||
{ | { | |||
/* | /* | |||
MySQL currently does not support multi-column | MySQL currently does not support multi-column | |||
indexes with a mix of ASC and DESC ordering, so if | indexes with a mix of ASC and DESC ordering, so if | |||
ORDER BY contains both, no index can provide | ORDER BY contains both, no index can provide | |||
correct order. | correct order. | |||
*/ | */ | |||
recheck_reason= DONT_RECHECK; | recheck_reason= DONT_RECHECK; | |||
break; | break; | |||
} | } | |||
usable_keys.intersect(((Item_field*)item)->field->part_of_s ortkey); | usable_keys.intersect(fld_item->field->part_of_sortkey); | |||
interesting_order= tmp_order->direction; | interesting_order= tmp_order->direction; | |||
if (usable_keys.is_clear_all()) | if (usable_keys.is_clear_all()) | |||
{ | { | |||
// No usable keys | // No usable keys | |||
recheck_reason= DONT_RECHECK; | recheck_reason= DONT_RECHECK; | |||
break; | break; | |||
} | } | |||
} | } | |||
/* | /* | |||
End of changes. 11 change blocks. | ||||
41 lines changed or deleted | 67 lines changed or added | |||
This html diff was produced by rfcdiff 1.41. The latest version is available from http://tools.ietf.org/tools/rfcdiff/ |