From 4dc963a9f3364395f0ade47f1cf212942fd23448 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 16 May 2012 20:39:03 -0700 Subject: Fixed LP bug #999251: Q13 from DBT3 uses table scan instead of covering index scan. The optimizer chose a less efficient execution plan due to the following defects of the code: 1. the generic handler function handler::keyread_time did not take into account that in clustered primary keys record data is included into each index entry 2. the function make_join_readinfo erroneously decided that index only scan could not be used if join cache was empoyed. Added no additional test case. Adjusted some of the test results. --- mysql-test/r/join_cache.result | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'mysql-test/r/join_cache.result') diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 0892ce7794a..f3d282cd6eb 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -3221,7 +3221,7 @@ explain select t1.a, count(t2.p) as count from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort 1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan select t1.a, count(t2.p) as count from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; @@ -4327,15 +4327,15 @@ SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL idx1 NULL NULL NULL 20 Using temporary; Using filesort +1 SIMPLE t2 index idx1 idx2 7 NULL 20 Using index; Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY,idx2 PRIMARY 4 test.t2.i 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t1 ref idx2 idx2 3 test.t3.v 5 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; v -b h +b n v p @@ -4345,7 +4345,7 @@ SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 GROUP BY t2.v ORDER BY t1.pk,t2.v; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL idx1 NULL NULL NULL 20 Using temporary; Using filesort +1 SIMPLE t2 index idx1 idx2 7 NULL 20 Using index; Using temporary; Using filesort 1 SIMPLE t3 hash_ALL PRIMARY,idx2 #hash#PRIMARY 4 test.t2.i 20 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t1 hash_ALL idx2 #hash#idx2 3 test.t3.v 45 Using where; Using join buffer (incremental, BNLH join) SELECT t2.v FROM t1, t2, t3 -- cgit v1.2.1