diff options
| -rw-r--r-- | mysql-test/r/key.result | 27 | ||||
| -rw-r--r-- | mysql-test/t/key.test | 20 | ||||
| -rw-r--r-- | sql/handler.h | 12 | ||||
| -rw-r--r-- | sql/sql_select.cc | 12 |
4 files changed, 69 insertions, 2 deletions
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 2479308a2c4..a89a79dadbb 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -612,3 +612,30 @@ SELECT 1 as RES FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; RES DROP TABLE t1; +# +# Bug#18144: Cost with FORCE/USE index seems incorrect in some cases. +# +# We are interested in showing that the cost for the last plan is higher +# than for the preceding two plans. +# +CREATE TABLE t1( a INT, b INT, KEY( a ) ); +INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5); +EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SHOW STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 9.212184 +EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SHOW STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 9.212184 +EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 6 +SHOW STATUS LIKE 'Last_query_cost'; +Variable_name Value +Last_query_cost 14.199000 +DROP TABLE t1; diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index 9a4158d8e13..355c5ef53a4 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -561,3 +561,23 @@ SELECT 1 as RES FROM t1 AS t1_outer WHERE (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12; DROP TABLE t1; + +--echo # +--echo # Bug#18144: Cost with FORCE/USE index seems incorrect in some cases. +--echo # +--echo # We are interested in showing that the cost for the last plan is higher +--echo # than for the preceding two plans. +--echo # +CREATE TABLE t1( a INT, b INT, KEY( a ) ); +INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5); + +EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a; +SHOW STATUS LIKE 'Last_query_cost'; + +EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a; +SHOW STATUS LIKE 'Last_query_cost'; + +EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a; +SHOW STATUS LIKE 'Last_query_cost'; + +DROP TABLE t1; diff --git a/sql/handler.h b/sql/handler.h index e67e68be931..37c2c86dce8 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -2721,6 +2721,18 @@ public: } virtual double scan_time() { return ulonglong2double(stats.data_file_length) / IO_SIZE + 2; } + + /** + The cost of reading a set of ranges from the table using an index + to access it. + + @param index The index number. + @param ranges The number of ranges to be read. + @param rows Total number of rows to be read. + + This method can be used to calculate the total cost of scanning a table + using an index by calling it using read_time(index, 1, table_size). + */ virtual double read_time(uint index, uint ranges, ha_rows rows) { return rows2double(ranges+rows); } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1ea738d976c..d635eec0e28 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6158,7 +6158,11 @@ best_access_path(JOIN *join, else { /* Estimate cost of reading table. */ - tmp= s->scan_time(); + if (s->table->force_index && !best_key) // index scan + tmp= s->table->file->read_time(s->ref.key, 1, s->records); + else // table scan + tmp= s->scan_time(); + if ((s->table->map & join->outer_join) || disable_jbuf) // Can't use join cache { /* @@ -7611,7 +7615,11 @@ best_extension_by_limited_search(JOIN *join, if (join->sort_by_table && join->sort_by_table != join->positions[join->const_tables].table->table) - /* We have to make a temp table */ + /* + We may have to make a temp table, note that this is only a + heuristic since we cannot know for sure at this point. + Hence it may be wrong. + */ current_read_time+= current_record_count; if (current_read_time < join->best_read) { |