Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-09-10 17:04:43+02:00, mhansson@stripped +4 -0
Bug#18144: Cost with FORCE/USE index seems incorrect in some cases.
Most of the times FORCE INDEX is ignored when the plan would be more expensive than
table scan. In GROUP BY cases, however, it is not, but the cost-based optimizer
used the same cost for performing a table scan by using a non-covering index.
Fixed by setting the cost to the same as reading a single range the size of the table.
mysql-test/r/key.result@stripped, 2007-09-10 17:04:40+02:00, mhansson@stripped +21 -0
Bug#18144: Correct result
mysql-test/t/key.test@stripped, 2007-09-10 17:04:41+02:00, mhansson@stripped +20 -0
Bug#18144: Test case
sql/handler.h@stripped, 2007-09-10 17:04:41+02:00, mhansson@stripped +12 -0
Bug#18144: Added comment describing handler::read_time(uint, uint, ha_rows)
sql/sql_select.cc@stripped, 2007-09-10 17:04:41+02:00, mhansson@stripped +10 -2
Bug#18144:
- Added the correct cost which occurs if FORCE INDEX is used to scan a table.
- Changed a comment to make a weaker assumption
as the old assumption does not hold in this case.
diff -Nrup a/mysql-test/r/key.result b/mysql-test/r/key.result
--- a/mysql-test/r/key.result 2007-05-30 14:04:02 +02:00
+++ b/mysql-test/r/key.result 2007-09-10 17:04:40 +02:00
@@ -462,4 +462,25 @@ EXPLAIN SELECT MAX(a) FROM t1 FORCE INDE
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
DROP TABLE t1;
+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;
End of 5.0 tests.
diff -Nrup a/mysql-test/t/key.test b/mysql-test/t/key.test
--- a/mysql-test/t/key.test 2007-05-22 14:58:29 +02:00
+++ b/mysql-test/t/key.test 2007-09-10 17:04:41 +02:00
@@ -443,4 +443,24 @@ ALTER TABLE t1 DISABLE KEYS;
EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
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;
+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;
+
--echo End of 5.0 tests.
diff -Nrup a/sql/handler.h b/sql/handler.h
--- a/sql/handler.h 2007-07-12 15:30:16 +02:00
+++ b/sql/handler.h 2007-09-10 17:04:41 +02:00
@@ -574,6 +574,18 @@ public:
void change_table_ptr(TABLE *table_arg) { table=table_arg; }
virtual double scan_time()
{ return ulonglong2double(data_file_length) / IO_SIZE + 2; }
+
+ /**
+ @brief 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); }
virtual const key_map *keys_to_use_for_scanning() { return &key_map_empty; }
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2007-07-23 05:25:59 +02:00
+++ b/sql/sql_select.cc 2007-09-10 17:04:41 +02:00
@@ -4237,7 +4237,11 @@ best_access_path(JOIN *join,
else
{
/* Estimate cost of reading table. */
- tmp= s->table->file->scan_time();
+ if (s->table->force_index && !best_key)
+ tmp= s->table->file->read_time(s->ref.key, 1, s->records);
+ else
+ tmp= s->table->file->scan_time();
+
if (s->table->map & join->outer_join) // Can't use join cache
{
/*
@@ -4904,7 +4908,11 @@ best_extension_by_limited_search(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 ((search_depth == 1) || (current_read_time < join->best_read))
{
