bk commit into 5.1 tree (sergefp:1.2232)

[Sergey Petrunia]

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;