From 013ba37ae23040ca804c1ac2c293cab4ccb5731e Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 30 Jun 2022 14:02:53 +0300 Subject: Fix cost calculation in test_if_cheaper_ordering() to be cost based The original code was mostly rule based and preferred clustered or covering indexed independent of cost. There where a few test changes: - Some test changed from using filesort to index or table scan. This happened when most of the rows had to be sorted and the ORDER BY could use covering or a clustered index (innodb_mysql, create_spatial_index). - Some test changed range to filesort. This where mainly because the range was scanning most of the rows or using index scan + row lookup and filesort with table scan is cheaper. (order_by). - Change in join_cache was because sorting 2 rows is faster than retrieving 10 rows. - In selectivity_innodb.test one test changed to use a cheaper index. --- mysql-test/main/join_cache.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/main/join_cache.result') diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index fe40178a8da..2c5323a7795 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -5153,7 +5153,7 @@ SET SESSION optimizer_switch = 'index_condition_pushdown=off'; EXPLAIN SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range f1,f2 f2 13 NULL 10 Using where +1 SIMPLE t1 range f1,f2 f1 5 NULL 3 Using where; Rowid-ordered scan; Using filesort 1 SIMPLE t2 ref f3 f3 67 test.t1.f2 2 Using where; Using index SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; @@ -5163,7 +5163,7 @@ SET SESSION optimizer_switch = 'index_condition_pushdown=on'; EXPLAIN SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range f1,f2 f2 13 NULL 10 Using where +1 SIMPLE t1 range f1,f2 f1 5 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort 1 SIMPLE t2 ref f3 f3 67 test.t1.f2 2 Using where; Using index SELECT * FROM t1,t2 WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; -- cgit v1.2.1