Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.2098 06/05/02 18:31:20 igor@stripped +5 -0
Fixed bug #14292: performance degradation for a benchmark query.
This performance degradation was due to the fact that some
cost evaluation code added into 4.1 in the function find_best was
not merged into the code of the function best_access_path added
together with other code for greedy optimizer.
Added a parameter to the function print_plan. The parameter contains
accumulated cost for a given partial join.
The patch does not include a special test case since this performance
degradation is hard to reproduse with a simple example.
TODO: make the function find_best use the function best_access_path
in order to remove duplication of code which might result in incomplete
merges in the future.
sql/sql_test.cc
1.45 06/05/02 18:31:14 igor@stripped +8 -6
Fixed bug #14292: performance degradation for a benchmark query.
Added a parameter to the function print_plan. The parameter contains
accumulated cost for a given partial join.
sql/sql_select.cc
1.408 06/05/02 18:31:14 igor@stripped +61 -33
Fixed bug #14292: performance degradation for a benchmark query.
This performance degradation was due to the fact that some
cost evaluation code added into 4.1 in the function find_best was
not merged into the code of the function best_access_path added
together with other code for greedy optimizer.
sql/mysql_priv.h
1.383 06/05/02 18:31:13 igor@stripped +2 -2
Fixed bug #14292: performance degradation for a benchmark query.
Added a parameter to the function print_plan. The parameter contains
accumulated cost for a given partial join.
mysql-test/r/subselect.result
1.139 06/05/02 18:31:13 igor@stripped +3 -3
Fixed bug #14292: performance degradation for a benchmark query.
Adjusted test results.
mysql-test/r/delete.result
1.23 06/05/02 18:31:13 igor@stripped +2 -2
Fixed bug #14292: performance degradation for a benchmark query.
Adjusted test results.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: igor
# Host: rurik.mysql.com
# Root: /home/igor/dev/mysql-5.0-0
--- 1.382/sql/mysql_priv.h 2006-04-13 13:05:38 -07:00
+++ 1.383/sql/mysql_priv.h 2006-05-02 18:31:13 -07:00
@@ -1066,8 +1066,8 @@
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 */
--- 1.407/sql/sql_select.cc 2006-04-20 23:28:57 -07:00
+++ 1.408/sql/sql_select.cc 2006-05-02 18:31:14 -07:00
@@ -3318,33 +3318,46 @@
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 @@
}
}
/* 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 @@
(!(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 @@
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 @@
/* 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 @@
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 @@
'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 @@
--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 @@
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 @@
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 @@
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 @@
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 @@
}
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 @@
*/
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 @@
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 @@
ha_rows rec;
double tmp;
THD *thd= join->thd;
-
if (!rest_tables)
{
DBUG_PRINT("best",("read_time: %g record_count: %g",read_time,
--- 1.44/sql/sql_test.cc 2006-01-03 08:54:38 -08:00
+++ 1.45/sql/sql_test.cc 2006-05-02 18:31:14 -07:00
@@ -230,8 +230,8 @@
*/
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 @@
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 */
--- 1.138/mysql-test/r/subselect.result 2006-03-31 21:26:11 -08:00
+++ 1.139/mysql-test/r/subselect.result 2006-05-02 18:31:13 -07:00
@@ -1354,10 +1354,10 @@
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
--- 1.22/mysql-test/r/delete.result 2005-05-30 10:48:35 -07:00
+++ 1.23/mysql-test/r/delete.result 2006-05-02 18:31:13 -07:00
@@ -186,8 +186,8 @@
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
| Thread |
|---|
| • bk commit into 5.0 tree (igor:1.2098) BUG#14292 | igor | 3 May |
