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/t/subselect4.test | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 0e51373fb01..b2e15c457d7 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -993,13 +993,16 @@ INSERT INTO t4 VALUES ('k'), ('d'); SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; +SET optimizer_switch='semijoin_with_cache=off'; +SET optimizer_switch='materialization=off'; EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +SET optimizer_switch='materialization=on'; EXPLAIN SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); -- cgit v1.2.1