Below is the list of changes that have just been committed into a local
5.1 repository of psergey. When psergey 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@stripped, 2006-07-28 21:27:01+04:00, sergefp@stripped +75 -0
BUG#14940 "MySQL choose wrong index", v.2
- Make the range-et-al optimizer produce E(#table records after table
condition is applied),
- Make the join optimizer use this value,
- Add "filtered" column to EXPLAIN EXTENDED to show
fraction of records left after table condition is applied
- Adjust test results, add comments
mysql-test/r/archive_gis.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +15
-15
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/auto_increment.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2
-2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/bdb_gis.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +15 -15
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/bench_count_distinct.result@stripped, 2006-07-28 21:26:55+04:00,
sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/binlog_stm_blackhole.result@stripped, 2006-07-28 21:26:55+04:00,
sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/case.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +6 -6
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/cast.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/compress.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/ctype_collate.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2
-2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/ctype_cp1250_ch.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +1
-0
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/date_formats.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2
-2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/distinct.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/fulltext.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +4 -4
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_compress.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +4
-4
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_crypt.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_default.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_encrypt.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +8 -8
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_gconcat.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +6
-6
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_group.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_if.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +4 -4
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_in.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_like.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +10 -6
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_math.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +26 -26
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_op.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +4 -4
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_regexp.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_set.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_str.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_system.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_test.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +12 -12
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/func_time.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +4 -4
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/gis.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +23 -23
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/group_by.result@stripped, 2006-07-28 21:26:55+04:00, sergefp@stripped +4 -4
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/group_min_max.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +50
-30
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/having.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/heap.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +1 -1
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/heap_hash.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +1 -1
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/index_merge.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/index_merge_innodb.result@stripped, 2006-07-28 21:26:56+04:00,
sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/index_merge_ror.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped
+14 -14
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/innodb_gis.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +15 -15
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/insert_update.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +4
-4
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/join.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +12 -0
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/join_nested.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +93
-93
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/key_diff.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +1 -1
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/myisam.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +3 -3
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/ndb_gis.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +30 -30
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/negation_elimination.result@stripped, 2006-07-28 21:26:56+04:00,
sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/null.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +6 -6
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/olap.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/partition_pruning.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped
+40 -0
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/query_cache.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/row.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/rpl_get_lock.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2
-2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/rpl_master_pos_wait.result@stripped, 2006-07-28 21:26:56+04:00,
sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/select.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +5 -5
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/ssl.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +5 -5
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/ssl_compress.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +5 -5
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/subselect.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +191
-191
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/type_blob.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/union.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +9 -9
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/varbinary.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/variables.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +6 -6
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/r/view.result@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +27 -27
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/t/ctype_cp1250_ch.test@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +3
-0
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/t/func_like.test@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +2 -2
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/t/group_min_max.test@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +10
-10
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/t/index_merge_ror.test@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +0
-1
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/t/index_merge_ror_cpk.test@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped
+1 -1
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/t/join.test@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +19 -0
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
mysql-test/t/partition_pruning.test@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped
+16 -0
BUG#14940: Adjust the test results: EXPLAIN EXTENDED now has extra "filtered" column
sql/opt_range.cc@stripped, 2006-07-28 21:26:56+04:00, sergefp@stripped +76 -24
BUG#14940: Make range/index_merge/group-min-max optimizer produce
TABLE::quick_condition_rows -
estimate of #records that will match the table condition.
sql/sql_class.cc@stripped, 2006-07-28 21:26:57+04:00, sergefp@stripped +5 -0
BUG#14940: Add "filtered" column to output of EXPLAIN EXTENDED
sql/sql_select.cc@stripped, 2006-07-28 21:26:57+04:00, sergefp@stripped +55 -7
BUG#14940:
- Make the join optimizer to use TABLE::quick_condition_rows=
= E(#table records after filtering with table condition)
- Add "filtered" column to output of EXPLAIN EXTENDED
sql/sql_select.h@stripped, 2006-07-28 21:26:57+04:00, sergefp@stripped +26 -3
BUG#14940: Added comments
sql/table.h@stripped, 2006-07-28 21:26:57+04:00, sergefp@stripped +17 -0
BUG#14940: Added comments
# 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: sergefp
# Host: newbox.mylan
# Root: /home/psergey/mysql-5.1-bug14940-r4
--- 1.223/sql/opt_range.cc 2006-07-28 21:27:09 +04:00
+++ 1.224/sql/opt_range.cc 2006-07-28 21:27:09 +04:00
@@ -478,7 +478,8 @@
static SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param,COND *cond);
static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts);
-static ha_rows check_quick_select(PARAM *param,uint index,SEL_ARG *key_tree);
+static ha_rows check_quick_select(PARAM *param,uint index,SEL_ARG *key_tree,
+ bool update_tbl_stats);
static ha_rows check_quick_keys(PARAM *param,uint index,SEL_ARG *key_tree,
char *min_key,uint min_key_flag,
char *max_key, uint max_key_flag);
@@ -488,6 +489,7 @@
MEM_ROOT *alloc = NULL);
static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
bool index_read_must_be_used,
+ bool update_tbl_stats,
double read_time);
static
TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree,
@@ -1854,16 +1856,43 @@
quick - Parameter to use when reading records.
In the table struct the following information is updated:
- quick_keys - Which keys can be used
- quick_rows - How many rows the key matches
+ quick_keys - Which keys can be used
+ quick_rows - How many rows the key matches
+ quick_condition_rows - E(# rows that will satisfy the table condition)
+
+ IMPLEMENTATION
+ quick_condition_rows value is obtained as follows:
+
+ It is a minimum of E(#output rows) for all considered table access
+ methods (range and index_merge accesses over various indexes).
+
+ The obtained value is not a true E(#rows that satisfy table condition)
+ but rather a pessimistic estimate. To obtain a true E(#...) one would
+ need to combine estimates of various access methods, taking into account
+ correlations between sets of rows they will return.
+
+ For example, if values of tbl.key1 and tbl.key2 are independent (a right
+ assumption if we have no infromation about their correlation) then the
+ correct estimate will be:
+
+ E(#rows("tbl.key1 < c1 AND tbl.key2 < c2")) =
+ = E(#rows(tbl.key1 < c1)) / total_rows(tbl) * E(#rows(tbl.key2 < c2) /
+ total_rows(tbl).
+
+ which is smaller than MIN(rows(tbl.key1 < c1), rows(tbl.key2 < c2)).
TODO
- Check if this function really needs to modify keys_to_use, and change the
- code to pass it by reference if it doesn't.
+ * Change the value returned in quick_condition_rows from a pessimistic
+ estimate to true E(#rows that satisfy table condition).
+ (we can re-use some of E(#rows) calcuation code from index_merge/intersection
+ for this)
+
+ * Check if this function really needs to modify keys_to_use, and change the
+ code to pass it by reference if it doesn't.
- In addition to force_quick_range other means can be (an usually are) used
- to make this function prefer range over full table scan. Figure out if
- force_quick_range is really needed.
+ * In addition to force_quick_range other means can be (an usually are) used
+ to make this function prefer range over full table scan. Figure out if
+ force_quick_range is really needed.
RETURN
-1 if impossible select (i.e. certainly no rows will be selected)
@@ -2015,10 +2044,15 @@
Notice that it can be constructed no matter if there is a range tree.
*/
group_trp= get_best_group_min_max(¶m, tree);
- if (group_trp && group_trp->read_cost < best_read_time)
+ if (group_trp)
{
- best_trp= group_trp;
- best_read_time= best_trp->read_cost;
+ param.table->quick_condition_rows= min(group_trp->records,
+ head->file->stats.records);
+ if (group_trp->read_cost < best_read_time)
+ {
+ best_trp= group_trp;
+ best_read_time= best_trp->read_cost;
+ }
}
if (tree)
@@ -2034,7 +2068,7 @@
bool can_build_covering= FALSE;
/* Get best 'range' plan and prepare data for making other plans */
- if ((range_trp= get_key_scans_params(¶m, tree, FALSE,
+ if ((range_trp= get_key_scans_params(¶m, tree, FALSE, TRUE,
best_read_time)))
{
best_trp= range_trp;
@@ -2077,13 +2111,15 @@
SEL_IMERGE *imerge;
TABLE_READ_PLAN *best_conj_trp= NULL, *new_conj_trp;
LINT_INIT(new_conj_trp); /* no empty index_merge lists possible */
-
DBUG_PRINT("info",("No range reads possible,"
" trying to construct index_merge"));
List_iterator_fast<SEL_IMERGE> it(tree->merges);
while ((imerge= it++))
{
new_conj_trp= get_best_disjunct_quick(¶m, imerge, best_read_time);
+ if (new_conj_trp)
+ set_if_smaller(param.table->quick_condition_rows,
+ new_conj_trp->records);
if (!best_conj_trp || (new_conj_trp && new_conj_trp->read_cost <
best_conj_trp->read_cost))
best_conj_trp= new_conj_trp;
@@ -3381,7 +3417,7 @@
{
DBUG_EXECUTE("info", print_sel_tree(param, *ptree, &(*ptree)->keys_map,
"tree in SEL_IMERGE"););
- if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, read_time)))
+ if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE, read_time)))
{
/*
One of index scans in this index_merge is more expensive than entire
@@ -4282,6 +4318,7 @@
ha_rows best_rows = double2rows(intersect_best->out_rows);
if (!best_rows)
best_rows= 1;
+ set_if_smaller(param->table->quick_condition_rows, best_rows);
trp->records= best_rows;
trp->index_scan_costs= intersect_best->index_scan_costs;
trp->cpk_scan= cpk_scan_used? cpk_scan: NULL;
@@ -4432,6 +4469,8 @@
trp->read_cost= total_cost;
trp->records= records;
trp->cpk_scan= NULL;
+ if (records < param->table->quick_condition_rows)
+ param->table->quick_condition_rows= records;
DBUG_PRINT("info",
("Returning covering ROR-intersect plan: cost %g, records %lu",
@@ -4456,7 +4495,8 @@
*/
static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
- bool index_read_must_be_used,
+ bool index_read_must_be_used,
+ bool update_tbl_stats,
double read_time)
{
int idx;
@@ -4491,7 +4531,7 @@
bool read_index_only= index_read_must_be_used ? TRUE :
(bool) param->table->used_keys.is_set(keynr);
- found_records= check_quick_select(param, idx, *key);
+ found_records= check_quick_select(param, idx, *key, update_tbl_stats);
if (param->is_ror_scan)
{
tree->n_ror_scans++;
@@ -6637,9 +6677,12 @@
SYNOPSIS
check_quick_select
param Parameter from test_quick_select
- idx Number of index to use in PARAM::key SEL_TREE::key
- tree Transformed selection condition, tree->key[idx] holds intervals
- tree to be used for scanning.
+ idx Number of index to use in tree->keys
+ tree Transformed selection condition, tree->keys[idx]
+ holds the range tree to be used for scanning.
+ update_tbl_stats If true, update table->quick_keys with information
+ about range scan we've evaluated.
+
NOTES
param->is_ror_scan is set to reflect if the key scan is a ROR (see
is_key_scan_ror function for more info)
@@ -6653,7 +6696,7 @@
*/
static ha_rows
-check_quick_select(PARAM *param,uint idx,SEL_ARG *tree)
+check_quick_select(PARAM *param,uint idx,SEL_ARG *tree, bool update_tbl_stats)
{
ha_rows records;
bool cpk_scan;
@@ -6694,10 +6737,19 @@
records=check_quick_keys(param,idx,tree,param->min_key,0,param->max_key,0);
if (records != HA_POS_ERROR)
{
- param->table->quick_keys.set_bit(key);
+ if (update_tbl_stats)
+ {
+ param->table->quick_keys.set_bit(key);
+ param->table->quick_key_parts[key]=param->max_key_part+1;
+ param->table->quick_n_ranges[key]= param->n_ranges;
+ param->table->quick_condition_rows=
+ min(param->table->quick_condition_rows, records);
+ }
+ /*
+ Need to save quick_rows in any case as it is used when calculating
+ cost of ROR intersection:
+ */
param->table->quick_rows[key]=records;
- param->table->quick_key_parts[key]=param->max_key_part+1;
- param->table->quick_n_ranges[key]= param->n_ranges;
if (cpk_scan)
param->is_ror_scan= TRUE;
}
@@ -8847,7 +8899,7 @@
&cur_param_idx);
/* Check if this range tree can be used for prefix retrieval. */
cur_quick_prefix_records= check_quick_select(param, cur_param_idx,
- cur_index_tree);
+ cur_index_tree, TRUE);
}
cost_group_min_max(table, cur_index_info, used_key_parts,
cur_group_key_parts, tree, cur_index_tree,
--- 1.271/sql/sql_class.cc 2006-07-28 21:27:09 +04:00
+++ 1.272/sql/sql_class.cc 2006-07-28 21:27:09 +04:00
@@ -852,6 +852,11 @@
item->maybe_null=1;
field_list.push_back(item= new Item_return_int("rows", 10,
MYSQL_TYPE_LONGLONG));
+ if (lex->describe & DESCRIBE_EXTENDED)
+ {
+ field_list.push_back(item= new Item_float("filtered", 0.1234, 2, 4));
+ item->maybe_null=1;
+ }
item->maybe_null= 1;
field_list.push_back(new Item_empty_string("Extra", 255, cs));
return (result->send_fields(field_list,
--- 1.417/sql/sql_select.cc 2006-07-28 21:27:09 +04:00
+++ 1.418/sql/sql_select.cc 2006-07-28 21:27:09 +04:00
@@ -2074,6 +2074,7 @@
s->key_dependent= 0;
if (tables->schema_table)
table->file->stats.records= 2;
+ table->quick_condition_rows= table->file->records();
s->on_expr_ref= &tables->on_expr;
if (*s->on_expr_ref)
@@ -3754,15 +3755,25 @@
{ // Check full join
ha_rows rnd_records= s->found_records;
/*
- If there is a restriction on the table, assume that 25% of the
- rows can be skipped on next part.
- This is to force tables that this table depends on before this
- table
+ If there is a filtering condition on the table (i.e. ref analyzer found
+ at least one "table.keyXpartY= exprZ", where exprZ refers only to tables
+ preceding this table in the join order we're now considering), then
+ assume that 25% of the rows will be filtered out by this condition.
+
+ This heuristic is supposed to force tables used in exprZ to be before
+ this table in join order.
*/
if (found_constraint)
rnd_records-= rnd_records/4;
/*
+ If applicable, get a more accurate estimate. Don't use the two
+ heuristics at once.
+ */
+ if (s->table->quick_condition_rows != s->found_records)
+ rnd_records= s->table->quick_condition_rows;
+
+ /*
Range optimizer never proposes a RANGE if it isn't better
than FULL: so if RANGE is present, it's always preferred to FULL.
Here we estimate its cost.
@@ -3773,6 +3784,10 @@
For each record we:
- read record range through 'quick'
- skip rows which does not satisfy WHERE constraints
+ TODO:
+ We take into account possible use of join cache for ALL/index
+ access (see first else-branch below), but we don't take it into
+ account here for range/index_merge access. Find out why this is so.
*/
tmp= record_count *
(s->quick->read_time +
@@ -4342,6 +4357,8 @@
return;
DBUG_ENTER("best_extension_by_limited_search");
+ DBUG_EXECUTE("opt", print_plan(join, idx, read_time, record_count, idx,
+ "SOFAR:"););
/*
'join' is a partial plan with lower cost than the best plan so far,
@@ -14065,6 +14082,8 @@
item_list.push_back(item_null);
if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
item_list.push_back(item_null);
+ if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
item_list.push_back(new Item_string(message,strlen(message),cs));
if (result->send_data(item_list))
@@ -14110,6 +14129,9 @@
}
item_list.push_back(new Item_string(table_name_buffer, len, cs));
}
+ /* partitions */
+ if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
+ item_list.push_back(item_null);
/* type */
item_list.push_back(new Item_string(join_type_str[JT_ALL],
strlen(join_type_str[JT_ALL]),
@@ -14122,6 +14144,9 @@
item_list.push_back(item_null);
/* ref */
item_list.push_back(item_null);
+ /* in_rows */
+ if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
/* rows */
item_list.push_back(item_null);
/* extra */
@@ -14277,10 +14302,33 @@
item_list.push_back(item_null);
item_list.push_back(item_null);
}
+
/* Add "rows" field to item_list. */
- item_list.push_back(new Item_int((longlong) (ulonglong)
- join->best_positions[i]. records_read,
- 21));
+ ha_rows examined_rows;
+ if (tab->select && tab->select->quick)
+ examined_rows= tab->select->quick->records;
+ else if (tab->type == JT_NEXT || tab->type == JT_ALL)
+ examined_rows= tab->table->file->records();
+ else
+ examined_rows=(ha_rows)join->best_positions[i].records_read;
+
+ item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows,
+ 21));
+
+ /* Add "filtered" field to item_list. */
+ if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+ {
+ Item_float *filtered;
+ float f;
+ if (examined_rows)
+ f= 100.0 * join->best_positions[i].records_read / examined_rows;
+ else
+ f= 0.0;
+ item_list.push_back((filtered= new Item_float(f)));
+ filtered->decimals= 2;
+ }
+
+
/* Build "Extra" field and add it to item_list. */
my_bool key_read=table->key_read;
if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
--- 1.109/sql/sql_select.h 2006-07-28 21:27:09 +04:00
+++ 1.110/sql/sql_select.h 2006-07-28 21:27:09 +04:00
@@ -128,7 +128,21 @@
key_map checked_keys; /* Keys checked in find_best */
key_map needed_reg;
key_map keys; /* all keys with can be used */
- ha_rows records,found_records,read_time;
+
+ /* Either #rows in the table or 1 for const table. */
+ ha_rows records;
+ /*
+ Number of records that will be scanned (yes scanned, not returned) by the
+ best 'independent' access method, i.e. table scan or QUICK_*_SELECT)
+ */
+ ha_rows found_records;
+ /*
+ Cost of accessing the table using "ALL" or range/index_merge access
+ method (but not 'index' for some reason), i.e. this matches method which
+ E(#records) is in found_records.
+ */
+ ha_rows read_time;
+
table_map dependent,key_dependent;
uint use_quick,index;
uint status; // Save status for cache
@@ -156,14 +170,23 @@
enum_nested_loop_state sub_select(JOIN *join,JOIN_TAB *join_tab, bool
end_of_records);
-
-typedef struct st_position /* Used in find_best */
+/*
+ Information about a position of table within a join order. Used in join
+ optimization.
+*/
+typedef struct st_position
{
double records_read;
+ /*
+ Cost accessing the table in course of the entire complete join execution,
+ i.e. cost of one access method use (e.g. 'range' or 'ref' scan ) times
+ number the access method will be invoked.
+ */
double read_time;
JOIN_TAB *table;
KEYUSE *key;
} POSITION;
+
typedef struct st_rollup
{
--- 1.142/sql/table.h 2006-07-28 21:27:09 +04:00
+++ 1.143/sql/table.h 2006-07-28 21:27:09 +04:00
@@ -264,10 +264,27 @@
MY_BITMAP *read_set, *write_set; /* Active column sets */
query_id_t query_id;
+ /*
+ For each key that has quick_keys.is_set(key) == TRUE: estimate of #records
+ and max #key parts that range access would use.
+ */
ha_rows quick_rows[MAX_KEY];
+
+ /* Bitmaps of key parts that =const for the entire join. */
key_part_map const_key_parts[MAX_KEY];
+
uint quick_key_parts[MAX_KEY];
uint quick_n_ranges[MAX_KEY];
+
+ /*
+ Estimate of number of records that satisfy SARGable part of the table
+ condition, or table->file->records if no SARGable condition could be
+ constructed.
+ This value is used by join optimizer as an estimate of number of records
+ that will pass the table condition (condition that depends on fields of
+ this table and constants)
+ */
+ ha_rows quick_condition_rows;
/*
If this table has TIMESTAMP field with auto-set property (pointed by
--- 1.32/mysql-test/r/olap.result 2006-07-28 21:27:09 +04:00
+++ 1.33/mysql-test/r/olap.result 2006-07-28 21:27:09 +04:00
@@ -87,8 +87,8 @@
TV NULL NULL 600
NULL NULL NULL 7785
explain extended select product, country_id , year, sum(profit) from t1 group by product,
country_id, year with rollup;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 15 100.00 Using filesort
Warnings:
Note 1003 select `test`.`t1`.`product` AS `product`,`test`.`t1`.`country_id` AS
`country_id`,`test`.`t1`.`year` AS `year`,sum(`test`.`t1`.`profit`) AS `sum(profit)` from
`test`.`t1` group by `test`.`t1`.`product`,`test`.`t1`.`country_id`,`test`.`t1`.`year`
with rollup
select product, country_id , sum(profit) from t1 group by product desc, country_id with
rollup;
--- 1.85/mysql-test/r/union.result 2006-07-28 21:27:09 +04:00
+++ 1.86/mysql-test/r/union.result 2006-07-28 21:27:09 +04:00
@@ -83,10 +83,10 @@
(select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by
t1.b;
ERROR 42000: Table 't1' from one of the SELECTs cannot be used in global ORDER clause
explain extended (select a,b from t1 limit 2) union all (select a,b from t2 order by a
limit 1) order by b desc;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 4
-2 UNION t2 ALL NULL NULL NULL NULL 4 Using filesort
-NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
+2 UNION t2 ALL NULL NULL NULL NULL 4 100.00 Using filesort
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort
Warnings:
Note 1003 (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` limit 2)
union all (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by
`test`.`t2`.`a` limit 1) order by `b` desc
(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by
a) limit 2;
@@ -475,10 +475,10 @@
insert into t1 (b) values (1),(2),(2),(3);
insert into t2 (b) values (10),(11),(12),(13);
explain extended (select * from t1 where a=1) union (select * from t2 where a=1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1
-2 UNION t2 const PRIMARY PRIMARY 4 const 1
-NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 100.00
+2 UNION t2 const PRIMARY PRIMARY 4 const 1 100.00
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where
(`test`.`t1`.`a` = 1)) union (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from
`test`.`t2` where (`test`.`t2`.`a` = 1))
(select * from t1 where a=5) union (select * from t2 where a=1);
@@ -500,7 +500,7 @@
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const
tables
2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index
-2 UNION t2 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
+2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
explain (select * from t1 where a=1) union (select * from t1 where b=1);
id select_type table type possible_keys key key_len ref rows Extra
--- 1.32/mysql-test/r/ctype_collate.result 2006-07-28 21:27:09 +04:00
+++ 1.33/mysql-test/r/ctype_collate.result 2006-07-28 21:27:09 +04:00
@@ -516,8 +516,8 @@
charset('a') collation('a') coercibility('a') 'a'='A'
latin1 latin1_swedish_ci 4 1
explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select charset(_latin1'a') AS `charset('a')`,collation(_latin1'a') AS
`collation('a')`,coercibility(_latin1'a') AS `coercibility('a')`,(_latin1'a' =
_latin1'A') AS `'a'='A'`
SET CHARACTER SET koi8r;
--- 1.19/mysql-test/r/heap_hash.result 2006-07-28 21:27:09 +04:00
+++ 1.20/mysql-test/r/heap_hash.result 2006-07-28 21:27:09 +04:00
@@ -166,7 +166,7 @@
update t1 set new_col=left(btn,1);
explain select * from t1 where btn="a";
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL btn NULL NULL NULL 11 Using where
+1 SIMPLE t1 ALL btn NULL NULL NULL 14 Using where
explain select * from t1 where btn="a" and new_col="a";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref btn btn 11 const,const 2 Using where
--- 1.4/mysql-test/r/ctype_cp1250_ch.result 2006-07-28 21:27:09 +04:00
+++ 1.5/mysql-test/r/ctype_cp1250_ch.result 2006-07-28 21:27:09 +04:00
@@ -1,3 +1,4 @@
+drop table if exists t1;
SHOW COLLATION LIKE 'cp1250_czech_cs';
Collation Charset Id Default Compiled Sortlen
cp1250_czech_cs cp1250 34 Yes 2
--- 1.4/mysql-test/t/ctype_cp1250_ch.test 2006-07-28 21:27:09 +04:00
+++ 1.5/mysql-test/t/ctype_cp1250_ch.test 2006-07-28 21:27:09 +04:00
@@ -1,4 +1,7 @@
-- source include/have_cp1250_ch.inc
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
SHOW COLLATION LIKE 'cp1250_czech_cs';
--- 1.5/mysql-test/r/func_default.result 2006-07-28 21:27:09 +04:00
+++ 1.6/mysql-test/r/func_default.result 2006-07-28 21:27:09 +04:00
@@ -5,8 +5,8 @@
default(str) default(strnull) default(intg) default(rel)
def NULL 10 3.14
explain extended select default(str), default(strnull), default(intg), default(rel) from
t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 1
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select default(`test`.`t1`.`str`) AS
`default(str)`,default(`test`.`t1`.`strnull`) AS
`default(strnull)`,default(`test`.`t1`.`intg`) AS
`default(intg)`,default(`test`.`t1`.`rel`) AS `default(rel)` from `test`.`t1`
select * from t1 where str <> default(str);
--- 1.23/mysql-test/r/row.result 2006-07-28 21:27:09 +04:00
+++ 1.24/mysql-test/r/row.result 2006-07-28 21:27:09 +04:00
@@ -46,8 +46,8 @@
row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)))
NULL
explain extended select row(1,2,row(3,4)) IN (row(3,2,row(3,4)), row(1,2,row(3,NULL)));
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select ((1,2,(3,4)) in ((3,2,(3,4)),(1,2,(3,NULL)))) AS `row(1,2,row(3,4)) IN
(row(3,2,row(3,4)), row(1,2,row(3,NULL)))`
SELECT (1,2,3)=(0,NULL,3);
--- 1.147/mysql-test/r/subselect.result 2006-07-28 21:27:09 +04:00
+++ 1.148/mysql-test/r/subselect.result 2006-07-28 21:27:09 +04:00
@@ -3,8 +3,8 @@
(select 2)
2
explain extended select (select 2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select 2 AS `(select 2)`
@@ -13,10 +13,10 @@
1
2
explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1249 Select 4 was reduced during optimization
@@ -25,11 +25,11 @@
(SELECT (SELECT 0 UNION SELECT 0))
0
explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select (select 0 AS `0` union select 0 AS `0`) AS `(SELECT (SELECT 0 UNION
SELECT 0))`
@@ -43,10 +43,10 @@
SELECT (SELECT a) as a;
ERROR 42S22: Reference 'a' not supported (forward reference in item list)
EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> system NULL NULL NULL NULL 1
-3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
+3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1276 Field or reference 'a' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
@@ -181,12 +181,12 @@
4 8
3 8
explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1))
union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using filesort
-3 UNION t4 ALL NULL NULL NULL NULL 3 Using where
-4 SUBQUERY t2 ALL NULL NULL NULL NULL 2
-NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using filesort
+3 UNION t4 ALL NULL NULL NULL NULL 3 100.00 Using where
+4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
+NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where
(`test`.`t2`.`b` = (select `test`.`t3`.`a` AS `a` from `test`.`t3` order by 1 desc limit
1))) union (select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where
(`test`.`t4`.`b` = (select (max(`test`.`t2`.`a`) * 4) AS `max(t2.a)*4` from `test`.`t2`))
order by `a`)
select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;
@@ -199,10 +199,10 @@
7 2
explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a
from
(select * from t2 where a>1) as tt;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived3> system NULL NULL NULL NULL 1
-3 DERIVED t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived3> system NULL NULL NULL NULL 1 100.00
+3 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using filesort
Warnings:
Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a`
< 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc
limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from
`test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order
by 1 desc limit 1);
@@ -219,10 +219,10 @@
8 4.5000
9 7.5000
explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >=
t4.a)) from t2) from t4;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t4 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2
-3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
+3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select
min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >=
`test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from
`test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from
t2)` from `test`.`t4`
@@ -267,9 +267,9 @@
6
7
explain extended select * from t3 where a >= any (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
select * from t3 where a >= all (select b from t2);
@@ -307,11 +307,11 @@
NULL 1
2 2
explain extended select (select a from t1 where t1.a=t2.a union select a from t5 where
t5.a=t2.a), a from t2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
-2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1
-3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
+2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
+3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
@@ -328,9 +328,9 @@
1 2
2 2
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where
-2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
+2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS
`clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where
(`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
@@ -362,11 +362,11 @@
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8
WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t8 ref PRIMARY PRIMARY 37 const 1 Using where; Using index
-4 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 Using where; Using index
-2 SUBQUERY t8 ref PRIMARY PRIMARY 37 const 1 Using where
-3 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t8 ref PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index
+4 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 100.00 Using where; Using index
+2 SUBQUERY t8 ref PRIMARY PRIMARY 37 const 1 100.00 Using where
+3 SUBQUERY t8 ref PRIMARY PRIMARY 37 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email`
from `test`.`t8` where (`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo`
from `test`.`t8` where (`test`.`t8`.`pseudo` = _latin1'joce')))) AS `(SELECT email FROM
t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where
(`test`.`t8`.`pseudo` = (select `test`.`t8`.`pseudo` AS `pseudo` from `test`.`t8` where
(`test`.`t8`.`pseudo` = _latin1'joce')))
SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
@@ -391,14 +391,14 @@
INSERT INTO t1 (topic,date,pseudo) VALUES
('43506','2002-10-02','joce'),('40143','2002-08-03','joce');
EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where
(`test`.`t1`.`date` = 20020803)
EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index
Warnings:
Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where
(`test`.`t1`.`date` = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE
date='2002-08-03')`
SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';
@@ -415,11 +415,11 @@
SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;
ERROR 21000: Subquery returns more than 1 row
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL topic 3 NULL 2 Using index
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL topic 3 NULL 2 100.00 Using index
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1`
drop table t1;
@@ -538,14 +538,14 @@
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1
FROM t1 WHERE numeropost='1');
ERROR 21000: Subquery returns more than 1 row
EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1`
where (`test`.`t1`.`numeropost` = _latin1'1')
EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT
MAX(numreponse) FROM t1 WHERE numeropost='1');
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 Using index
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where
((`test`.`t1`.`numeropost` = _latin1'1'))
drop table t1;
@@ -719,8 +719,8 @@
id
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ref id id 5 const 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -731,18 +731,18 @@
id
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ref id id 5 const 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 +
1))
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL id 5 NULL 2 Using where; Using index
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
-3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`id`,<exists>(select 1 AS `1` having
(<cache>(`test`.`t2`.`id`) = <ref_null_helper>(1)) union select 3 AS `3`
having (<cache>(`test`.`t2`.`id`) = <ref_null_helper>(3))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
@@ -865,8 +865,8 @@
10.5 > ANY (SELECT * from t1)
1
explain extended select (select a+1) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
@@ -888,9 +888,9 @@
3 1
4 0
explain extended SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery a a 5 func 2 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS
`a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`)
in t2 on a checking NULL))) AS `t1.a in (select t2.a from t2)` from `test`.`t1`
CREATE TABLE t3 (a int(11) default '0');
@@ -902,10 +902,10 @@
3 1
4 0
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
-2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
+2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS
`a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from
`test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and
((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having
<is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where
t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
@@ -1015,21 +1015,21 @@
drop table t1;
CREATE TABLE t1 (a int(1));
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
+2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select sql_no_cache (select sql_no_cache rand() AS `RAND()` from `test`.`t1`)
AS `(SELECT RAND() FROM t1)` from `test`.`t1`
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
+2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select sql_no_cache (select sql_no_cache encrypt(_latin1'test') AS
`ENCRYPT('test')` from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from
`test`.`t1`
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
+2 UNCACHEABLE SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select sql_no_cache (select sql_no_cache benchmark(1,1) AS `BENCHMARK(1,1)`
from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
drop table t1;
@@ -1121,10 +1121,10 @@
insert into t1 values (1), (2), (3);
explain extended select a,(select (select rand() from t1 limit 1) from t1 limit 1)
from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3
-3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+2 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
+3 UNCACHEABLE SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select sql_no_cache `test`.`t1`.`a` AS `a`,(select sql_no_cache (select
sql_no_cache rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1
limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1) from t1
limit 1)` from `test`.`t1`
drop table t1;
@@ -1176,9 +1176,9 @@
0 IN (SELECT 1 FROM t1 a)
0
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
@@ -1186,9 +1186,9 @@
0 IN (SELECT 1 FROM t1 a)
0
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from
`test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
@@ -1231,9 +1231,9 @@
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ref salary salary 5 const 1 Using where
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using where
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` =
(select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))
drop table t1;
@@ -1293,9 +1293,9 @@
3
4
explain extended select * from t2 where t2.a in (select a from t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`)
in t1 on PRIMARY)))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -1303,9 +1303,9 @@
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <>
30);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index;
Using where
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`)
in t1 on PRIMARY where (`test`.`t1`.`b` <> 30))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -1313,10 +1313,10 @@
2
3
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 PRIMARY 4 NULL 4 Using where; Using index
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 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`.`t3`.`a` = `test`.`t1`.`b`) and
(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
drop table t1, t2, t3;
@@ -1332,9 +1332,9 @@
3
4
explain extended select * from t2 where t2.a in (select a from t1);
-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 t1 index_subquery a a 5 func 1001 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`)
in t1 on a)))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -1342,9 +1342,9 @@
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <>
30);
-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 t1 index_subquery a a 5 func 1001 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`)
in t1 on a where (`test`.`t1`.`b` <> 30))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -1352,10 +1352,10 @@
2
3
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 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
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 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`.`t3`.`a` = `test`.`t1`.`b`) and
(<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
insert into t1 values (3,31);
@@ -1369,9 +1369,9 @@
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <>
30);
-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 t1 index_subquery a a 5 func 1001 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`)
in t1 on a where (`test`.`t1`.`b` <> 30))))
drop table t1, t2, t3;
@@ -1427,8 +1427,8 @@
s1
tttt
explain extended (select * from t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 1
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 (select `test`.`t1`.`s1` AS `s1` from `test`.`t1`)
(select * from t1);
@@ -1460,27 +1460,27 @@
a2 1
a3 1
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`s1` AS
`s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`s1` AS
`s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`s1` AS
`s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL s1 6 NULL 3 Using index
-2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
+2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 6 func 2 100.00 Using index; Using where
Warnings:
Note 1003 select `test`.`t1`.`s1` AS
`s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN
(SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
@@ -1493,17 +1493,17 @@
7
3
explain extended select * from t3 where a >= all (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < (select max(`test`.`t2`.`b`) from `test`.`t2`)))
select * from t3 where a >= some (select b from t2);
a
explain extended select * from t3 where a >= some (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))
select * from t3 where a >= all (select b from t2 group by 1);
@@ -1512,49 +1512,49 @@
7
3
explain extended select * from t3 where a >= all (select b from t2 group by 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` < <max>(select `test`.`t2`.`b` AS `b` from
`test`.`t2` group by 1)))
select * from t3 where a >= some (select b from t2 group by 1);
a
explain extended select * from t3 where a >= some (select b from t2 group by 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<nop>((`test`.`t3`.`a` >= <min>(select `test`.`t2`.`b` AS `b` from
`test`.`t2` group by 1)))
select * from t3 where NULL >= any (select b from t2);
a
explain extended select * from t3 where NULL >= any (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`
select * from t3 where NULL >= any (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= any (select b from t2 group by 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`
select * from t3 where NULL >= some (select b from t2);
a
explain extended select * from t3 where NULL >= some (select b from t2);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`
select * from t3 where NULL >= some (select b from t2 group by 1);
a
explain extended select * from t3 where NULL >= some (select b from t2 group by 1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`
insert into t2 values (2,2), (2,1), (3,3), (3,1);
@@ -1563,9 +1563,9 @@
6
7
explain extended select * from t3 where a > all (select max(b) from t2 group by a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where
<not>((`test`.`t3`.`a` <= <max>(select max(`test`.`t2`.`b`) AS `max(b)`
from `test`.`t2` group by `test`.`t2`.`a`)))
drop table t2, t3;
@@ -1612,11 +1612,11 @@
s1
e
explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1
from t1);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY t1 system NULL NULL NULL NULL 1
-3 UNION t1 system NULL NULL NULL NULL 1
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
+2 SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
+3 UNION t1 system NULL NULL NULL NULL 1 100.00
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1`
drop table t1;
@@ -1733,15 +1733,15 @@
11 text11
12 text12
explain extended select * from t1 where id not in (select id from t1 where id < 8);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where
-2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 12 100.00 Using where
+2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index;
Using where
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1`
where
not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`)
in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))
explain extended select * from t1 as tt where not exists (select id from t1 where id <
8 and (id = tt.id or id is null) having id is not null);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using
index
Warnings:
Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1`
`tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where
((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having
(`test`.`t1`.`id` is not null)))
@@ -1765,10 +1765,10 @@
1000 text1000 NULL NULL 1000 text1000
1001 text1001 NULL NULL 1000 text1000
explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1
c on (if(isnull(b.id), 1000, b.id)=c.id);
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE a ALL NULL NULL NULL NULL 14
-1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2
-1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE a ALL NULL NULL NULL NULL 14 100.00
+1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.id 2 100.00
+1 SIMPLE c eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
Warnings:
Note 1003 select `test`.`a`.`id` AS `id`,`test`.`a`.`text` AS `text`,`test`.`b`.`id` AS
`id`,`test`.`b`.`text` AS `text`,`test`.`c`.`id` AS `id`,`test`.`c`.`text` AS `text` from
`test`.`t1` `a` left join `test`.`t2` `b` on(((`test`.`b`.`id` = `test`.`a`.`id`) or
isnull(`test`.`b`.`id`))) join `test`.`t1` `c` where
(if(isnull(`test`.`b`.`id`),1000,`test`.`b`.`id`) = `test`.`c`.`id`)
drop table t1,t2;
@@ -2275,9 +2275,9 @@
1 2
3 4
explain extended select * from t1 up where exists (select * from t1 where t1.a=up.a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY up ALL NULL NULL NULL NULL 2 Using where
-2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up`
where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` =
`test`.`up`.`a`))
@@ -2813,21 +2813,21 @@
10 5 NULL
10 10 NULL
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0')
as 'test' from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS
`two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select
`test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where
((`test`.`t2`.`flag` = _latin1'0') and ((<cache>(`test`.`t1`.`one`) =
`test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)) and ((<cache>(`test`.`t1`.`two`) =
`test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) having
(<is_not_null_test>(`test`.`t2`.`one`) and
<is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2
WHERE flag = 'N');
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1`
where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select
`test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where
((`test`.`t2`.`flag` = _latin1'N') and (<cache>(`test`.`t1`.`one`) =
`test`.`t2`.`one`) and (<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`))))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0'
group by one,two) as 'test' from t1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using
filesort
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary;
Using filesort
Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS
`two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select
`test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where
(`test`.`t2`.`flag` = _latin1'0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having
(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))
and ((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))
and <is_not_null_test>(`test`.`t2`.`one`) and
<is_not_null_test>(`test`.`t2`.`two`)))) AS `test` from `test`.`t1`
DROP TABLE t1,t2;
--- 1.166/mysql-test/r/view.result 2006-07-28 21:27:09 +04:00
+++ 1.167/mysql-test/r/view.result 2006-07-28 21:27:09 +04:00
@@ -48,8 +48,8 @@
select v1.b from v1;
ERROR 42S22: Unknown column 'v1.b' in 'field list'
explain extended select c from v1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1`
create algorithm=temptable view v2 (c) as select b+1 from t1;
@@ -64,9 +64,9 @@
6
11
explain extended select c from v2;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
-2 DERIVED t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
+2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `v2`.`c` AS `c` from `test`.`v2`
create view v3 (c) as select a+1 from v1;
@@ -82,8 +82,8 @@
7
12
explain extended select c from v3;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1`
create algorithm=temptable view v4 (c) as select c+1 from v2;
@@ -95,10 +95,10 @@
7
12
explain extended select c from v4;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
-2 DERIVED <derived3> ALL NULL NULL NULL NULL 5
-3 DERIVED t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 5 100.00
+3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `v4`.`c` AS `c` from `test`.`v4`
create view v5 (c) as select c+1 from v2;
@@ -110,9 +110,9 @@
7
12
explain extended select c from v5;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5
-3 DERIVED t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00
+3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select (`v2`.`c` + 1) AS `c` from `test`.`v2`
create algorithm=temptable view v6 (c) as select c+1 from v1;
@@ -124,9 +124,9 @@
7
12
explain extended select c from v6;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5
-2 DERIVED t1 ALL NULL NULL NULL NULL 5
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 100.00
+2 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `v6`.`c` AS `c` from `test`.`v6`
show tables;
@@ -375,8 +375,8 @@
20
30
explain extended select * from v1;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3)
update v1 set c=c+1;
@@ -1390,10 +1390,10 @@
2 2 NULL
4 NULL NULL
explain extended select * from t3 left join v3 on (t3.a = v3.a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b`
from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` =
`test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1
create view v1 (a) as select a from t1;
@@ -1405,10 +1405,10 @@
2 2 NULL
4 NULL NULL
explain extended select * from t3 left join v4 on (t3.a = v4.a);
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 3
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b`
from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` =
`test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1
prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);";
--- 1.6/mysql-test/r/archive_gis.result 2006-07-28 21:27:09 +04:00
+++ 1.7/mysql-test/r/archive_gis.result 2006-07-28 21:27:09 +04:00
@@ -233,8 +233,8 @@
120 POLYGON((0 0,10 0,10 10,0 10,0 0))
121 POLYGON((3 6,44 6,44 9,3 9,3 6))
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g))
from gis_geometry;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00
Warnings:
Note 1003 select dimension(`test`.`gis_geometry`.`g`) AS
`Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS
`GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS
`IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from
`test`.`gis_geometry`
SELECT fid, X(g) FROM gis_point ORDER by fid;
@@ -250,8 +250,8 @@
103 20
104 20
explain extended select X(g),Y(g) FROM gis_point;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_point ALL NULL NULL NULL NULL 4
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00
Warnings:
Note 1003 select x(`test`.`gis_point`.`g`) AS `X(g)`,y(`test`.`gis_point`.`g`) AS `Y(g)`
from `test`.`gis_point`
SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid;
@@ -285,8 +285,8 @@
106 1
107 0
explain extended select
AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g,
2)),IsClosed(g) FROM gis_line;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_line ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select astext(startpoint(`test`.`gis_line`.`g`)) AS
`AsText(StartPoint(g))`,astext(endpoint(`test`.`gis_line`.`g`)) AS
`AsText(EndPoint(g))`,glength(`test`.`gis_line`.`g`) AS
`GLength(g)`,numpoints(`test`.`gis_line`.`g`) AS
`NumPoints(g)`,astext(pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g,
2))`,isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line`
SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid;
@@ -315,8 +315,8 @@
109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
110 NULL
explain extended select
AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g,
1)) FROM gis_polygon;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select astext(centroid(`test`.`gis_polygon`.`g`)) AS
`AsText(Centroid(g))`,area(`test`.`gis_polygon`.`g`) AS
`Area(g)`,astext(exteriorring(`test`.`gis_polygon`.`g`)) AS
`AsText(ExteriorRing(g))`,numinteriorrings(`test`.`gis_polygon`.`g`) AS
`NumInteriorRings(g)`,astext(interiorringn(`test`.`gis_polygon`.`g`,1)) AS
`AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon`
SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid;
@@ -354,8 +354,8 @@
120 2
121 2
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`gis_multi_point`.`fid` AS
`fid`,numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from
`test`.`gis_multi_point`
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid;
@@ -382,8 +382,8 @@
120 POINT(0 0)
121 POINT(44 6)
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`gis_multi_point`.`fid` AS
`fid`,astext(geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from
`test`.`gis_multi_point`
SELECT g1.fid as first, g2.fid as second,
@@ -401,9 +401,9 @@
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE g1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
-1 SIMPLE g2 ALL NULL NULL NULL NULL 2
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS
`second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS
`w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS
`c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS
`o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS
`e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS
`d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS
`t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS
`i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection`
`g1` join `test`.`gis_geometrycollection` `g2` order by
`test`.`g1`.`fid`,`test`.`g2`.`fid`
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line,
gis_multi_polygon, gis_geometrycollection, gis_geometry;
--- 1.7/mysql-test/r/bdb_gis.result 2006-07-28 21:27:09 +04:00
+++ 1.8/mysql-test/r/bdb_gis.result 2006-07-28 21:27:09 +04:00
@@ -233,8 +233,8 @@
120 POLYGON((0 0,10 0,10 10,0 10,0 0))
121 POLYGON((3 6,44 6,44 9,3 9,3 6))
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g))
from gis_geometry;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00
Warnings:
Note 1003 select dimension(`test`.`gis_geometry`.`g`) AS
`Dimension(g)`,geometrytype(`test`.`gis_geometry`.`g`) AS
`GeometryType(g)`,isempty(`test`.`gis_geometry`.`g`) AS
`IsEmpty(g)`,astext(envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from
`test`.`gis_geometry`
SELECT fid, X(g) FROM gis_point ORDER by fid;
@@ -250,8 +250,8 @@
103 20
104 20
explain extended select X(g),Y(g) FROM gis_point;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE gis_point ALL NULL NULL N 