summaryrefslogtreecommitdiffstats
path: root/sql/sql_select.cc
diff options
context:
space:
mode:
Diffstat (limited to 'sql/sql_select.cc')
-rw-r--r--sql/sql_select.cc108
1 files changed, 91 insertions, 17 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f4cbed58..c3ce21d1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1457,6 +1457,26 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num,
if (setup_wild(thd, tables_list, fields_list, &all_fields, select_lex, false))
DBUG_RETURN(-1);
+ /*
+ If the select_lex is immediately contained within a derived table
+ AND this derived table is a CTE
+ WITH supplied column names
+ AND we have the correct number of elements in both lists
+ (mismatches found in mysql_derived_prepare/rename_columns_of_derived_unit)
+ THEN NOW is the time to take a copy of these item_names for
+ later restoration if required.
+ */
+ TABLE_LIST *derived= select_lex->master_unit()->derived;
+
+ if (derived &&
+ derived->with &&
+ derived->with->column_list.elements &&
+ (derived->with->column_list.elements == select_lex->item_list.elements))
+ {
+ if (select_lex->save_item_list_names(thd))
+ DBUG_RETURN(-1);
+ }
+
if (thd->lex->current_select->first_cond_optimization)
{
if ( conds && ! thd->lex->current_select->merged_into)
@@ -1965,9 +1985,14 @@ bool JOIN::make_range_rowid_filters()
bool force_index_save= tab->table->force_index;
tab->table->force_index= true;
quick_select_return rc;
+ /*
+ EQ_FUNC and EQUAL_FUNC already sent unusable key notes (if any)
+ during update_ref_and_keys(). Have only other functions raise notes
+ from can_optimize_scalar_range().
+ */
rc= sel->test_quick_select(thd, filter_map, (table_map) 0,
(ha_rows) HA_POS_ERROR, true, false, true,
- true);
+ true, Item_func::BITMAP_EXCEPT_ANY_EQUALITY);
tab->table->force_index= force_index_save;
if (rc == SQL_SELECT::ERROR || thd->is_error())
{
@@ -5194,13 +5219,19 @@ static bool get_quick_record_count(THD *thd, SQL_SELECT *select,
if (unlikely(check_stack_overrun(thd, STACK_MIN_SIZE, buff)))
DBUG_RETURN(false); // Fatal error flag is set
if (select)
- {
+ {
select->head=table;
table->reginfo.impossible_range=0;
+ /*
+ EQ_FUNC and EQUAL_FUNC already sent unusable key notes (if any)
+ during update_ref_and_keys(). Have only other functions raise notes
+ from can_optimize_scalar_range().
+ */
error= select->test_quick_select(thd, *(key_map *)keys, (table_map) 0,
limit, 0, FALSE,
TRUE, /* remove_where_parts*/
- FALSE, TRUE);
+ FALSE,
+ Item_func::BITMAP_EXCEPT_ANY_EQUALITY);
if (error == SQL_SELECT::OK && select->quick)
{
@@ -5915,11 +5946,15 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
This is can't be to high as otherwise we are likely to use
table scan.
*/
- s->worst_seeks= MY_MIN((double) s->found_records / 10,
- (double) s->read_time*3);
- if (s->worst_seeks < 2.0) // Fix for small tables
- s->worst_seeks=2.0;
-
+ /* Largest integer that can be stored in double (no compiler warning) */
+ s->worst_seeks= (double) (1ULL << 53);
+ if (thd->variables.optimizer_adjust_secondary_key_costs != 2)
+ {
+ s->worst_seeks= MY_MIN((double) s->found_records / 10,
+ (double) s->read_time*3);
+ if (s->worst_seeks < 2.0) // Fix for small tables
+ s->worst_seeks=2.0;
+ }
/*
Add to stat->const_keys those indexes for which all group fields or
all select distinct fields participate in one index.
@@ -7031,6 +7066,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field)
{
field->raise_note_cannot_use_key_part(thd, key, part,
equal_str,
+ key_field->cond->compare_collation(),
key_field->val,
compat);
}
@@ -7896,8 +7932,27 @@ double cost_for_index_read(const THD *thd, const TABLE *table, uint key,
if (table->covering_keys.is_set(key))
cost= file->keyread_time(key, 1, records);
else
+ {
cost= ((file->keyread_time(key, 0, records) +
file->read_time(key, 1, MY_MIN(records, worst_seeks))));
+ if (thd->variables.optimizer_adjust_secondary_key_costs == 1 &&
+ file->is_clustering_key(0))
+ {
+ /*
+ According to benchmarks done in 11.0 to calculate the new cost
+ model secondary key ranges are about 7x slower than primary
+ key ranges for big tables. Here we are a bit conservative and
+ only calculate with 5x. The reason for having it only 5x and
+ not for example 7x is is that choosing plans with more rows
+ that are read (ignored by the WHERE clause) causes the 10.x
+ optimizer to believe that there are more rows in the result
+ set, which can cause problems in finding the best join order.
+ Note: A clustering primary key is always key 0.
+ */
+ double clustering_key_cost= file->read_time(0, 1, records);
+ cost= MY_MAX(cost, clustering_key_cost * 5);
+ }
+ }
DBUG_PRINT("statistics", ("cost: %.3f", cost));
DBUG_RETURN(cost);
@@ -8071,6 +8126,14 @@ best_access_path(JOIN *join,
double keyread_tmp= 0;
ha_rows rec;
bool best_uses_jbuf= FALSE;
+ /*
+ if optimizer_use_condition_selectivity adjust filter cost to be slightly
+ higher to ensure that ref|filter is not less than range over same
+ number of rows
+ */
+ double filter_setup_cost= (thd->variables.
+ optimizer_adjust_secondary_key_costs == 2 ?
+ 1.0 : 0.0);
MY_BITMAP *eq_join_set= &s->table->eq_join_set;
KEYUSE *hj_start_key= 0;
SplM_plan_info *spl_plan= 0;
@@ -8301,6 +8364,9 @@ best_access_path(JOIN *join,
trace_access_idx.add("used_range_estimates", true);
tmp= adjust_quick_cost(table->opt_range[key].cost,
table->opt_range[key].rows);
+ keyread_tmp= table->file->keyread_time(key, 1,
+ table->opt_range[key].
+ rows);
goto got_cost;
}
else
@@ -8625,6 +8691,7 @@ best_access_path(JOIN *join,
type == JT_EQ_REF ? 0.5 * tmp : MY_MIN(tmp, keyread_tmp);
double access_cost_factor= MY_MIN((tmp - key_access_cost) / rows, 1.0);
+
if (!(records < s->worst_seeks &&
records <= thd->variables.max_seeks_for_key))
{
@@ -8641,7 +8708,9 @@ best_access_path(JOIN *join,
}
if (filter)
{
- tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows);
+ tmp-= (filter->get_adjusted_gain(rows) -
+ filter->get_cmp_gain(rows) -
+ filter_setup_cost);
DBUG_ASSERT(tmp >= 0);
trace_access_idx.add("rowid_filter_key",
table->key_info[filter->key_no].name);
@@ -8891,7 +8960,7 @@ best_access_path(JOIN *join,
access_cost_factor);
if (filter)
{
- tmp-= filter->get_adjusted_gain(rows);
+ tmp-= filter->get_adjusted_gain(rows) - filter_setup_cost;
DBUG_ASSERT(tmp >= 0);
}
@@ -13049,7 +13118,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
HA_POS_ERROR :
join->unit->lim.get_select_limit()),
0,
- FALSE, FALSE, FALSE)) ==
+ FALSE, FALSE, FALSE,
+ Item_func::BITMAP_ALL)) ==
SQL_SELECT::IMPOSSIBLE_RANGE)
{
/*
@@ -13064,7 +13134,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
OPTION_FOUND_ROWS ?
HA_POS_ERROR :
join->unit->lim.get_select_limit()),
- 0, FALSE, FALSE, FALSE, TRUE)) ==
+ 0, FALSE, FALSE, FALSE,
+ Item_func::BITMAP_NONE)) ==
SQL_SELECT::IMPOSSIBLE_RANGE)
DBUG_RETURN(1); // Impossible WHERE
}
@@ -23199,7 +23270,9 @@ test_if_quick_select(JOIN_TAB *tab)
res= tab->select->test_quick_select(tab->join->thd, tab->keys,
(table_map) 0, HA_POS_ERROR, 0,
FALSE, /*remove where parts*/FALSE,
- FALSE, /* no warnings */ TRUE);
+ FALSE,
+ /* no unusable key notes */
+ Item_func::BITMAP_NONE);
if (tab->explain_plan && tab->explain_plan->range_checked_fer)
tab->explain_plan->range_checked_fer->collect_data(tab->select->quick);
@@ -25250,7 +25323,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
HA_POS_ERROR :
tab->join->unit->
lim.get_select_limit(),
- TRUE, TRUE, FALSE, FALSE);
+ TRUE, TRUE, FALSE, FALSE,
+ Item_func::BITMAP_ALL);
// if we cannot use quick select
if (res != SQL_SELECT::OK || !tab->select->quick)
{
@@ -25355,7 +25429,8 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
join->select_options & OPTION_FOUND_ROWS ?
HA_POS_ERROR :
join->unit->lim.get_select_limit(),
- TRUE, FALSE, FALSE, FALSE);
+ TRUE, FALSE, FALSE, FALSE,
+ Item_func::BITMAP_ALL);
if (res == SQL_SELECT::ERROR)
{
*fatal_error= true;
@@ -29163,7 +29238,6 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
bool distinct,const char *message)
{
THD *thd=join->thd;
- select_result *result=join->result;
DBUG_ENTER("select_describe");
if (join->select_lex->pushdown_select)
@@ -29198,7 +29272,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
if (unit->explainable())
{
- if (mysql_explain_union(thd, unit, result))
+ if (mysql_explain_union(thd, unit, unit->result))
DBUG_VOID_RETURN;
}
}
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-29 08:13:04 +0000