447
438
return(join->error);
441
int subq_sj_candidate_cmp(Item_in_subselect* const *el1,
442
Item_in_subselect* const *el2)
444
return ((*el1)->sj_convert_priority < (*el2)->sj_convert_priority) ? 1 :
445
( ((*el1)->sj_convert_priority == (*el2)->sj_convert_priority)? 0 : -1);
450
448
inline Item *and_items(Item* cond, Item *item)
452
450
return (cond? (new Item_cond_and(cond, item)) : item);
453
static TableList *alloc_join_nest(Session *session)
456
if (!(tbl= (TableList*) session->calloc(ALIGN_SIZE(sizeof(TableList))+
457
sizeof(nested_join_st))))
459
tbl->nested_join= (nested_join_st*) ((unsigned char*)tbl +
460
ALIGN_SIZE(sizeof(TableList)));
464
void fix_list_after_tbl_changes(Select_Lex *new_parent, List<TableList> *tlist)
466
List_iterator<TableList> it(*tlist);
468
while ((table= it++))
471
table->on_expr->fix_after_pullout(new_parent, &table->on_expr);
472
if (table->nested_join)
473
fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list);
478
Convert a subquery predicate into a TableList semi-join nest
482
parent_join Parent join, the one that has subq_pred in its WHERE/ON
484
subq_pred Subquery predicate to be converted
487
Convert a subquery predicate into a TableList semi-join nest. All the
488
prerequisites are already checked, so the conversion is always successfull.
490
Prepared Statements: the transformation is permanent:
491
- Changes in TableList structures are naturally permanent
492
- Item tree changes are performed on statement MEM_ROOT:
493
= we activate statement MEM_ROOT
494
= this function is called before the first fix_prepare_information
497
This is intended because the criteria for subquery-to-sj conversion remain
498
constant for the lifetime of the Prepared Statement.
502
true Out of memory error
504
bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
506
Select_Lex *parent_lex= parent_join->select_lex;
507
TableList *emb_tbl_nest= NULL;
508
List<TableList> *emb_join_list= &parent_lex->top_join_list;
509
Session *session= parent_join->session;
512
1. Find out where to put the predicate into.
513
Note: for "t1 LEFT JOIN t2" this will be t2, a leaf.
515
if ((void*)subq_pred->expr_join_nest != (void*)1)
517
if (subq_pred->expr_join_nest->nested_join)
522
... [LEFT] JOIN ( ... ) ON (subquery AND whatever) ...
524
The sj-nest will be inserted into the brackets nest.
526
emb_tbl_nest= subq_pred->expr_join_nest;
527
emb_join_list= &emb_tbl_nest->nested_join->join_list;
529
else if (!subq_pred->expr_join_nest->outer_join)
534
... INNER JOIN tblX ON (subquery AND whatever) ...
536
The sj-nest will be tblX's "sibling", i.e. another child of its
537
parent. This is ok because tblX is joined as an inner join.
539
emb_tbl_nest= subq_pred->expr_join_nest->embedding;
541
emb_join_list= &emb_tbl_nest->nested_join->join_list;
543
else if (!subq_pred->expr_join_nest->nested_join)
545
TableList *outer_tbl= subq_pred->expr_join_nest;
546
TableList *wrap_nest;
550
... LEFT JOIN tbl ON (on_expr AND subq_pred) ...
552
we'll need to convert it into:
554
... LEFT JOIN ( tbl SJ (subq_tables) ) ON (on_expr AND subq_pred) ...
556
|<----- wrap_nest ---->|
558
Q: other subqueries may be pointing to this element. What to do?
559
A1: simple solution: copy *subq_pred->expr_join_nest= *parent_nest.
560
But we'll need to fix other pointers.
561
A2: Another way: have TableList::next_ptr so the following
562
subqueries know the table has been nested.
563
A3: changes in the TableList::outer_join will make everything work
566
if (!(wrap_nest= alloc_join_nest(parent_join->session)))
570
wrap_nest->embedding= outer_tbl->embedding;
571
wrap_nest->join_list= outer_tbl->join_list;
572
wrap_nest->alias= (char*) "(sj-wrap)";
574
wrap_nest->nested_join->join_list.empty();
575
wrap_nest->nested_join->join_list.push_back(outer_tbl);
577
outer_tbl->embedding= wrap_nest;
578
outer_tbl->join_list= &wrap_nest->nested_join->join_list;
581
wrap_nest will take place of outer_tbl, so move the outer join flag
584
wrap_nest->outer_join= outer_tbl->outer_join;
585
outer_tbl->outer_join= 0;
587
wrap_nest->on_expr= outer_tbl->on_expr;
588
outer_tbl->on_expr= NULL;
590
List_iterator<TableList> li(*wrap_nest->join_list);
594
if (tbl == outer_tbl)
596
li.replace(wrap_nest);
601
Ok now wrap_nest 'contains' outer_tbl and we're ready to add the
602
semi-join nest into it
604
emb_join_list= &wrap_nest->nested_join->join_list;
605
emb_tbl_nest= wrap_nest;
610
nested_join_st *nested_join;
611
if (!(sj_nest= alloc_join_nest(parent_join->session)))
615
nested_join= sj_nest->nested_join;
617
sj_nest->join_list= emb_join_list;
618
sj_nest->embedding= emb_tbl_nest;
619
sj_nest->alias= (char*) "(sj-nest)";
620
/* Nests do not participate in those 'chains', so: */
621
/* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/
622
emb_join_list->push_back(sj_nest);
625
nested_join->used_tables and nested_join->not_null_tables are
626
initialized in simplify_joins().
630
2. Walk through subquery's top list and set 'embedding' to point to the
633
Select_Lex *subq_lex= subq_pred->unit->first_select();
634
nested_join->join_list.empty();
635
List_iterator_fast<TableList> li(subq_lex->top_join_list);
636
TableList *tl, *last_leaf;
639
tl->embedding= sj_nest;
640
tl->join_list= &nested_join->join_list;
641
nested_join->join_list.push_back(tl);
645
Reconnect the next_leaf chain.
646
TODO: Do we have to put subquery's tables at the end of the chain?
647
Inserting them at the beginning would be a bit faster.
648
NOTE: We actually insert them at the front! That's because the order is
649
reversed in this list.
651
for (tl= parent_lex->leaf_tables; tl->next_leaf; tl= tl->next_leaf) {};
652
tl->next_leaf= subq_lex->leaf_tables;
656
Same as above for next_local chain
657
(a theory: a next_local chain always starts with ::leaf_tables
658
because view's tables are inserted after the view)
660
for (tl= parent_lex->leaf_tables; tl->next_local; tl= tl->next_local) {};
661
tl->next_local= subq_lex->leaf_tables;
663
/* A theory: no need to re-connect the next_global chain */
665
/* 3. Remove the original subquery predicate from the WHERE/ON */
667
// The subqueries were replaced for Item_int(1) earlier
668
subq_pred->exec_method= Item_in_subselect::SEMI_JOIN; // for subsequent executions
669
/*TODO: also reset the 'with_subselect' there. */
671
/* n. Adjust the parent_join->tables counter */
672
uint32_t table_no= parent_join->tables;
673
/* n. Walk through child's tables and adjust table->map */
674
for (tl= subq_lex->leaf_tables; tl; tl= tl->next_leaf, table_no++)
676
tl->table->tablenr= table_no;
677
tl->table->map= ((table_map)1) << table_no;
678
Select_Lex *old_sl= tl->select_lex;
679
tl->select_lex= parent_join->select_lex;
680
for(TableList *emb= tl->embedding; emb && emb->select_lex == old_sl; emb= emb->embedding)
681
emb->select_lex= parent_join->select_lex;
683
parent_join->tables += subq_lex->join->tables;
686
Put the subquery's WHERE into semi-join's sj_on_expr
687
Add the subquery-induced equalities too.
689
Select_Lex *save_lex= session->lex->current_select;
690
session->lex->current_select=subq_lex;
691
if (!subq_pred->left_expr->fixed &&
692
subq_pred->left_expr->fix_fields(session, &subq_pred->left_expr))
694
session->lex->current_select=save_lex;
696
sj_nest->nested_join->sj_corr_tables= subq_pred->used_tables();
697
sj_nest->nested_join->sj_depends_on= subq_pred->used_tables() |
698
subq_pred->left_expr->used_tables();
699
sj_nest->sj_on_expr= subq_lex->where;
702
Create the IN-equalities and inject them into semi-join's ON expression.
703
Additionally, for InsideOut strategy
704
- Record the number of IN-equalities.
705
- Create list of pointers to (oe1, ..., ieN). We'll need the list to
706
see which of the expressions are bound and which are not (for those
707
we'll produce a distinct stream of (ie_i1,...ie_ik).
709
(TODO: can we just create a list of pointers and hope the expressions
710
will not substitute themselves on fix_fields()? or we need to wrap
711
them into Item_direct_view_refs and store pointers to those. The
712
pointers to Item_direct_view_refs are guaranteed to be stable as
713
Item_direct_view_refs doesn't substitute itself with anything in
714
Item_direct_view_ref::fix_fields.
716
sj_nest->sj_in_exprs= subq_pred->left_expr->cols();
717
sj_nest->nested_join->sj_outer_expr_list.empty();
719
if (subq_pred->left_expr->cols() == 1)
721
nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr);
723
Item *item_eq= new Item_func_eq(subq_pred->left_expr,
724
subq_lex->ref_pointer_array[0]);
725
item_eq->name= (char*)subq_sj_cond_name;
726
sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
730
for (uint32_t i= 0; i < subq_pred->left_expr->cols(); i++)
732
nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr->
735
new Item_func_eq(subq_pred->left_expr->element_index(i),
736
subq_lex->ref_pointer_array[i]);
737
item_eq->name= (char*)subq_sj_cond_name + (i % 64);
738
sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
741
/* Fix the created equality and AND */
742
sj_nest->sj_on_expr->fix_fields(parent_join->session, &sj_nest->sj_on_expr);
745
Walk through sj nest's WHERE and ON expressions and call
746
item->fix_table_changes() for all items.
748
sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
749
fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);
752
/* Unlink the child select_lex so it doesn't show up in EXPLAIN: */
753
subq_lex->master_unit()->exclude_level();
755
/* Inject sj_on_expr into the parent's WHERE or ON */
758
emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,
759
sj_nest->sj_on_expr);
760
emb_tbl_nest->on_expr->fix_fields(parent_join->session, &emb_tbl_nest->on_expr);
764
/* Inject into the WHERE */
765
parent_join->conds= and_items(parent_join->conds, sj_nest->sj_on_expr);
766
parent_join->conds->fix_fields(parent_join->session, &parent_join->conds);
767
parent_join->select_lex->where= parent_join->conds;
774
Check if table's KEYUSE elements have an eq_ref(outer_tables) candidate
777
find_eq_ref_candidate()
778
table Table to be checked
779
sj_inner_tables Bitmap of inner tables. eq_ref(inner_table) doesn't
783
Check if table's KEYUSE elements have an eq_ref(outer_tables) candidate
786
Check again if it is feasible to factor common parts with constant table
790
true - There exists an eq_ref(outer-tables) candidate
793
bool find_eq_ref_candidate(Table *table, table_map sj_inner_tables)
795
KEYUSE *keyuse= table->reginfo.join_tab->keyuse;
800
while (1) /* For each key */
803
KEY *keyinfo= table->key_info + key;
804
key_part_map bound_parts= 0;
805
if ((keyinfo->flags & HA_NOSAME) == HA_NOSAME)
807
do /* For all equalities on all key parts */
809
/* Check if this is "t.keypart = expr(outer_tables) */
810
if (!(keyuse->used_tables & sj_inner_tables) &&
811
!(keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL))
813
bound_parts |= 1 << keyuse->keypart;
816
} while (keyuse->key == key && keyuse->table == table);
818
if (bound_parts == PREV_BITS(uint, keyinfo->key_parts))
820
if (keyuse->table != table)
828
if (keyuse->table != table)
831
while (keyuse->key == key);
455
838
/*****************************************************************************
456
Create JoinTableS, make a guess about the table types,
839
Create JOIN_TABS, make a guess about the table types,
457
840
Approximate how many records will be used in each table
458
841
*****************************************************************************/
459
ha_rows get_quick_record_count(Session *session, optimizer::SqlSelect *select, Table *table, const key_map *keys,ha_rows limit)
842
ha_rows get_quick_record_count(Session *session, SQL_SELECT *select, Table *table, const key_map *keys,ha_rows limit)
462
845
if (check_stack_overrun(session, STACK_MIN_SIZE, NULL))
485
868
keyuse Pointer to possible keys
486
869
*****************************************************************************/
872
Merge new key definitions to old ones, remove those not used in both.
874
This is called for OR between different levels.
876
To be able to do 'ref_or_null' we merge a comparison of a column
877
and 'column IS NULL' to one test. This is useful for sub select queries
878
that are internally transformed to something like:.
881
SELECT * FROM t1 WHERE t1.key=outer_ref_field or t1.key IS NULL
884
KEY_FIELD::null_rejecting is processed as follows: @n
885
result has null_rejecting=true if it is set for both ORed references.
887
- (t2.key = t1.field OR t2.key = t1.field) -> null_rejecting=true
888
- (t2.key = t1.field OR t2.key <=> t1.field) -> null_rejecting=false
891
The result of this is that we're missing some 'ref' accesses.
892
OptimizerTeam: Fix this
894
static KEY_FIELD *merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end, uint32_t and_level)
896
if (start == new_fields)
897
return start; // Impossible or
898
if (new_fields == end)
899
return start; // No new fields, skip all
901
KEY_FIELD *first_free=new_fields;
903
/* Mark all found fields in old array */
904
for (; new_fields != end ; new_fields++)
906
for (KEY_FIELD *old=start ; old != first_free ; old++)
908
if (old->field == new_fields->field)
911
NOTE: below const_item() call really works as "!used_tables()", i.e.
912
it can return false where it is feasible to make it return true.
914
The cause is as follows: Some of the tables are already known to be
915
const tables (the detection code is in make_join_statistics(),
916
above the update_ref_and_keys() call), but we didn't propagate
917
information about this: Table::const_table is not set to true, and
918
Item::update_used_tables() hasn't been called for each item.
919
The result of this is that we're missing some 'ref' accesses.
920
TODO: OptimizerTeam: Fix this
922
if (!new_fields->val->const_item())
925
If the value matches, we can use the key reference.
926
If not, we keep it until we have examined all new values
928
if (old->val->eq(new_fields->val, old->field->binary()))
930
old->level= and_level;
931
old->optimize= ((old->optimize & new_fields->optimize &
932
KEY_OPTIMIZE_EXISTS) |
933
((old->optimize | new_fields->optimize) &
934
KEY_OPTIMIZE_REF_OR_NULL));
935
old->null_rejecting= (old->null_rejecting &&
936
new_fields->null_rejecting);
939
else if (old->eq_func && new_fields->eq_func &&
940
old->val->eq_by_collation(new_fields->val,
941
old->field->binary(),
942
old->field->charset()))
945
old->level= and_level;
946
old->optimize= ((old->optimize & new_fields->optimize &
947
KEY_OPTIMIZE_EXISTS) |
948
((old->optimize | new_fields->optimize) &
949
KEY_OPTIMIZE_REF_OR_NULL));
950
old->null_rejecting= (old->null_rejecting &&
951
new_fields->null_rejecting);
953
else if (old->eq_func && new_fields->eq_func &&
954
((old->val->const_item() && old->val->is_null()) ||
955
new_fields->val->is_null()))
957
/* field = expression OR field IS NULL */
958
old->level= and_level;
959
old->optimize= KEY_OPTIMIZE_REF_OR_NULL;
961
Remember the NOT NULL value unless the value does not depend
964
if (!old->val->used_tables() && old->val->is_null())
965
old->val= new_fields->val;
966
/* The referred expression can be NULL: */
967
old->null_rejecting= 0;
972
We are comparing two different const. In this case we can't
973
use a key-lookup on this so it's better to remove the value
974
and let the range optimzier handle it
976
if (old == --first_free) // If last item
978
*old= *first_free; // Remove old value
979
old--; // Retry this value
984
/* Remove all not used items */
985
for (KEY_FIELD *old=start ; old != first_free ;)
987
if (old->level != and_level)
988
{ // Not used in all levels
989
if (old == --first_free)
991
*old= *first_free; // Remove old value
1000
Add a possible key to array of possible keys if it's usable as a key
1002
@param key_fields Pointer to add key, if usable
1003
@param and_level And level, to be stored in KEY_FIELD
1004
@param cond Condition predicate
1005
@param field Field used in comparision
1006
@param eq_func True if we used =, <=> or IS NULL
1007
@param value Value used for comparison with field
1008
@param usable_tables Tables which can be used for key optimization
1009
@param sargables IN/OUT Array of found sargable candidates
1012
If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
1013
table, we store this to be able to do not exists optimization later.
1016
*key_fields is incremented if we stored a key in the array
1018
static void add_key_field(KEY_FIELD **key_fields,
1024
uint32_t num_values,
1025
table_map usable_tables,
1026
SARGABLE_PARAM **sargables)
1028
uint32_t exists_optimize= 0;
1029
if (!(field->flags & PART_KEY_FLAG))
1031
// Don't remove column IS NULL on a LEFT JOIN table
1032
if (!eq_func || (*value)->type() != Item::NULL_ITEM ||
1033
!field->table->maybe_null || field->null_ptr)
1034
return; // Not a key. Skip it
1035
exists_optimize= KEY_OPTIMIZE_EXISTS;
1036
assert(num_values == 1);
1040
table_map used_tables=0;
1042
for (uint32_t i=0; i<num_values; i++)
1044
used_tables|=(value[i])->used_tables();
1045
if (!((value[i])->used_tables() & (field->table->map | RAND_TABLE_BIT)))
1050
if (!(usable_tables & field->table->map))
1052
if (!eq_func || (*value)->type() != Item::NULL_ITEM ||
1053
!field->table->maybe_null || field->null_ptr)
1054
return; // Can't use left join optimize
1055
exists_optimize= KEY_OPTIMIZE_EXISTS;
1059
JOIN_TAB *stat=field->table->reginfo.join_tab;
1060
key_map possible_keys= field->key_start;
1061
possible_keys&= field->table->keys_in_use_for_query;
1062
stat[0].keys|= possible_keys; // Add possible keys
1065
Save the following cases:
1067
Field LIKE constant where constant doesn't start with a wildcard
1068
Field = field2 where field2 is in a different table
1075
stat[0].key_dependent|= used_tables;
1078
for (uint32_t i=0; i<num_values; i++)
1080
if (!(is_const&= value[i]->const_item()))
1084
stat[0].const_keys|= possible_keys;
1088
Save info to be able check whether this predicate can be
1089
considered as sargable for range analisis after reading const tables.
1090
We do not save info about equalities as update_const_equal_items
1091
will take care of updating info on keys from sargable equalities.
1094
(*sargables)->field= field;
1095
(*sargables)->arg_value= value;
1096
(*sargables)->num_values= num_values;
1099
We can't always use indexes when comparing a string index to a
1100
number. cmp_type() is checked to allow compare of dates to numbers.
1101
eq_func is NEVER true when num_values > 1
1106
Additional optimization: if we're processing
1107
"t.key BETWEEN c1 AND c1" then proceed as if we were processing
1109
TODO: This is a very limited fix. A more generic fix is possible.
1110
There are 2 options:
1111
A) Make equality propagation code be able to handle BETWEEN
1112
(including cases like t1.key BETWEEN t2.key AND t3.key)
1113
B) Make range optimizer to infer additional "t.key = c" equalities
1114
and use them in equality propagation process (see details in
1117
if ((cond->functype() != Item_func::BETWEEN) ||
1118
((Item_func_between*) cond)->negated ||
1119
!value[0]->eq(value[1], field->binary()))
1124
if (field->result_type() == STRING_RESULT)
1126
if ((*value)->result_type() != STRING_RESULT)
1128
if (field->cmp_type() != (*value)->result_type())
1134
We can't use indexes if the effective collation
1135
of the operation differ from the field collation.
1137
if (field->cmp_type() == STRING_RESULT &&
1138
((Field_str*)field)->charset() != cond->compare_collation())
1145
For the moment eq_func is always true. This slot is reserved for future
1146
extensions where we want to remembers other things than just eq comparisons
1149
/* Store possible eq field */
1150
(*key_fields)->field= field;
1151
(*key_fields)->eq_func= eq_func;
1152
(*key_fields)->val= *value;
1153
(*key_fields)->level= and_level;
1154
(*key_fields)->optimize= exists_optimize;
1156
If the condition has form "tbl.keypart = othertbl.field" and
1157
othertbl.field can be NULL, there will be no matches if othertbl.field
1159
We use null_rejecting in add_not_null_conds() to add
1160
'othertbl.field IS NOT NULL' to tab->select_cond.
1162
(*key_fields)->null_rejecting= ((cond->functype() == Item_func::EQ_FUNC ||
1163
cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
1164
((*value)->type() == Item::FIELD_ITEM) &&
1165
((Item_field*)*value)->field->maybe_null());
1166
(*key_fields)->cond_guard= NULL;
1167
(*key_fields)->sj_pred_no= (cond->name >= subq_sj_cond_name &&
1168
cond->name < subq_sj_cond_name + 64)?
1169
cond->name - subq_sj_cond_name: UINT_MAX;
1174
Add possible keys to array of possible keys originated from a simple
1177
@param key_fields Pointer to add key, if usable
1178
@param and_level And level, to be stored in KEY_FIELD
1179
@param cond Condition predicate
1180
@param field Field used in comparision
1181
@param eq_func True if we used =, <=> or IS NULL
1182
@param value Value used for comparison with field
1183
Is NULL for BETWEEN and IN
1184
@param usable_tables Tables which can be used for key optimization
1185
@param sargables IN/OUT Array of found sargable candidates
1188
If field items f1 and f2 belong to the same multiple equality and
1189
a key is added for f1, the the same key is added for f2.
1192
*key_fields is incremented if we stored a key in the array
1194
static void add_key_equal_fields(KEY_FIELD **key_fields,
1197
Item_field *field_item,
1200
uint32_t num_values,
1201
table_map usable_tables,
1202
SARGABLE_PARAM **sargables)
1204
Field *field= field_item->field;
1205
add_key_field(key_fields, and_level, cond, field,
1206
eq_func, val, num_values, usable_tables, sargables);
1207
Item_equal *item_equal= field_item->item_equal;
1211
Add to the set of possible key values every substitution of
1212
the field for an equal field included into item_equal
1214
Item_equal_iterator it(*item_equal);
1216
while ((item= it++))
1218
if (!field->eq(item->field))
1220
add_key_field(key_fields, and_level, cond, item->field,
1221
eq_func, val, num_values, usable_tables,
1228
static void add_key_fields(JOIN *join,
1229
KEY_FIELD **key_fields,
1230
uint32_t *and_level,
1232
table_map usable_tables,
1233
SARGABLE_PARAM **sargables)
1235
if (cond->type() == Item_func::COND_ITEM)
1237
List_iterator_fast<Item> li(*((Item_cond*) cond)->argument_list());
1238
KEY_FIELD *org_key_fields= *key_fields;
1240
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
1244
add_key_fields(join, key_fields, and_level, item, usable_tables,
1246
for (; org_key_fields != *key_fields ; org_key_fields++)
1247
org_key_fields->level= *and_level;
1252
add_key_fields(join, key_fields, and_level, li++, usable_tables,
1257
KEY_FIELD *start_key_fields= *key_fields;
1259
add_key_fields(join, key_fields, and_level, item, usable_tables,
1261
*key_fields=merge_key_fields(org_key_fields,start_key_fields,
1262
*key_fields,++(*and_level));
1269
Subquery optimization: Conditions that are pushed down into subqueries
1270
are wrapped into Item_func_trig_cond. We process the wrapped condition
1271
but need to set cond_guard for KEYUSE elements generated from it.
1274
if (cond->type() == Item::FUNC_ITEM &&
1275
((Item_func*)cond)->functype() == Item_func::TRIG_COND_FUNC)
1277
Item *cond_arg= ((Item_func*)cond)->arguments()[0];
1278
if (!join->group_list && !join->order &&
1280
join->unit->item->substype() == Item_subselect::IN_SUBS &&
1281
!join->unit->is_union())
1283
KEY_FIELD *save= *key_fields;
1284
add_key_fields(join, key_fields, and_level, cond_arg, usable_tables,
1286
// Indicate that this ref access candidate is for subquery lookup:
1287
for (; save != *key_fields; save++)
1288
save->cond_guard= ((Item_func_trig_cond*)cond)->get_trig_var();
1294
/* If item is of type 'field op field/constant' add it to key_fields */
1295
if (cond->type() != Item::FUNC_ITEM)
1297
Item_func *cond_func= (Item_func*) cond;
1298
switch (cond_func->select_optimize()) {
1299
case Item_func::OPTIMIZE_NONE:
1301
case Item_func::OPTIMIZE_KEY:
1305
if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM &&
1306
!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
1308
values= cond_func->arguments()+1;
1309
if (cond_func->functype() == Item_func::NE_FUNC &&
1310
cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
1311
!(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
1313
assert(cond_func->functype() != Item_func::IN_FUNC ||
1314
cond_func->argument_count() != 2);
1315
add_key_equal_fields(key_fields, *and_level, cond_func,
1316
(Item_field*) (cond_func->key_item()->real_item()),
1318
cond_func->argument_count()-1,
1319
usable_tables, sargables);
1321
if (cond_func->functype() == Item_func::BETWEEN)
1323
values= cond_func->arguments();
1324
for (uint32_t i= 1 ; i < cond_func->argument_count() ; i++)
1326
Item_field *field_item;
1327
if (cond_func->arguments()[i]->real_item()->type() == Item::FIELD_ITEM
1329
!(cond_func->arguments()[i]->used_tables() & OUTER_REF_TABLE_BIT))
1331
field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
1332
add_key_equal_fields(key_fields, *and_level, cond_func,
1333
field_item, 0, values, 1, usable_tables,
1340
case Item_func::OPTIMIZE_OP:
1342
bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC ||
1343
cond_func->functype() == Item_func::EQUAL_FUNC);
1345
if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
1346
!(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
1348
add_key_equal_fields(key_fields, *and_level, cond_func,
1349
(Item_field*) (cond_func->arguments()[0])->real_item(),
1351
cond_func->arguments()+1, 1, usable_tables,
1354
if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
1355
cond_func->functype() != Item_func::LIKE_FUNC &&
1356
!(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT))
1358
add_key_equal_fields(key_fields, *and_level, cond_func,
1359
(Item_field*) (cond_func->arguments()[1])->real_item(), equal_func,
1360
cond_func->arguments(),1,usable_tables,
1365
case Item_func::OPTIMIZE_NULL:
1366
/* column_name IS [NOT] NULL */
1367
if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
1368
!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
1370
Item *tmp=new Item_null;
1371
if (unlikely(!tmp)) // Should never be true
1373
add_key_equal_fields(key_fields, *and_level, cond_func,
1374
(Item_field*) (cond_func->arguments()[0])->real_item(),
1375
cond_func->functype() == Item_func::ISNULL_FUNC,
1376
&tmp, 1, usable_tables, sargables);
1379
case Item_func::OPTIMIZE_EQUAL:
1380
Item_equal *item_equal= (Item_equal *) cond;
1381
Item *const_item= item_equal->get_const();
1382
Item_equal_iterator it(*item_equal);
1387
For each field field1 from item_equal consider the equality
1388
field1=const_item as a condition allowing an index access of the table
1389
with field1 by the keys value of field1.
1391
while ((item= it++))
1393
add_key_field(key_fields, *and_level, cond_func, item->field,
1394
true, &const_item, 1, usable_tables, sargables);
1400
Consider all pairs of different fields included into item_equal.
1401
For each of them (field1, field1) consider the equality
1402
field1=field2 as a condition allowing an index access of the table
1403
with field1 by the keys value of field2.
1405
Item_equal_iterator fi(*item_equal);
1406
while ((item= fi++))
1408
Field *field= item->field;
1409
while ((item= it++))
1411
if (!field->eq(item->field))
1413
add_key_field(key_fields, *and_level, cond_func, field,
1414
true, (Item **) &item, 1, usable_tables,
490
1426
Add all keys with uses 'field' for some keypart.
501
static int sort_keyuse(optimizer::KeyUse *a, optimizer::KeyUse *b)
1437
static void add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
1439
Field *field=key_field->field;
1440
Table *form= field->table;
1443
if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS))
1445
for (uint32_t key= 0 ; key < form->sizeKeys() ; key++)
1447
if (!(form->keys_in_use_for_query.test(key)))
1450
uint32_t key_parts= (uint32_t) form->key_info[key].key_parts;
1451
for (uint32_t part=0 ; part < key_parts ; part++)
1453
if (field->eq(form->key_info[key].key_part[part].field))
1455
keyuse.table= field->table;
1456
keyuse.val = key_field->val;
1458
keyuse.keypart=part;
1459
keyuse.keypart_map= (key_part_map) 1 << part;
1460
keyuse.used_tables=key_field->val->used_tables();
1461
keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
1462
keyuse.null_rejecting= key_field->null_rejecting;
1463
keyuse.cond_guard= key_field->cond_guard;
1464
keyuse.sj_pred_no= key_field->sj_pred_no;
1465
insert_dynamic(keyuse_array,(unsigned char*) &keyuse);
1472
static int sort_keyuse(KEYUSE *a,KEYUSE *b)
504
if (a->getTable()->tablenr != b->getTable()->tablenr)
505
return static_cast<int>((a->getTable()->tablenr - b->getTable()->tablenr));
506
if (a->getKey() != b->getKey())
507
return static_cast<int>((a->getKey() - b->getKey()));
508
if (a->getKeypart() != b->getKeypart())
509
return static_cast<int>((a->getKeypart() - b->getKeypart()));
1475
if (a->table->tablenr != b->table->tablenr)
1476
return (int) (a->table->tablenr - b->table->tablenr);
1477
if (a->key != b->key)
1478
return (int) (a->key - b->key);
1479
if (a->keypart != b->keypart)
1480
return (int) (a->keypart - b->keypart);
510
1481
// Place const values before other ones
511
if ((res= test((a->getUsedTables() & ~OUTER_REF_TABLE_BIT)) -
512
test((b->getUsedTables() & ~OUTER_REF_TABLE_BIT))))
1482
if ((res= test((a->used_tables & ~OUTER_REF_TABLE_BIT)) -
1483
test((b->used_tables & ~OUTER_REF_TABLE_BIT))))
514
1485
/* Place rows that are not 'OPTIMIZE_REF_OR_NULL' first */
515
return static_cast<int>(((a->getOptimizeFlags() & KEY_OPTIMIZE_REF_OR_NULL) -
516
(b->getOptimizeFlags() & KEY_OPTIMIZE_REF_OR_NULL)));
1486
return (int) ((a->optimize & KEY_OPTIMIZE_REF_OR_NULL) -
1487
(b->optimize & KEY_OPTIMIZE_REF_OR_NULL));
1491
Add to KEY_FIELD array all 'ref' access candidates within nested join.
1493
This function populates KEY_FIELD array with entries generated from the
1494
ON condition of the given nested join, and does the same for nested joins
1495
contained within this nested join.
1497
@param[in] nested_join_table Nested join pseudo-table to process
1498
@param[in,out] end End of the key field array
1499
@param[in,out] and_level And-level
1500
@param[in,out] sargables Array of found sargable candidates
1504
We can add accesses to the tables that are direct children of this nested
1505
join (1), and are not inner tables w.r.t their neighbours (2).
1507
Example for #1 (outer brackets pair denotes nested join this function is
1510
... LEFT JOIN (t1 LEFT JOIN (t2 ... ) ) ON cond
1514
... LEFT JOIN (t1 LEFT JOIN t2 ) ON cond
1516
In examples 1-2 for condition cond, we can add 'ref' access candidates to
1520
... LEFT JOIN (t1, t2 LEFT JOIN t3 ON inner_cond) ON cond
1522
Here we can add 'ref' access candidates for t1 and t2, but not for t3.
1524
static void add_key_fields_for_nj(JOIN *join,
1525
TableList *nested_join_table,
1527
uint32_t *and_level,
1528
SARGABLE_PARAM **sargables)
1530
List_iterator<TableList> li(nested_join_table->nested_join->join_list);
1531
List_iterator<TableList> li2(nested_join_table->nested_join->join_list);
1532
bool have_another = false;
1533
table_map tables= 0;
1535
assert(nested_join_table->nested_join);
1537
while ((table= li++) || (have_another && (li=li2, have_another=false,
1540
if (table->nested_join)
1542
if (!table->on_expr)
1544
/* It's a semi-join nest. Walk into it as if it wasn't a nest */
1547
li= List_iterator<TableList>(table->nested_join->join_list);
1550
add_key_fields_for_nj(join, table, end, and_level, sargables);
1553
if (!table->on_expr)
1554
tables |= table->table->map;
1556
if (nested_join_table->on_expr)
1557
add_key_fields(join, end, and_level, nested_join_table->on_expr, tables,
521
1562
Update keyuse array with all possible keys we can use to fetch rows.
524
@param[out] keyuse Put here ordered array of KeyUse structures
1565
@param[out] keyuse Put here ordered array of KEYUSE structures
525
1566
@param join_tab Array in tablenr_order
526
1567
@param tables Number of tables in join
527
1568
@param cond WHERE condition (note that the function analyzes
2290
Check if given expression uses only table fields covered by the given index
2293
uses_index_fields_only()
2294
item Expression to check
2295
tbl The table having the index
2296
keyno The index number
2297
other_tbls_ok true <=> Fields of other non-const tables are allowed
2300
Check if given expression only uses fields covered by index #keyno in the
2301
table tbl. The expression can use any fields in any other tables.
2303
The expression is guaranteed not to be AND or OR - those constructs are
2304
handled outside of this function.
2310
bool uses_index_fields_only(Item *item, Table *tbl, uint32_t keyno, bool other_tbls_ok)
2312
if (item->const_item())
2316
Don't push down the triggered conditions. Nested outer joins execution
2317
code may need to evaluate a condition several times (both triggered and
2318
untriggered), and there is no way to put thi
2319
TODO: Consider cloning the triggered condition and using the copies for:
2320
1. push the first copy down, to have most restrictive index condition
2322
2. Put the second copy into tab->select_cond.
2324
if (item->type() == Item::FUNC_ITEM &&
2325
((Item_func*)item)->functype() == Item_func::TRIG_COND_FUNC)
2328
if (!(item->used_tables() & tbl->map))
2329
return other_tbls_ok;
2331
Item::Type item_type= item->type();
2332
switch (item_type) {
2333
case Item::FUNC_ITEM:
2335
/* This is a function, apply condition recursively to arguments */
2336
Item_func *item_func= (Item_func*)item;
2338
Item **item_end= (item_func->arguments()) + item_func->argument_count();
2339
for (child= item_func->arguments(); child != item_end; child++)
2341
if (!uses_index_fields_only(*child, tbl, keyno, other_tbls_ok))
2346
case Item::COND_ITEM:
2348
/* This is a function, apply condition recursively to arguments */
2349
List_iterator<Item> li(*((Item_cond*)item)->argument_list());
2351
while ((list_item=li++))
2353
if (!uses_index_fields_only(item, tbl, keyno, other_tbls_ok))
2358
case Item::FIELD_ITEM:
2360
Item_field *item_field= (Item_field*)item;
2361
if (item_field->field->table != tbl)
2363
return item_field->field->part_of_key.test(keyno);
2365
case Item::REF_ITEM:
2366
return uses_index_fields_only(item->real_item(), tbl, keyno,
2369
return false; /* Play it safe, don't push unknown non-const items */
1211
2373
#define ICP_COND_USES_INDEX_ONLY 10
2376
Get a part of the condition that can be checked using only index fields
2379
make_cond_for_index()
2380
cond The source condition
2381
table The table that is partially available
2382
keyno The index in the above table. Only fields covered by the index
2384
other_tbls_ok true <=> Fields of other non-const tables are allowed
2387
Get a part of the condition that can be checked when for the given table
2388
we have values only of fields covered by some index. The condition may
2389
refer to other tables, it is assumed that we have values of all of their
2393
make_cond_for_index(
2394
"cond(t1.field) AND cond(t2.key1) AND cond(t2.non_key) AND cond(t2.key2)",
2397
"cond(t1.field) AND cond(t2.key2)"
2400
Index condition, or NULL if no condition could be inferred.
2402
Item *make_cond_for_index(Item *cond, Table *table, uint32_t keyno, bool other_tbls_ok)
2406
if (cond->type() == Item::COND_ITEM)
2408
uint32_t n_marked= 0;
2409
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
2411
Item_cond_and *new_cond=new Item_cond_and;
2414
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
2418
Item *fix= make_cond_for_index(item, table, keyno, other_tbls_ok);
2420
new_cond->argument_list()->push_back(fix);
2421
n_marked += test(item->marker == ICP_COND_USES_INDEX_ONLY);
2423
if (n_marked ==((Item_cond*)cond)->argument_list()->elements)
2424
cond->marker= ICP_COND_USES_INDEX_ONLY;
2425
switch (new_cond->argument_list()->elements) {
2429
return new_cond->argument_list()->head();
2431
new_cond->quick_fix_field();
2437
Item_cond_or *new_cond=new Item_cond_or;
2440
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
2444
Item *fix= make_cond_for_index(item, table, keyno, other_tbls_ok);
2447
new_cond->argument_list()->push_back(fix);
2448
n_marked += test(item->marker == ICP_COND_USES_INDEX_ONLY);
2450
if (n_marked ==((Item_cond*)cond)->argument_list()->elements)
2451
cond->marker= ICP_COND_USES_INDEX_ONLY;
2452
new_cond->quick_fix_field();
2453
new_cond->top_level_item();
2458
if (!uses_index_fields_only(cond, table, keyno, other_tbls_ok))
2460
cond->marker= ICP_COND_USES_INDEX_ONLY;
2465
Item *make_cond_remainder(Item *cond, bool exclude_index)
2467
if (exclude_index && cond->marker == ICP_COND_USES_INDEX_ONLY)
2468
return 0; /* Already checked */
2470
if (cond->type() == Item::COND_ITEM)
2472
table_map tbl_map= 0;
2473
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
2475
/* Create new top level AND item */
2476
Item_cond_and *new_cond=new Item_cond_and;
2479
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
2483
Item *fix= make_cond_remainder(item, exclude_index);
2486
new_cond->argument_list()->push_back(fix);
2487
tbl_map |= fix->used_tables();
2490
switch (new_cond->argument_list()->elements) {
2494
return new_cond->argument_list()->head();
2496
new_cond->quick_fix_field();
2497
((Item_cond*)new_cond)->used_tables_cache= tbl_map;
2503
Item_cond_or *new_cond=new Item_cond_or;
2506
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
2510
Item *fix= make_cond_remainder(item, false);
2513
new_cond->argument_list()->push_back(fix);
2514
tbl_map |= fix->used_tables();
2516
new_cond->quick_fix_field();
2517
((Item_cond*)new_cond)->used_tables_cache= tbl_map;
2518
new_cond->top_level_item();
2526
Try to extract and push the index condition
2530
tab A join tab that has tab->table->file and its condition
2532
keyno Index for which extract and push the condition
2533
other_tbls_ok true <=> Fields of other non-const tables are allowed
2536
Try to extract and push the index condition down to table handler
2538
void push_index_cond(JOIN_TAB *tab, uint32_t keyno, bool other_tbls_ok)
2541
if (tab->table->file->index_flags(keyno, 0, 1) & HA_DO_INDEX_COND_PUSHDOWN &&
2542
tab->join->session->variables.engine_condition_pushdown)
2544
idx_cond= make_cond_for_index(tab->select_cond, tab->table, keyno,
2549
tab->pre_idx_push_select_cond= tab->select_cond;
2550
Item *idx_remainder_cond= tab->table->file->idx_cond_push(keyno, idx_cond);
2553
Disable eq_ref's "lookup cache" if we've pushed down an index
2555
TODO: This check happens to work on current ICP implementations, but
2556
there may exist a compliant implementation that will not work
2557
correctly with it. Sort this out when we stabilize the condition
2560
if (idx_remainder_cond != idx_cond)
2561
tab->ref.disable_cache= true;
2563
Item *row_cond= make_cond_remainder(tab->select_cond, true);
2567
if (!idx_remainder_cond)
2568
tab->select_cond= row_cond;
2571
tab->select_cond= new Item_cond_and(row_cond, idx_remainder_cond);
2572
tab->select_cond->quick_fix_field();
2573
((Item_cond_and*)tab->select_cond)->used_tables_cache=
2574
row_cond->used_tables() | idx_remainder_cond->used_tables();
2578
tab->select_cond= idx_remainder_cond;
2581
tab->select->cond= tab->select_cond;
1217
void JoinTable::cleanup()
2591
void JOIN_TAB::cleanup()
1223
2597
if (cache.buff)
1225
size_t size= cache.end - cache.buff;
1226
global_join_buffer.sub(size);
1227
2598
free(cache.buff);
7769
Send a description about what how the select will be done to stdout.
7771
void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
7772
bool distinct,const char *message)
7774
List<Item> field_list;
7775
List<Item> item_list;
7776
Session *session=join->session;
7777
select_result *result=join->result;
7778
Item *item_null= new Item_null();
7779
const CHARSET_INFO * const cs= system_charset_info;
7781
/* Don't log this into the slow query log */
7782
session->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
7783
join->unit->offset_limit_cnt= 0;
7786
NOTE: the number/types of items pushed into item_list must be in sync with
7787
EXPLAIN column types as they're "defined" in Session::send_explain_fields()
7791
item_list.push_back(new Item_int((int32_t)
7792
join->select_lex->select_number));
7793
item_list.push_back(new Item_string(join->select_lex->type,
7794
strlen(join->select_lex->type), cs));
7795
for (uint32_t i=0 ; i < 7; i++)
7796
item_list.push_back(item_null);
7797
if (join->session->lex->describe & DESCRIBE_EXTENDED)
7798
item_list.push_back(item_null);
7800
item_list.push_back(new Item_string(message,strlen(message),cs));
7801
if (result->send_data(item_list))
7804
else if (join->select_lex == join->unit->fake_select_lex)
7807
here we assume that the query will return at least two rows, so we
7808
show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
7809
and no filesort will be actually done, but executing all selects in
7810
the UNION to provide precise EXPLAIN information will hardly be
7813
char table_name_buffer[NAME_LEN];
7816
item_list.push_back(new Item_null);
7818
item_list.push_back(new Item_string(join->select_lex->type,
7819
strlen(join->select_lex->type),
7823
Select_Lex *sl= join->unit->first_select();
7824
uint32_t len= 6, lastop= 0;
7825
memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
7826
for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
7829
lastop= snprintf(table_name_buffer + len, NAME_LEN - len,
7830
"%u,", sl->select_number);
7832
if (sl || len + lastop >= NAME_LEN)
7834
memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1);
7840
table_name_buffer[len - 1]= '>'; // change ',' to '>'
7842
item_list.push_back(new Item_string(table_name_buffer, len, cs));
7845
item_list.push_back(new Item_string(join_type_str[JT_ALL],
7846
strlen(join_type_str[JT_ALL]),
7849
item_list.push_back(item_null);
7851
item_list.push_back(item_null);
7853
item_list.push_back(item_null);
7855
item_list.push_back(item_null);
7857
if (join->session->lex->describe & DESCRIBE_EXTENDED)
7858
item_list.push_back(item_null);
7860
item_list.push_back(item_null);
7862
if (join->unit->global_parameters->order_list.first)
7863
item_list.push_back(new Item_string("Using filesort",
7866
item_list.push_back(new Item_string("", 0, cs));
7868
if (result->send_data(item_list))
7873
table_map used_tables=0;
7874
for (uint32_t i=0 ; i < join->tables ; i++)
7876
JOIN_TAB *tab=join->join_tab+i;
7877
Table *table=tab->table;
7878
TableList *table_list= tab->table->pos_in_table_list;
7880
char buff1[512], buff2[512], buff3[512];
7881
char keylen_str_buf[64];
7882
String extra(buff, sizeof(buff),cs);
7883
char table_name_buffer[NAME_LEN];
7884
String tmp1(buff1,sizeof(buff1),cs);
7885
String tmp2(buff2,sizeof(buff2),cs);
7886
String tmp3(buff3,sizeof(buff3),cs);
7895
item_list.push_back(new Item_uint((uint32_t)
7896
join->select_lex->select_number));
7898
item_list.push_back(new Item_string(join->select_lex->type,
7899
strlen(join->select_lex->type),
7901
if (tab->type == JT_ALL && tab->select && tab->select->quick)
7903
quick_type= tab->select->quick->get_type();
7904
if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
7905
(quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
7906
(quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
7907
tab->type = JT_INDEX_MERGE;
7909
tab->type = JT_RANGE;
7912
if (table->derived_select_number)
7914
/* Derived table name generation */
7915
int len= snprintf(table_name_buffer, sizeof(table_name_buffer)-1,
7917
table->derived_select_number);
7918
item_list.push_back(new Item_string(table_name_buffer, len, cs));
7922
TableList *real_table= table->pos_in_table_list;
7923
item_list.push_back(new Item_string(real_table->alias,
7924
strlen(real_table->alias),
7928
item_list.push_back(new Item_string(join_type_str[tab->type],
7929
strlen(join_type_str[tab->type]),
7931
/* Build "possible_keys" value and add it to item_list */
7932
if (tab->keys.any())
7935
for (j=0 ; j < table->s->keys ; j++)
7937
if (tab->keys.test(j))
7941
tmp1.append(table->key_info[j].name,
7942
strlen(table->key_info[j].name),
7943
system_charset_info);
7948
item_list.push_back(new Item_string(tmp1.ptr(),tmp1.length(),cs));
7950
item_list.push_back(item_null);
7952
/* Build "key", "key_len", and "ref" values and add them to item_list */
7953
if (tab->ref.key_parts)
7955
KEY *key_info=table->key_info+ tab->ref.key;
7956
register uint32_t length;
7957
item_list.push_back(new Item_string(key_info->name,
7958
strlen(key_info->name),
7959
system_charset_info));
7960
length= int64_t2str(tab->ref.key_length, keylen_str_buf, 10) -
7962
item_list.push_back(new Item_string(keylen_str_buf, length,
7963
system_charset_info));
7964
for (StoredKey **ref=tab->ref.key_copy ; *ref ; ref++)
7968
tmp2.append((*ref)->name(), strlen((*ref)->name()),
7969
system_charset_info);
7971
item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
7973
else if (tab->type == JT_NEXT)
7975
KEY *key_info=table->key_info+ tab->index;
7976
register uint32_t length;
7977
item_list.push_back(new Item_string(key_info->name,
7978
strlen(key_info->name),cs));
7979
length= int64_t2str(key_info->key_length, keylen_str_buf, 10) -
7981
item_list.push_back(new Item_string(keylen_str_buf,
7983
system_charset_info));
7984
item_list.push_back(item_null);
7986
else if (tab->select && tab->select->quick)
7988
tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
7989
item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
7990
item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs));
7991
item_list.push_back(item_null);
7995
if (table_list->schema_table &&
7996
table_list->schema_table->getRequestedObject() & OPTIMIZE_I_S_TABLE)
7998
if (table_list->has_db_lookup_value)
8000
int f_idx= table_list->schema_table->getFirstColumnIndex();
8001
const string &tmp_buff= table_list->schema_table->getColumnName(f_idx);
8002
tmp2.append(tmp_buff.c_str(), tmp_buff.length(), cs);
8004
if (table_list->has_table_lookup_value)
8006
if (table_list->has_db_lookup_value)
8008
int f_idx= table_list->schema_table->getSecondColumnIndex();
8009
const string &tmp_buff= table_list->schema_table->getColumnName(f_idx);
8010
tmp2.append(tmp_buff.c_str(), tmp_buff.length(), cs);
8013
item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
8015
item_list.push_back(item_null);
8018
item_list.push_back(item_null);
8019
item_list.push_back(item_null);
8020
item_list.push_back(item_null);
8023
/* Add "rows" field to item_list. */
8024
if (table_list->schema_table)
8027
if (join->session->lex->describe & DESCRIBE_EXTENDED)
8028
item_list.push_back(item_null);
8030
item_list.push_back(item_null);
8034
double examined_rows;
8035
if (tab->select && tab->select->quick)
8036
examined_rows= rows2double(tab->select->quick->records);
8037
else if (tab->type == JT_NEXT || tab->type == JT_ALL)
8038
examined_rows= rows2double(tab->limit ? tab->limit :
8039
tab->table->file->records());
8041
examined_rows= join->best_positions[i].records_read;
8043
item_list.push_back(new Item_int((int64_t) (uint64_t) examined_rows,
8044
MY_INT64_NUM_DECIMAL_DIGITS));
8046
/* Add "filtered" field to item_list. */
8047
if (join->session->lex->describe & DESCRIBE_EXTENDED)
8051
f= (float) (100.0 * join->best_positions[i].records_read /
8053
item_list.push_back(new Item_float(f, 2));
8057
/* Build "Extra" field and add it to item_list. */
8058
bool key_read=table->key_read;
8059
if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
8060
table->covering_keys.test(tab->index))
8062
if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
8063
!((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row)
8067
item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
8068
else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
8070
if (tab->packed_info & TAB_INFO_USING_INDEX)
8071
extra.append(STRING_WITH_LEN("; Using index"));
8072
if (tab->packed_info & TAB_INFO_USING_WHERE)
8073
extra.append(STRING_WITH_LEN("; Using where"));
8074
if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
8075
extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
8076
/* Skip initial "; "*/
8077
const char *str= extra.ptr();
8078
uint32_t len= extra.length();
8084
item_list.push_back(new Item_string(str, len, cs));
8088
uint32_t keyno= MAX_KEY;
8089
if (tab->ref.key_parts)
8090
keyno= tab->ref.key;
8091
else if (tab->select && tab->select->quick)
8092
keyno = tab->select->quick->index;
8094
if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
8095
table->file->pushed_idx_cond)
8096
extra.append(STRING_WITH_LEN("; Using index condition"));
8098
if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
8099
quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
8100
quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
8102
extra.append(STRING_WITH_LEN("; Using "));
8103
tab->select->quick->add_info_string(&extra);
8107
if (tab->use_quick == 2)
8110
* To print out the bitset in tab->keys, we go through
8111
* it 32 bits at a time. We need to do this to ensure
8112
* that the to_ulong() method will not throw an
8113
* out_of_range exception at runtime which would happen
8114
* if the bitset we were working with was larger than 64
8115
* bits on a 64-bit platform (for example).
8121
for (uint32_t pos= 0; pos < tab->keys.size(); pos+= 32)
8123
bitset<32> tmp(str, pos, 32);
8125
s << uppercase << hex << tmp.to_ulong();
8127
extra.append(STRING_WITH_LEN("; Range checked for each "
8128
"record (index map: 0x"));
8129
extra.append(s.str().c_str());
8132
else if (tab->select->cond)
8134
const COND *pushed_cond= tab->table->file->pushed_cond;
8136
if (session->variables.engine_condition_pushdown && pushed_cond)
8138
extra.append(STRING_WITH_LEN("; Using where with pushed "
8140
if (session->lex->describe & DESCRIBE_EXTENDED)
8142
extra.append(STRING_WITH_LEN(": "));
8143
((COND *)pushed_cond)->print(&extra, QT_ORDINARY);
8147
extra.append(STRING_WITH_LEN("; Using where"));
8152
if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
8153
extra.append(STRING_WITH_LEN("; Using index for group-by"));
8155
extra.append(STRING_WITH_LEN("; Using index"));
8157
if (table->reginfo.not_exists_optimize)
8158
extra.append(STRING_WITH_LEN("; Not exists"));
8160
if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
8161
!(((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags &
8162
HA_MRR_USE_DEFAULT_IMPL))
8164
extra.append(STRING_WITH_LEN("; Using MRR"));
8167
if (table_list->schema_table &&
8168
table_list->schema_table->getRequestedObject() & OPTIMIZE_I_S_TABLE)
8170
if (!table_list->table_open_method)
8171
extra.append(STRING_WITH_LEN("; Skip_open_table"));
8172
else if (table_list->table_open_method == OPEN_FRM_ONLY)
8173
extra.append(STRING_WITH_LEN("; Open_frm_only"));
8175
extra.append(STRING_WITH_LEN("; Open_full_table"));
8176
if (table_list->has_db_lookup_value &&
8177
table_list->has_table_lookup_value)
8178
extra.append(STRING_WITH_LEN("; Scanned 0 databases"));
8179
else if (table_list->has_db_lookup_value ||
8180
table_list->has_table_lookup_value)
8181
extra.append(STRING_WITH_LEN("; Scanned 1 database"));
8183
extra.append(STRING_WITH_LEN("; Scanned all databases"));
8188
extra.append(STRING_WITH_LEN("; Using temporary"));
8193
extra.append(STRING_WITH_LEN("; Using filesort"));
8195
if (distinct & test_all_bits(used_tables,session->used_tables))
8196
extra.append(STRING_WITH_LEN("; Distinct"));
8198
if (tab->insideout_match_tab)
8200
extra.append(STRING_WITH_LEN("; LooseScan"));
8203
if (tab->flush_weedout_table)
8204
extra.append(STRING_WITH_LEN("; Start temporary"));
8205
else if (tab->check_weed_out_table)
8206
extra.append(STRING_WITH_LEN("; End temporary"));
8207
else if (tab->do_firstmatch)
8209
extra.append(STRING_WITH_LEN("; FirstMatch("));
8210
Table *prev_table=tab->do_firstmatch->table;
8211
if (prev_table->derived_select_number)
8213
char namebuf[NAME_LEN];
8214
/* Derived table name generation */
8215
int len= snprintf(namebuf, sizeof(namebuf)-1,
8217
prev_table->derived_select_number);
8218
extra.append(namebuf, len);
8221
extra.append(prev_table->pos_in_table_list->alias);
8222
extra.append(STRING_WITH_LEN(")"));
8225
for (uint32_t part= 0; part < tab->ref.key_parts; part++)
8227
if (tab->ref.cond_guards[part])
8229
extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
8234
if (i > 0 && tab[-1].next_select == sub_select_cache)
8235
extra.append(STRING_WITH_LEN("; Using join buffer"));
8237
/* Skip initial "; "*/
8238
const char *str= extra.ptr();
8239
uint32_t len= extra.length();
8245
item_list.push_back(new Item_string(str, len, cs));
8247
// For next iteration
8248
used_tables|=table->map;
8249
if (result->send_data(item_list))
8253
for (Select_Lex_Unit *unit= join->select_lex->first_inner_unit();
8255
unit= unit->next_unit())
8257
if (mysql_explain_union(session, unit, result))
8263
bool mysql_explain_union(Session *session, Select_Lex_Unit *unit, select_result *result)
8266
Select_Lex *first= unit->first_select();
8268
for (Select_Lex *sl= first;
8270
sl= sl->next_select())
8272
// drop UNCACHEABLE_EXPLAIN, because it is for internal usage only
8273
uint8_t uncacheable= (sl->uncacheable & ~UNCACHEABLE_EXPLAIN);
8274
sl->type= (((&session->lex->select_lex)==sl)?
8275
(sl->first_inner_unit() || sl->next_select() ?
8276
"PRIMARY" : "SIMPLE"):
8278
((sl->linkage == DERIVED_TABLE_TYPE) ?
8280
((uncacheable & UNCACHEABLE_DEPENDENT) ?
8281
"DEPENDENT SUBQUERY":
8282
(uncacheable?"UNCACHEABLE SUBQUERY":
8284
((uncacheable & UNCACHEABLE_DEPENDENT) ?
8286
uncacheable?"UNCACHEABLE UNION":
8288
sl->options|= SELECT_DESCRIBE;
8290
if (unit->is_union())
8292
unit->fake_select_lex->select_number= UINT_MAX; // jost for initialization
8293
unit->fake_select_lex->type= "UNION RESULT";
8294
unit->fake_select_lex->options|= SELECT_DESCRIBE;
8295
if (!(res= unit->prepare(session, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
8297
res|= unit->cleanup();
8301
session->lex->current_select= first;
8302
unit->set_limit(unit->global_parameters);
8303
res= mysql_select(session, &first->ref_pointer_array,
8304
(TableList*) first->table_list.first,
8305
first->with_wild, first->item_list,
8307
first->order_list.elements +
8308
first->group_list.elements,
8309
(order_st*) first->order_list.first,
8310
(order_st*) first->group_list.first,
8312
first->options | session->options | SELECT_DESCRIBE,
8313
result, unit, first);
8315
return(res || session->is_error());
6320
8318
static void print_table_array(Session *session, String *str, TableList **table,
6321
8319
TableList **end)
added
removed
drizzled/sql_select.cc
