bk commit into 4.1 tree (sergefp:1.2136)

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
4.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
  1.2136 05/03/26 18:15:11 sergefp@stripped +13 -0
  Optimizer fix:
  * Make range optimizer produce estimate on number of records that will statisfy 
    table condition, even in the case where quick select is not constructed
  * Make join optimizer use this value. 
  * Change EXPLAIN code to display the number of rows scanned, not the above value
    (as this is what docs say about rows column in EXPLAIN output) [This cset includes the
previous one]

  sql/table.h
    1.70 05/03/26 18:15:08 sergefp@stripped +9 -0
    Optimizer fix:
    * Make range optimizer produce estimate on number of records that will statisfy 
      table condition, even in the case where quick select is not constructed
    * Make join optimizer use this value. 
    * Change EXPLAIN code to display the number of rows scanned, not the above value
      (as this is what docs say about rows column in EXPLAIN output

  sql/sql_select.cc
    1.389 05/03/26 18:15:08 sergefp@stripped +13 -3
    Optimizer fix:
    * Make range optimizer produce estimate on number of records that will statisfy 
      table condition, even in the case where quick select is not constructed
    * Make join optimizer use this value. 
    * Change EXPLAIN code to display the number of rows scanned, not the above value
      (as this is what docs say about rows column in EXPLAIN output)

  sql/opt_range.cc
    1.133 05/03/26 18:15:08 sergefp@stripped +4 -0
    Make range optimizer produce estimate on number of records that will statisfy 
    table condition, even in the case where quick select is not constructed

  mysql-test/t/heap.test
    1.21 05/03/26 18:15:08 sergefp@stripped +2 -0
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/union.result
    1.87 05/03/26 18:15:08 sergefp@stripped +1 -1
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/select.result
    1.51 05/03/26 18:15:08 sergefp@stripped +2 -2
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/null_key.result
    1.29 05/03/26 18:15:08 sergefp@stripped +1 -1
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/myisam.result
    1.48 05/03/26 18:15:08 sergefp@stripped +5 -5
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/key_diff.result
    1.11 05/03/26 18:15:08 sergefp@stripped +1 -1
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/heap_hash.result
    1.13 05/03/26 18:15:08 sergefp@stripped +1 -1
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/heap.result
    1.29 05/03/26 18:15:08 sergefp@stripped +7 -1
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/group_by.result
    1.43 05/03/26 18:15:08 sergefp@stripped +2 -2
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

  mysql-test/r/distinct.result
    1.32 05/03/26 18:15:08 sergefp@stripped +2 -2
    Optimizer fix: Make join optimizer use selectivity estimates produced by range
analysis:
     updated the tests results

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-4.1-look30

--- 1.132/sql/opt_range.cc	2004-11-08 02:13:49 +03:00
+++ 1.133/sql/opt_range.cc	2005-03-26 18:15:08 +03:00
@@ -595,6 +595,8 @@
    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_condition_rows - Estimate on number of rows that will
+      satisfy the entire condition.
 
  RETURN VALUES
   -1 if impossible select
@@ -725,6 +727,8 @@
 	        needed_reg.set_bit(keynr);
 
 	    found_records=check_quick_select(&param, idx, *key);
+            if (found_records != HA_POS_ERROR)
+              head->quick_condition_rows= min(head->quick_condition_rows,
found_records);
 	    if (found_records != HA_POS_ERROR && found_records > 2 &&
 		head->used_keys.is_set(keynr) &&
 		(head->file->index_flags(keynr, param.max_key_part, 1) &

--- 1.388/sql/sql_select.cc	2005-03-18 07:09:30 +03:00
+++ 1.389/sql/sql_select.cc	2005-03-26 18:15:08 +03:00
@@ -1696,6 +1696,7 @@
     s->needed_reg.init();
     table_vector[i]=s->table=table=tables->table;
     table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);// record count
+    table->quick_condition_rows= table->file->records;
     table->quick_keys.clear_all();
     table->reginfo.join_tab=s;
     table->reginfo.not_exists_optimize=0;
@@ -2953,6 +2954,7 @@
         {
           /* Estimate cost of reading table. */
           tmp= s->table->file->scan_time();
+          rnd_records= s->table->quick_condition_rows;
           if (s->on_expr)                         // Can't use join cache
           {
             /*
@@ -5804,6 +5806,7 @@
     join->thd->row_count= 0;
     do
     {
+      //printf("%s read_record FIRST\n", join_tab->table->path);
       if (join->thd->killed)			// Aborted by user
       {
 	my_error(ER_SERVER_SHUTDOWN,MYF(0));	/* purecov: inspected */
@@ -9496,9 +9499,16 @@
 	item_list.push_back(item_null);
       }
       /* rows */
-      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));
       /* extra */
       my_bool key_read=table->key_read;
       if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&

--- 1.69/sql/table.h	2004-12-11 15:54:07 +03:00
+++ 1.70/sql/table.h	2005-03-26 18:15:08 +03:00
@@ -183,6 +183,15 @@
   ha_rows	quick_rows[MAX_KEY];
   uint		quick_key_parts[MAX_KEY];
   key_part_map  const_key_parts[MAX_KEY];
+  /* 
+    Estimate of number of records that satisfy SARGable part of the table
+    ondition, 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 on this table 
+    fields and constants).
+  */
+  ha_rows       quick_condition_rows;
   ulong		query_id;
   uchar		frm_version;
 

--- 1.86/mysql-test/r/union.result	2005-02-14 01:35:36 +03:00
+++ 1.87/mysql-test/r/union.result	2005-03-26 18:15:08 +03:00
@@ -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	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	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.12/mysql-test/r/heap_hash.result	2005-02-08 23:44:49 +03:00
+++ 1.13/mysql-test/r/heap_hash.result	2005-03-26 18:15:08 +03: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.31/mysql-test/r/distinct.result	2005-02-12 02:00:26 +03:00
+++ 1.32/mysql-test/r/distinct.result	2005-03-26 18:15:08 +03:00
@@ -173,9 +173,9 @@
 INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
 explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	Using temporary
+1	SIMPLE	t2	index	a	a	4	NULL	5	Using index; Using temporary
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	
 1	SIMPLE	t3	ref	a	a	5	test.t1.b	2	Using where; Using index
-1	SIMPLE	t2	index	a	a	4	NULL	4	Using where; Using index; Distinct
 SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 a
 1

--- 1.42/mysql-test/r/group_by.result	2005-02-08 15:41:05 +03:00
+++ 1.43/mysql-test/r/group_by.result	2005-03-26 18:15:08 +03:00
@@ -537,11 +537,11 @@
 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	4	Using where
 explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary
-1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	4	Using where
 drop table t1,t2;
 create table t1 (a int, b int);
 insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1,
4);

--- 1.28/mysql-test/r/heap.result	2005-03-15 12:32:08 +03:00
+++ 1.29/mysql-test/r/heap.result	2005-03-26 18:15:08 +03:00
@@ -164,9 +164,15 @@
 btn
 alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn;
 update t1 set new_col=left(btn,1);
+select count(*) from t1 where btn="a";
+count(*)
+1
+select count(*) from t1;
+count(*)
+14
 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.10/mysql-test/r/key_diff.result	2003-08-11 23:28:01 +04:00
+++ 1.11/mysql-test/r/key_diff.result	2005-03-26 18:15:08 +03:00
@@ -36,7 +36,7 @@
 explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	5	Using where
 select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B order by binary t1.a,t2.a;
 a	b	a	b
 A	B	a	a

--- 1.28/mysql-test/r/null_key.result	2004-09-07 23:30:23 +04:00
+++ 1.29/mysql-test/r/null_key.result	2005-03-26 18:15:08 +03:00
@@ -153,7 +153,7 @@
 7	NULL
 explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a,b	a	10	NULL	3	Using where; Using index
+1	SIMPLE	t1	range	a,b	a	10	NULL	4	Using where; Using index
 select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
 a	b
 NULL	7

--- 1.50/mysql-test/r/select.result	2005-03-09 17:50:57 +03:00
+++ 1.51/mysql-test/r/select.result	2005-03-26 18:15:08 +03:00
@@ -2360,7 +2360,7 @@
 explain select * from t1 left join t2 on a=c where d in (4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
 select * from t1 left join t2 on a=c where d in (4);
 a	b	c	d
 3	2	3	4
@@ -2368,7 +2368,7 @@
 explain select * from t1 left join t2 on a=c where d = 4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	c,d	d	5	const	2	Using where
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
 select * from t1 left join t2 on a=c where d = 4;
 a	b	c	d
 3	2	3	4

--- 1.20/mysql-test/t/heap.test	2005-03-15 12:32:08 +03:00
+++ 1.21/mysql-test/t/heap.test	2005-03-26 18:15:08 +03:00
@@ -106,6 +106,8 @@
 select * from t1 where btn like "q%";
 alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn;
 update t1 set new_col=left(btn,1);
+select count(*) from t1 where btn="a";
+select count(*) from t1;
 explain select * from t1 where btn="a";
 explain select * from t1 where btn="a" and new_col="a";
 drop table t1;

--- 1.47/mysql-test/r/myisam.result	2005-03-02 12:34:56 +03:00
+++ 1.48/mysql-test/r/myisam.result	2005-03-26 18:15:08 +03:00
@@ -345,12 +345,12 @@
 t1	1	c_2	2	a	A	5	NULL	NULL		BTREE	
 explain select * from t1,t2 where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	Using where
 explain select * from t1,t2 force index(a) where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where
 explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	
@@ -361,8 +361,8 @@
 1	SIMPLE	t1	ref	b	b	5	test.t2.b	1	Using where
 explain select * from t1,t2 force index(c) where t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain select * from t1 where a=0 or a=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	5	Using where