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, 57 insertions, 2 deletions

diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 6db2b942e44..b59089bee80 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1552,16 +1552,71 @@ UNIQUE KEY a_c (a,c),
 KEY (a));
 INSERT INTO t1 VALUES (1, 10), (2, NULL);
 # Must use ref-or-null on the a_c index
+ANALYZE FORMAT=JSON
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "read_sorted_file": {
+          "r_rows": 1,
+          "filesort": {
+            "sort_key": "t1.c",
+            "r_loops": 1,
+            "r_total_time_ms": "REPLACED",
+            "r_used_priority_queue": false,
+            "r_output_rows": 1,
+            "r_buffer_size": "REPLACED",
+            "r_sort_mode": "sort_key,addon_fields",
+            "table": {
+              "table_name": "t1",
+              "access_type": "index",
+              "possible_keys": ["a_c", "a"],
+              "key": "a_c",
+              "key_length": "10",
+              "used_key_parts": ["a", "c"],
+              "r_loops": 1,
+              "rows": 2,
+              "r_rows": 2,
+              "r_table_time_ms": "REPLACED",
+              "r_other_time_ms": "REPLACED",
+              "filtered": 50,
+              "r_filtered": 50,
+              "attached_condition": "t1.a = 2 and (t1.c = 10 or t1.c is null)",
+              "using_index": true
+            }
+          }
+        }
+      }
+    ]
+  }
+}
 EXPLAIN
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a_c,a	a_c	10	NULL	2	Using where; Using index
+1	SIMPLE	t1	index	a_c,a	a_c	10	NULL	2	Using where; Using index; Using filesort
 # Must return 1 row
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 col
 1
+# With more rows "filesort" is removed
+INSERT INTO t1 select seq,seq from seq_1_to_2;
+EXPLAIN
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a_c,a	a_c	10	NULL	2	Using where; Using index
+SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
+col
+1
 # With more rows "range" changes to "ref_or_null"
-INSERT INTO t1 select seq,seq from seq_1_to_10;
+INSERT INTO t1 select seq,seq from seq_3_to_10;
 EXPLAIN
 SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra