summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/delete.result4
-rw-r--r--mysql-test/r/subselect.result6
-rw-r--r--sql/mysql_priv.h4
-rw-r--r--sql/sql_select.cc94
-rw-r--r--sql/sql_test.cc14
5 files changed, 76 insertions, 46 deletions
diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result
index ddfeeac77b5..05f1c967e77 100644
--- a/mysql-test/r/delete.result
+++ b/mysql-test/r/delete.result
@@ -186,8 +186,8 @@ a b a b a b
explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
-1 SIMPLE t2 index PRIMARY PRIMARY 8 NULL 3 Using where; Using index
-1 SIMPLE t3 index PRIMARY PRIMARY 8 NULL 3 Using where; Using index
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b,test.t1.b 1 Using index
delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
select * from t3;
a b
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index bd0fa5ae661..8768b22a70d 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1354,10 +1354,10 @@ a
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 5 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using index
-2 DEPENDENT SUBQUERY t1 ref a a 10 func,test.t3.a 1167 Using where; Using index
+2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 Using where; Using index
+2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using where; Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 4eb444e9854..665f6f2ca5a 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1066,8 +1066,8 @@ pthread_handler_t handle_manager(void *arg);
void print_where(COND *cond,const char *info);
void print_cached_tables(void);
void TEST_filesort(SORT_FIELD *sortorder,uint s_length);
-void print_plan(JOIN* join, double read_time, double record_count,
- uint idx, const char *info);
+void print_plan(JOIN* join,uint idx, double record_count, double read_time,
+ double current_read_time, const char *info);
#endif
void mysql_print_status();
/* key.cc */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 47c5281b258..ff6e5cfb984 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3318,33 +3318,46 @@ best_access_path(JOIN *join,
for (keyuse=s->keyuse ; keyuse->table == table ;)
{
key_part_map found_part= 0;
- table_map found_ref= 0;
- uint found_ref_or_null= 0;
- uint key= keyuse->key;
+ table_map found_ref= 0;
+ uint key= keyuse->key;
KEY *keyinfo= table->key_info+key;
bool ft_key= (keyuse->keypart == FT_KEYPART);
+ uint found_ref_or_null= 0;
/* Calculate how many key segments of the current key we can use */
start_key= keyuse;
do
{ /* for each keypart */
uint keypart= keyuse->keypart;
- uint found_part_ref_or_null= KEY_OPTIMIZE_REF_OR_NULL;
+ table_map best_part_found_ref= 0;
+ double best_prev_record_reads= DBL_MAX;
do
{
if (!(remaining_tables & keyuse->used_tables) &&
!(found_ref_or_null & keyuse->optimize))
{
found_part|= keyuse->keypart_map;
- found_ref|= keyuse->used_tables;
+ double tmp= prev_record_reads(join,
+ (found_ref |
+ keyuse->used_tables));
+ if (tmp < best_prev_record_reads)
+ {
+ best_part_found_ref= keyuse->used_tables;
+ best_prev_record_reads= tmp;
+ }
if (rec > keyuse->ref_table_rows)
rec= keyuse->ref_table_rows;
- found_part_ref_or_null&= keyuse->optimize;
+ /*
+ If there is one 'key_column IS NULL' expression, we can
+ use this ref_or_null optimisation of this field
+ */
+ found_ref_or_null|= (keyuse->optimize &
+ KEY_OPTIMIZE_REF_OR_NULL);
}
keyuse++;
- found_ref_or_null|= found_part_ref_or_null;
} while (keyuse->table == table && keyuse->key == key &&
keyuse->keypart == keypart);
+ found_ref|= best_part_found_ref;
} while (keyuse->table == table && keyuse->key == key);
/*
@@ -3409,17 +3422,17 @@ best_access_path(JOIN *join,
}
}
/* Limit the number of matched rows */
- tmp = records;
+ tmp= records;
set_if_smaller(tmp, (double) thd->variables.max_seeks_for_key);
if (table->used_keys.is_set(key))
{
/* we can use only index tree */
uint keys_per_block= table->file->block_size/2/
(keyinfo->key_length+table->file->ref_length)+1;
- tmp = record_count*(tmp+keys_per_block-1)/keys_per_block;
+ tmp= record_count*(tmp+keys_per_block-1)/keys_per_block;
}
else
- tmp = record_count*min(tmp,s->worst_seeks);
+ tmp= record_count*min(tmp,s->worst_seeks);
}
}
else
@@ -3433,7 +3446,7 @@ best_access_path(JOIN *join,
(!(table->file->index_flags(key, 0, 0) & HA_ONLY_WHOLE_INDEX) ||
found_part == PREV_BITS(uint,keyinfo->key_parts)))
{
- max_key_part=max_part_bit(found_part);
+ max_key_part= max_part_bit(found_part);
/*
Check if quick_range could determinate how many rows we
will match
@@ -3444,8 +3457,8 @@ best_access_path(JOIN *join,
else
{
/* Check if we have statistic about the distribution */
- if ((records = keyinfo->rec_per_key[max_key_part-1]))
- tmp = records;
+ if ((records= keyinfo->rec_per_key[max_key_part-1]))
+ tmp= records;
else
{
/*
@@ -3506,13 +3519,13 @@ best_access_path(JOIN *join,
/* we can use only index tree */
uint keys_per_block= table->file->block_size/2/
(keyinfo->key_length+table->file->ref_length)+1;
- tmp = record_count*(tmp+keys_per_block-1)/keys_per_block;
+ tmp= record_count*(tmp+keys_per_block-1)/keys_per_block;
}
else
- tmp = record_count*min(tmp,s->worst_seeks);
+ tmp= record_count*min(tmp,s->worst_seeks);
}
else
- tmp = best_time; // Do nothing
+ tmp= best_time; // Do nothing
}
} /* not ft_key */
if (tmp < best_time - records/(double) TIME_FOR_COMPARE)
@@ -3864,7 +3877,8 @@ optimize_straight_join(JOIN *join, table_map join_tables)
for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
{
/* Find the best access method from 's' to the current partial plan */
- best_access_path(join, s, join->thd, join_tables, idx, record_count, read_time);
+ best_access_path(join, s, join->thd, join_tables, idx,
+ record_count, read_time);
/* compute the cost of the new plan extended with 's' */
record_count*= join->positions[idx].records_read;
read_time+= join->positions[idx].read_time;
@@ -3987,8 +4001,9 @@ greedy_search(JOIN *join,
'join->best_positions' contains a complete optimal extension of the
current partial QEP.
*/
- DBUG_EXECUTE("opt", print_plan(join, read_time, record_count,
- join->tables, "optimal"););
+ DBUG_EXECUTE("opt", print_plan(join, join->tables,
+ record_count, read_time, read_time,
+ "optimal"););
DBUG_VOID_RETURN;
}
@@ -4019,8 +4034,9 @@ greedy_search(JOIN *join,
--rem_size;
++idx;
- DBUG_EXECUTE("opt",
- print_plan(join, read_time, record_count, idx, "extended"););
+ DBUG_EXECUTE("opt", print_plan(join, join->tables,
+ record_count, read_time, read_time,
+ "extended"););
} while (TRUE);
}
@@ -4043,13 +4059,14 @@ greedy_search(JOIN *join,
read_time the cost of the best partial plan
search_depth maximum depth of the recursion and thus size of the found
optimal plan (0 < search_depth <= join->tables+1).
- prune_level pruning heuristics that should be applied during optimization
+ prune_level pruning heuristics that should be applied during
+ optimization
(values: 0 = EXHAUSTIVE, 1 = PRUNE_BY_TIME_OR_ROWS)
DESCRIPTION
The procedure searches for the optimal ordering of the query tables in set
- 'remaining_tables' of size N, and the corresponding optimal access paths to each
- table. The choice of a table order and an access path for each table
+ 'remaining_tables' of size N, and the corresponding optimal access paths to
+ each table. The choice of a table order and an access path for each table
constitutes a query execution plan (QEP) that fully specifies how to
execute the query.
@@ -4159,8 +4176,8 @@ best_extension_by_limited_search(JOIN *join,
double best_record_count= DBL_MAX;
double best_read_time= DBL_MAX;
- DBUG_EXECUTE("opt",
- print_plan(join, read_time, record_count, idx, "part_plan"););
+ DBUG_EXECUTE("opt", print_plan(join, idx, record_count, read_time, read_time,
+ "part_plan"););
for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
{
@@ -4172,7 +4189,8 @@ best_extension_by_limited_search(JOIN *join,
double current_record_count, current_read_time;
/* Find the best access method from 's' to the current partial plan */
- best_access_path(join, s, thd, remaining_tables, idx, record_count, read_time);
+ best_access_path(join, s, thd, remaining_tables, idx,
+ record_count, read_time);
/* Compute the cost of extending the plan with 's' */
current_record_count= record_count * join->positions[idx].records_read;
current_read_time= read_time + join->positions[idx].read_time;
@@ -4181,7 +4199,12 @@ best_extension_by_limited_search(JOIN *join,
if ((current_read_time +
current_record_count / (double) TIME_FOR_COMPARE) >= join->best_read)
{
- DBUG_EXECUTE("opt", print_plan(join, read_time, record_count, idx,
+ DBUG_EXECUTE("opt", print_plan(join, idx+1,
+ current_record_count,
+ read_time,
+ (current_read_time +
+ current_record_count /
+ (double) TIME_FOR_COMPARE),
"prune_by_cost"););
restore_prev_nj_state(s);
continue;
@@ -4210,7 +4233,10 @@ best_extension_by_limited_search(JOIN *join,
}
else
{
- DBUG_EXECUTE("opt", print_plan(join, read_time, record_count, idx,
+ DBUG_EXECUTE("opt", print_plan(join, idx+1,
+ current_record_count,
+ read_time,
+ current_read_time,
"pruned_by_heuristic"););
restore_prev_nj_state(s);
continue;
@@ -4238,7 +4264,8 @@ best_extension_by_limited_search(JOIN *join,
*/
current_read_time+= current_record_count / (double) TIME_FOR_COMPARE;
if (join->sort_by_table &&
- join->sort_by_table != join->positions[join->const_tables].table->table)
+ join->sort_by_table !=
+ join->positions[join->const_tables].table->table)
/* We have to make a temp table */
current_read_time+= current_record_count;
if ((search_depth == 1) || (current_read_time < join->best_read))
@@ -4247,8 +4274,10 @@ best_extension_by_limited_search(JOIN *join,
sizeof(POSITION) * (idx + 1));
join->best_read= current_read_time - 0.001;
}
- DBUG_EXECUTE("opt", print_plan(join, current_read_time,
- current_record_count, idx,
+ DBUG_EXECUTE("opt", print_plan(join, idx+1,
+ current_record_count,
+ read_time,
+ current_read_time,
"full_plan"););
}
restore_prev_nj_state(s);
@@ -4269,7 +4298,6 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
ha_rows rec;
double tmp;
THD *thd= join->thd;
-
if (!rest_tables)
{
DBUG_PRINT("best",("read_time: %g record_count: %g",read_time,
diff --git a/sql/sql_test.cc b/sql/sql_test.cc
index 975cc19ea3f..0784685432a 100644
--- a/sql/sql_test.cc
+++ b/sql/sql_test.cc
@@ -230,8 +230,8 @@ TEST_join(JOIN *join)
*/
void
-print_plan(JOIN* join, double read_time, double record_count,
- uint idx, const char *info)
+print_plan(JOIN* join, uint idx, double record_count, double read_time,
+ double current_read_time, const char *info)
{
uint i;
POSITION pos;
@@ -245,13 +245,15 @@ print_plan(JOIN* join, double read_time, double record_count,
DBUG_LOCK_FILE;
if (join->best_read == DBL_MAX)
{
- fprintf(DBUG_FILE,"%s; idx:%u, best: DBL_MAX, current:%g\n",
- info, idx, read_time);
+ fprintf(DBUG_FILE,
+ "%s; idx:%u, best: DBL_MAX, atime: %g, itime: %g, count: %g\n",
+ info, idx, current_read_time, read_time, record_count);
}
else
{
- fprintf(DBUG_FILE,"%s; idx: %u, best: %g, current: %g\n",
- info, idx, join->best_read, read_time);
+ fprintf(DBUG_FILE,
+ "%s; idx:%u, best: %g, atime: %g, itime: %g, count: %g\n",
+ info, idx, join->best_read, current_read_time, read_time, record_count);
}
/* Print the tables in JOIN->positions */
View Lorry Controller Status