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/