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.

1 files changed, 2 insertions, 2 deletions

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;