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-30 00:02:42+04:00, sergefp@stripped +3 -0
Merge spetrunia@stripped:/home/bk/mysql-5.1-opt
into mysql.com:/home/psergey/mysql-5.1-bug14940-r4
MERGE: 1.2229.1.2
mysql-test/r/partition_pruning.result@stripped, 2006-07-30 00:02:37+04:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.17.1.1
mysql-test/t/partition_pruning.test@stripped, 2006-07-30 00:02:37+04:00, sergefp@stripped +0 -0
Auto merged
MERGE: 1.14.1.1
sql/sql_select.cc@stripped, 2006-07-30 00:02:37+04:00, sergefp@stripped +0 -3
Auto merged
MERGE: 1.417.1.1
# 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/RESYNC
--- 1.418/sql/sql_select.cc 2006-07-30 00:02:47 +04:00
+++ 1.419/sql/sql_select.cc 2006-07-30 00:02:47 +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))
@@ -14125,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 */
@@ -14280,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.19/mysql-test/r/partition_pruning.result 2006-07-30 00:02:48 +04:00
+++ 1.20/mysql-test/r/partition_pruning.result 2006-07-30 00:02:48 +04:00
@@ -460,27 +460,67 @@
explain partitions select * from t2 where b = 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
+explain extended select * from t2 where b = 6;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ref b b 5 const 76 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6)
explain partitions select * from t2 where b = 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 Using where
+explain extended select * from t2 where b in (1,3,5);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5))
explain partitions select * from t2 where b in (1,3,5);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b in (2,4,6);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6))
explain partitions select * from t2 where b in (2,4,6);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b in (7,8,9);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9))
explain partitions select * from t2 where b in (7,8,9);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b > 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 44.84 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5)
explain partitions select * from t2 where b > 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b > 5 and b < 8;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8))
explain partitions select * from t2 where b > 5 and b < 8;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
+explain extended select * from t2 where b > 5 and b < 7;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7))
explain partitions select * from t2 where b > 5 and b < 7;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where
+explain extended select * from t2 where b > 0 and b < 5;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL b NULL NULL NULL 910 41.65 Using where
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5))
explain partitions select * from t2 where b > 0 and b < 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where
--- 1.16/mysql-test/t/partition_pruning.test 2006-07-30 00:02:48 +04:00
+++ 1.17/mysql-test/t/partition_pruning.test 2006-07-30 00:02:48 +04:00
@@ -407,13 +407,29 @@
explain partitions select * from t2 where (a = 100 OR a = 900);
explain partitions select * from t2 where (a > 100 AND a < 600);
explain partitions select * from t2 where b = 4;
+
+explain extended select * from t2 where b = 6;
explain partitions select * from t2 where b = 6;
+
+explain extended select * from t2 where b in (1,3,5);
explain partitions select * from t2 where b in (1,3,5);
+
+explain extended select * from t2 where b in (2,4,6);
explain partitions select * from t2 where b in (2,4,6);
+
+explain extended select * from t2 where b in (7,8,9);
explain partitions select * from t2 where b in (7,8,9);
+
+explain extended select * from t2 where b > 5;
explain partitions select * from t2 where b > 5;
+
+explain extended select * from t2 where b > 5 and b < 8;
explain partitions select * from t2 where b > 5 and b < 8;
+
+explain extended select * from t2 where b > 5 and b < 7;
explain partitions select * from t2 where b > 5 and b < 7;
+
+explain extended select * from t2 where b > 0 and b < 5;
explain partitions select * from t2 where b > 0 and b < 5;
flush status;
| Thread |
|---|
| • bk commit into 5.1 tree (sergefp:1.2232) | Sergey Petrunia | 29 Jul |
