diff options
| -rw-r--r-- | mysql-test/r/distinct.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/join_outer.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/select_safe.result | 2 | ||||
| -rw-r--r-- | sql/sql_select.cc | 46 |
4 files changed, 27 insertions, 29 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 1dbccb65748..da0c6a5eb72 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -173,9 +173,9 @@ INSERT INTO t2 values (1),(2),(3); 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; table type possible_keys key key_len ref rows Extra -t3 index a a 5 NULL 6 Using index; Using temporary -t2 index a a 4 NULL 5 Using index; Distinct -t1 eq_ref PRIMARY PRIMARY 4 t2.a 1 Using where; Distinct +t2 index a a 4 NULL 5 Using index; Using temporary +t1 eq_ref PRIMARY PRIMARY 4 t2.a 1 +t3 index a a 5 NULL 5 Using where; Using index SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a 1 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 8f3f82201c3..debec01fbdc 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -615,7 +615,7 @@ INSERT INTO t2 VALUES (1,1); explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 2 -t2 index id id 8 NULL 1 Using where; Using index; Not exists +t2 ref id id 4 t1.id 1 Using where; Using index; Not exists SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL; id name id idx 2 no NULL NULL diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index c4e5984d360..1ee1368d029 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -70,7 +70,7 @@ insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(nu explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; table type possible_keys key key_len ref rows Extra t1 ALL b NULL NULL NULL 21 -t2 ALL b NULL NULL NULL 16 Using where +t2 ref b b 21 t1.b 6 Using where set MAX_SEEKS_FOR_KEY=1; explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b; table type possible_keys key key_len ref rows Extra diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6f6d21f1f8b..3738382928f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2133,33 +2133,31 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, s->table->used_keys && best_key) && !(s->table->force_index && best_key)) { // Check full join - ha_rows rnd_records= s->found_records; - if (s->on_expr) - { - tmp=rows2double(rnd_records); // Can't use read cache - } - else - { - tmp=(double) s->read_time; - /* Calculate time to read previous rows through cache */ - tmp*=(1.0+floor((double) cache_record_length(join,idx)* - record_count / - (double) thd->variables.join_buff_size)); - } - - /* - 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 (found_constrain) - rnd_records-= rnd_records/4; - + /* + Estimate cost of reading table. Note, that we don't read a table + on each iteration as in most cases join buffer is in use. + */ + tmp= (double) s->read_time; + /* + In case of full scan we check every row in the table: + here we take into account rows read and skipped, as well as rows + passed to next select + */ + if (best == DBL_MAX || - (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records < + (tmp + record_count/(double) TIME_FOR_COMPARE*s->records < best + record_count/(double) TIME_FOR_COMPARE*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 + */ + ha_rows rnd_records= s->found_records; + if (found_constrain) + rnd_records-= rnd_records/4; + /* If the table has a range (s->quick is set) make_join_select() will ensure that this will be used |