MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Martin Hansson Date:June 24 2010 12:17pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (martin.hansson:3269)
Bug#18144
View as plain text  
#At file:///data0/martin/bzr/bug18144/n-mr-bf/ based on revid:joerg@stripped

 3269 Martin Hansson	2010-06-24
      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.

    modified:
      mysql-test/r/key.result
      mysql-test/t/key.test
      sql/handler.h
      sql/sql_select.cc
=== modified file 'mysql-test/r/key.result'
--- a/mysql-test/r/key.result	2010-02-20 10:07:32 +0000
+++ b/mysql-test/r/key.result	2010-06-24 12:17:37 +0000
@@ -604,3 +604,30 @@ SELECT 1 as RES FROM t1 AS t1_outer WHER
 (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;

=== modified file 'mysql-test/t/key.test'
--- a/mysql-test/t/key.test	2007-12-05 19:33:36 +0000
+++ b/mysql-test/t/key.test	2010-06-24 12:17:37 +0000
@@ -561,3 +561,23 @@ SELECT 1 as RES FROM t1 AS t1_outer WHER
   (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;

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2010-06-15 08:16:08 +0000
+++ b/sql/handler.h	2010-06-24 12:17:37 +0000
@@ -1375,6 +1375,19 @@ 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); }
   virtual const key_map *keys_to_use_for_scanning() { return &key_map_empty; }

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-06-23 06:27:57 +0000
+++ b/sql/sql_select.cc	2010-06-24 12:17:37 +0000
@@ -4763,7 +4763,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
       {
         /*
@@ -5464,7 +5468,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))
         {


Attachment: [text/bzr-bundle] bzr/martin.hansson@sun.com-20100624121737-g91uw294migp5cbu.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (martin.hansson:3269)Bug#18144Martin Hansson24 Jun