From 2b1f0b875775b65496e959db7f54f6eb4220400e Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 24 Dec 2011 08:55:10 -0800 Subject: Back-ported the patch of the mysql-5.6 code line that fixed several defects in the greedy optimization: 1) The greedy optimizer calculated the 'compare-cost' (CPU-cost) for iterating over the partial plan result at each level in the query plan as 'record_count / (double) TIME_FOR_COMPARE' This cost was only used locally for 'best' calculation at each level, and *not* accumulated into the total cost for the query plan. This fix added the 'CPU-cost' of processing 'current_record_count' records at each level to 'current_read_time' *before* it is used as 'accumulated cost' argument to recursive best_extension_by_limited_search() calls. This ensured that the cost of a huge join-fanout early in the QEP was correctly reflected in the cost of the final QEP. To get identical cost for a 'best' optimized query and a straight_join with the same join order, the same change was also applied to optimize_straight_join() and get_partial_join_cost() 2) Furthermore to get equal cost for 'best' optimized query and a straight_join the new code substrcated the same '0.001' in optimize_straight_join() as it had been already done in best_extension_by_limited_search() 3) When best_extension_by_limited_search() aggregated the 'best' plan a plan was 'best' by the check : 'if ((search_depth == 1) || (current_read_time < join->best_read))' The term '(search_depth == 1' incorrectly caused a new best plan to be collected whenever the specified 'search_depth' was reached - even if this partial query plan was more expensive than what we had already found. --- mysql-test/r/subselect3.result | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'mysql-test/r/subselect3.result') diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 29518fb6df9..ae629ccb928 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1138,8 +1138,8 @@ create table t3 (a int); insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where -1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) create table t4 (pk int primary key); insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 @@ -1336,9 +1336,9 @@ insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start temporary; Using join buffer (flat, BNL join) -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where +1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where +1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t1) drop table t0,t1,t2; set @@optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1