SAMU-240 Port fix for Mysql "Bug#16697792: POOR EXECUTION PLAN ..."

[midenok]

This change from 4bf48e8bf0d fixes the result of main.distinct

diff --git sql/opt_costconstants.cc sql/opt_costconstants.cc
index d825fafba4c..2ab0ce4144e 100644
--- sql/opt_costconstants.cc
+++ sql/opt_costconstants.cc
@@ -142,7 +142,7 @@ cost_constant_error Server_cost_constants::set(const LEX_CSTRING &name,
 */

 // The cost of reading a block from a main memory buffer pool
-const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 1.0;
+const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 0.25;

 // The cost of reading a block from an IO device (disk)

MEMORY_BLOCK_READ_COST was introduced by 07cad655133

commit 07cad655133
Author: Olav Sandstaa <olav.sandstaa@oracle.com>
Date:   Sun Jun 28 11:38:26 2015 +0200

    WL#7340 IO aware cost estimate function for data access

This change is important:

diff --git sql/sql_select.cc sql/sql_select.cc
index 5275ebfec5a..64cd0daa112 100644
--- sql/sql_select.cc
+++ sql/sql_select.cc
@@ -4129,7 +4129,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
         */
         const Cost_estimate table_scan_time= table->file->table_scan_cost();
         const double index_scan_time= select_limit / rec_per_key *
-          min<double>(table->cost_model()->io_block_read_cost(rec_per_key),
+          min<double>(table->cost_model()->page_read_cost(rec_per_key),
                       table_scan_time.total_cost());

         /*

The core logic for MEMORY_BLOCK_READ_COST is done by:

double Cost_model_table::page_read_cost(double pages) const
{
  DBUG_ASSERT(m_initialized);
  DBUG_ASSERT(pages >= 0.0);

  const double in_mem= m_table->file->table_in_memory_estimate();

  const double pages_in_mem= pages * in_mem;
  const double pages_on_disk= pages - pages_in_mem;
  DBUG_ASSERT(pages_on_disk >= 0.0);

  const double cost= buffer_block_read_cost(pages_in_mem) +
    io_block_read_cost(pages_on_disk);

  return cost;
}

page_read_cost() depends on 91b65d99

commit 91b65d99c40
Author: Olav Sandstaa <olav.sandstaa@oracle.com>
Date:   Mon Jan 12 10:41:38 2015 +0100

    WL#7168 API for estimates for how much of table and index data that is in memory buffer

91b65d99 depends on get_memory_buffer_size() from 77059678

commit 77059678d80
Author: Olav Sandstaa <olav.sandstaa@oracle.com>
Date:   Wed May 30 11:33:26 2012 +0200

    WL#6082 Improve the Disk-Sweep Multi-Range Read cost model

Other commits

@@ -5224,18 +5224,18 @@ INSERT INTO `cc` VALUES
 EXPLAIN SELECT `varchar_nokey` g1  FROM cc  WHERE `int_nokey` AND `int_key`  <= 4
 HAVING g1  ORDER  BY `varchar_key` LIMIT  6   ;
 id     select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered             Extra
-1      SIMPLE  cc      NULL    ALL     int_key NULL    NULL    NULL    20      45.00   Using where; Using filesort
+1      SIMPLE  cc      NULL    range   int_key int_key 4       NULL    10      90.00   Using where; Using filesort

commit dc1c0eb8b6e (HEAD)
Author: Olav Sandstaa <olav.sandstaa@oracle.com>
Date:   Fri Feb 28 09:54:21 2014 +0100

    WL#7182 Optimizer Cost Model API

io_block_read_cost() introduced and 1.0 replaced with it. io_block_read_cost() is added to get_merge_cost(), etc.

commit 250d5fb8bed
Author: Olav Sandstaa <olav.sandstaa@oracle.com>
Date:   Wed Jan 8 11:32:25 2014 +0100

    WL#7209 Handler interface for new cost model

[midenok]

Mysql

        const double index_scan_time =
            select_limit / rec_per_key *
            min<double>(table->file->page_read_cost(nr, rec_per_key),
                        table_scan_time.total_cost());

MariaDB

        index_scan_time= select_limit/rec_per_key *
                         MY_MIN(rec_per_key, table->file->scan_time());