bzr push into mysql-trunk branch (olav.sandstaa:3916 to 3917) WL#6082

[Olav Sandstaa]

 3917 Olav Sandstaa	2012-06-06
      WL#6082 Improve the Disk-Sweep Multi-Range Read cost model
      
      Patch 5: IO cost estimate is too high compared to "standard MRR"
      
      The DS-MRR cost model has two different ways for calculating the
      IO-cost of doing a "disk sweep scan":
      
      a. Disk-sweep model: Used when the data is not stored as a
         clustered index: The model estimates the number of blocks that will
         be accessed and models the time to read these using sequential
         scan/read of a data file. This model is used for MyISAM.
      
      b. If the data is stored as a clustered index: The cost model
         estimates this as having to do one random block access 
         per record. This model does not take into account that
         we actually will be accessing the table as a scan. This
         model is used for InnoDB.
      
      The focus is on improving the model for InnoDB since this does not
      take into account any benefit from sequential access of the table. Due
      to this, the IO-cost estimate will in almost all cases be larger for
      DS-MRR than "standard MRR" (and thus, the cost based DS-MRR will not
      be used for InnoDB).
      
      The fix for this problem is to switch to use the "disk streaming IO
      cost model" (as described as a above) also when the data is stored in
      a clustered index.
     @ mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
        WL#6082: Change in cost estimate due the reduced IO-cost for DS-MRR.
     @ mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
        WL#6082: Change in cost estimate due the reduced IO-cost for DS-MRR.
     @ sql/handler.cc
        WL#6082: Change the DS-MRR cost model for how the IO-cost of a
        disk sweep when the data is stored as a clustered index.

    modified:
      mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
      mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
      sql/handler.cc
 3916 Olav Sandstaa	2012-06-06 [merge]
      Merge from mysql-trunk to mysql-trunk-wl6082

    modified:
      mysql-test/include/index_merge1.inc
      mysql-test/include/index_merge2.inc
      mysql-test/include/index_merge_ror.inc
      mysql-test/include/index_merge_ror_cpk.inc
      mysql-test/include/mrr_tests.inc
      mysql-test/suite/engines/funcs/r/se_join_default.result
      mysql-test/suite/engines/funcs/t/se_join_cross.test
      mysql-test/suite/engines/funcs/t/se_join_default.test
      mysql-test/suite/engines/funcs/t/se_join_inner.test
      mysql-test/suite/engines/funcs/t/se_join_left.test
      mysql-test/suite/engines/funcs/t/se_join_left_outer.test
      mysql-test/suite/engines/funcs/t/se_join_natural_left.test
      mysql-test/suite/engines/funcs/t/se_join_natural_left_outer.test
      mysql-test/suite/engines/funcs/t/se_join_natural_right.test
      mysql-test/suite/engines/funcs/t/se_join_natural_right_outer.test
      mysql-test/suite/engines/funcs/t/se_join_right.test
      mysql-test/suite/engines/funcs/t/se_join_right_outer.test
      mysql-test/suite/engines/funcs/t/se_join_straight.test
      mysql-test/t/index_merge_innodb.test
      sql/log_event.h
      storage/innobase/dict/dict0dict.cc
      storage/innobase/dict/dict0load.cc
      storage/innobase/dict/dict0stats.cc
      storage/innobase/dict/dict0stats_background.cc
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/include/dict0dict.h
      storage/innobase/include/dict0load.h
      storage/innobase/include/dict0stats.h
      storage/innobase/include/dict0stats.ic
      storage/innobase/row/row0mysql.cc
      strings/ctype-uca.c
=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result'

=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-06-06 08:17:11 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result	2012-06-06 11:44:43 +0000
@@ -2192,7 +2192,7 @@
                                     "using_mrr": true,
                                     "index_only": false,
                                     "rows": 1,
-                                    "cost": 2.2,
+                                    "cost": 2.1031,
                                     "chosen": false,
                                     "cause": "cost"
                                   },

=== modified file 'mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result	2012-06-06 08:17:11 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result	2012-06-06 11:44:43 +0000
@@ -2192,7 +2192,7 @@
                                     "using_mrr": true,
                                     "index_only": false,
                                     "rows": 1,
-                                    "cost": 2.2,
+                                    "cost": 2.1031,
                                     "chosen": false,
                                     "cause": "cost"
                                   },

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2012-06-06 08:17:11 +0000
+++ b/sql/handler.cc	2012-06-06 11:44:43 +0000
@@ -6444,13 +6444,7 @@
   DBUG_ENTER("get_sweep_read_cost");
 
   DBUG_ASSERT(cost->is_zero());
-  if (table->file->primary_key_is_clustered())
-  {
-    cost->add_io(table->file->read_time(table->s->primary_key,
-                                        (uint)nrows, nrows) *
-                 Cost_estimate::IO_BLOCK_READ_COST());
-  }
-  else
+  if(nrows > 0)
   {
     double n_blocks=
       ceil(ulonglong2double(table->file->stats.data_file_length) / IO_SIZE);

No bundle (reason: useless for push emails).