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(¶m, 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
| Thread |
|---|
| • bk commit into 4.1 tree (sergefp:1.2136) | Sergey Petrunia | 26 Mar |
