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.2103 06/05/03 19:38:37 igor@stripped +3 -0
Merge rurik.mysql.com:/home/igor/mysql-5.0
into rurik.mysql.com:/home/igor/dev/mysql-5.0-0
sql/sql_select.cc
1.409 06/05/03 19:38:30 igor@stripped +0 -0
Auto merged
sql/mysql_priv.h
1.384 06/05/03 19:38:29 igor@stripped +0 -0
Auto merged
mysql-test/r/subselect.result
1.140 06/05/03 19:38:29 igor@stripped +0 -0
Auto merged
# 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/RESYNC
--- 1.383/sql/mysql_priv.h 2006-04-18 08:07:25 -07:00
+++ 1.384/sql/mysql_priv.h 2006-05-03 19:38:29 -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.408/sql/sql_select.cc 2006-04-24 06:49:32 -07:00
+++ 1.409/sql/sql_select.cc 2006-05-03 19:38:30 -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.139/mysql-test/r/subselect.result 2006-04-28 03:06:51 -07:00
+++ 1.140/mysql-test/r/subselect.result 2006-05-03 19:38:29 -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
| Thread |
|---|
| • bk commit into 5.0 tree (igor:1.2103) | igor | 4 May |
