Fixed limit optimization in range optimizer

The issue was that when limit is used,
SQL_SELECT::test_quick_select would set the cost of table scan to be
unreasonable high to force a range to be used.
The problem with this approach was that range was used even when the
cost of range, when it would only read 'limit rows' would be higher
than the cost of a table scan.

This patch fixes it by not accepting ranges when the range can never
have a lower cost than a table scan, even if every row would match the
WHERE clause.

19 files changed, 154 insertions, 85 deletions

diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index 917f2737eec..24c90a62041 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -799,6 +799,7 @@ INSERT INTO t2 SELECT * FROM t1 ORDER BY id;
 
 EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20;
 EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 4000;
+EXPLAIN SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 6000;
 EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 10 AND 12 ORDER BY c3 LIMIT 20;
 EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000;
 
diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc
index 701bffd8dd7..30c1832fadc 100644
--- a/sql/ha_partition.cc
+++ b/sql/ha_partition.cc
@@ -6575,7 +6575,7 @@ ha_rows ha_partition::multi_range_read_info_const(uint keyno,
                                                   RANGE_SEQ_IF *seq,
                                                   void *seq_init_param,
                                                   uint n_ranges, uint *bufsz,
-                                                  uint *mrr_mode,
+                                                  uint *mrr_mode, ha_rows limit,
                                                   Cost_estimate *cost)
 {
   int error;
@@ -6636,7 +6636,7 @@ ha_rows ha_partition::multi_range_read_info_const(uint keyno,
                                     &m_partition_part_key_multi_range_hld[i],
                                     m_part_mrr_range_length[i],
                                     &m_mrr_buffer_size[i],
-                                    &tmp_mrr_mode, &part_cost);
+                                    &tmp_mrr_mode, limit, &part_cost);
       if (tmp_rows == HA_POS_ERROR)
       {
         m_part_spec= save_part_spec;
diff --git a/sql/ha_partition.h b/sql/ha_partition.h
index 01d5aa79f88..e8534488560 100644
--- a/sql/ha_partition.h
+++ b/sql/ha_partition.h
@@ -919,7 +919,7 @@ public:
   ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                       void *seq_init_param,
                                       uint n_ranges, uint *bufsz,
-                                      uint *mrr_mode,
+                                      uint *mrr_mode, ha_rows limit,
                                       Cost_estimate *cost) override;
   ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys,
                                 uint key_parts, uint *bufsz,
diff --git a/sql/handler.h b/sql/handler.h
index a29825db657..e7d848e5994 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -2785,6 +2785,7 @@ public:
   double comp_cost;       /* Cost of comparing found rows with WHERE clause */
   double copy_cost;       /* Copying the data to 'record' */
   double mem_cost;        /* cost of used memory           */
+  double limit_cost;      /* Total cost when restricting rows with limit */
 
   static constexpr double IO_COEFF= 1;
   static constexpr double CPU_COEFF= 1;
@@ -2848,8 +2849,8 @@ public:
   {
     avg_io_cost= 1.0;
     idx_avg_io_cost= 1.0;
-    io_count= idx_io_count= cpu_cost= idx_cpu_cost= mem_cost= import_cost=
-      comp_cost= copy_cost= 0.0;
+    io_count= idx_io_count= cpu_cost= idx_cpu_cost= mem_cost= import_cost= 0.0;
+    comp_cost= copy_cost= limit_cost= 0.0;
   }
 
   void multiply(double m)
@@ -2860,6 +2861,7 @@ public:
     idx_cpu_cost *= m;
     import_cost *= m;
     comp_cost *= m;
+    limit_cost*= m;
     /* Don't multiply mem_cost */
   }
 
@@ -2885,6 +2887,7 @@ public:
     idx_cpu_cost += cost->idx_cpu_cost;
     import_cost += cost->import_cost;
     comp_cost+= cost->comp_cost;
+    limit_cost+= cost->limit_cost;
   }
 
   void add_io(double add_io_cnt, double add_avg_cost)
@@ -4088,7 +4091,7 @@ public:
   virtual ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                               void *seq_init_param, 
                                               uint n_ranges, uint *bufsz,
-                                              uint *mrr_mode,
+                                              uint *mrr_mode, ha_rows limit,
                                               Cost_estimate *cost);
   virtual ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys,
                                         uint key_parts, uint *bufsz, 
@@ -4097,6 +4100,13 @@ public:
                                     uint n_ranges, uint mrr_mode, 
                                     HANDLER_BUFFER *buf);
   virtual int multi_range_read_next(range_id_t *range_info);
+private:
+  inline void calculate_costs(Cost_estimate *cost, uint keyno,
+                              uint ranges, uint flags,
+                              ha_rows total_rows,
+                              ulonglong io_blocks,
+                              ulonglong unassigned_single_point_ranges);
+public:
   /*
     Return string representation of the MRR plan.
 
diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc
index d4103f669fa..e4fd1e75176 100644
--- a/sql/multi_range_read.cc
+++ b/sql/multi_range_read.cc
@@ -22,6 +22,50 @@
 #include "rowid_filter.h"
 #include "optimizer_defaults.h"
 
+
+
+/* The following calculation is the same as in multi_range_read_info() */
+
+void handler::calculate_costs(Cost_estimate *cost, uint keyno,
+                              uint n_ranges, uint flags,
+                              ha_rows total_rows,
+                              ulonglong io_blocks,
+                              ulonglong unassigned_single_point_ranges)
+{
+  double key_cost;
+  cost->reset();
+  cost->avg_io_cost= cost->idx_avg_io_cost= 0; // Not used!
+
+  if (!is_clustering_key(keyno))
+  {
+    key_cost= ha_keyread_time(keyno, n_ranges, total_rows, io_blocks);
+    cost->idx_cpu_cost= key_cost;
+
+    if (!(flags & HA_MRR_INDEX_ONLY))
+    {
+      /* ha_rnd_pos_time includes ROW_COPY_COST */
+      cost->cpu_cost=     ha_rnd_pos_time(total_rows);
+    }
+    else
+    {
+      /* Index only read */
+      cost->copy_cost=    rows2double(total_rows) * KEY_COPY_COST;
+    }
+  }
+  else
+  {
+    /* Clustered index */
+    io_blocks+= unassigned_single_point_ranges;
+    key_cost= ha_keyread_time(keyno, n_ranges, total_rows, io_blocks);
+    cost->idx_cpu_cost= key_cost;
+    cost->copy_cost=    rows2double(total_rows) * ROW_COPY_COST;
+  }
+  cost->comp_cost= (rows2double(total_rows) * WHERE_COST +
+                    MULTI_RANGE_READ_SETUP_COST);
+}
+
+
+
 /****************************************************************************
  * Default MRR implementation (MRR to non-MRR converter)
  ***************************************************************************/
@@ -57,10 +101,12 @@
                   contain scan parameters.
 */
 
+
 ha_rows
 handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                      void *seq_init_param, uint n_ranges_arg,
                                      uint *bufsz, uint *flags,
+                                     ha_rows top_limit,
                                      Cost_estimate *cost)
 {
   KEY_MULTI_RANGE range;
@@ -303,40 +349,22 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
 
   if (total_rows != HA_POS_ERROR)
   {
-    double key_cost;
     set_if_smaller(total_rows, max_rows);
-
-    /* The following calculation is the same as in multi_range_read_info(): */
     *flags |= HA_MRR_USE_DEFAULT_IMPL;
-    cost->reset();
-    cost->avg_io_cost= cost->idx_avg_io_cost= 0; // Not used!
-
-    if (!is_clustering_key(keyno))
+    calculate_costs(cost, keyno, n_ranges, *flags, total_rows,
+                    io_blocks, unassigned_single_point_ranges);
+    if (top_limit < total_rows)
     {
-      key_cost= ha_keyread_time(keyno, n_ranges, total_rows, io_blocks);
-      cost->idx_cpu_cost= key_cost;
-
-      if (!(*flags & HA_MRR_INDEX_ONLY))
-      {
-        /* ha_rnd_pos_time includes ROW_COPY_COST */
-        cost->cpu_cost=     ha_rnd_pos_time(total_rows);
-      }
-      else
-      {
-        /* Index only read */
-        cost->copy_cost=    rows2double(total_rows) * KEY_COPY_COST;
-      }
-    }
-    else
-    {
-      /* Clustered index */
-      io_blocks+= unassigned_single_point_ranges;
-      key_cost= ha_keyread_time(keyno, n_ranges, total_rows, io_blocks);
-      cost->idx_cpu_cost= key_cost;
-      cost->copy_cost=    rows2double(total_rows) * ROW_COPY_COST;
+      /*
+        Calculate what the cost would be if we only have to read 'top_limit'
+        rows. This is the lowest possible cost fwhen using the range
+        when we find the 'accepted rows' at once.
+      */
+      Cost_estimate limit_cost;
+      calculate_costs(&limit_cost, keyno, n_ranges, *flags, top_limit,
+                      io_blocks, unassigned_single_point_ranges);
+      cost->limit_cost= limit_cost.total_cost();
     }
-    cost->comp_cost= (rows2double(total_rows) * WHERE_COST +
-                      MULTI_RANGE_READ_SETUP_COST);
   }
   DBUG_PRINT("statistics",
              ("key: %s  rows: %llu  total_cost: %.3f  io_blocks: %llu  "
@@ -1717,8 +1745,9 @@ ha_rows DsMrr_impl::dsmrr_info(uint keyno, uint n_ranges, uint rows,
 */
 
 ha_rows DsMrr_impl::dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq,
-                                 void *seq_init_param, uint n_ranges, 
-                                 uint *bufsz, uint *flags, Cost_estimate *cost)
+                                     void *seq_init_param, uint n_ranges,
+                                     uint *bufsz, uint *flags, ha_rows limit,
+                                     Cost_estimate *cost)
 {
   ha_rows rows;
   uint def_flags= *flags;
@@ -1728,7 +1757,9 @@ ha_rows DsMrr_impl::dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                                            seq_init_param,
                                                            n_ranges, 
                                                            &def_bufsz, 
-                                                           &def_flags, cost);
+                                                           &def_flags,
+                                                           limit,
+                                                           cost);
   if (rows == HA_POS_ERROR)
   {
     /* Default implementation can't perform MRR scan => we can't either */
diff --git a/sql/multi_range_read.h b/sql/multi_range_read.h
index 37a00e3086f..76b026f994a 100644
--- a/sql/multi_range_read.h
+++ b/sql/multi_range_read.h
@@ -576,7 +576,7 @@ public:
 
   ha_rows dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq, 
                             void *seq_init_param, uint n_ranges, uint *bufsz,
-                            uint *flags, Cost_estimate *cost);
+                           uint *flags, ha_rows limit, Cost_estimate *cost);
 
   int dsmrr_explain_info(uint mrr_mode, char *str, size_t size);
 private:
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 7f943a45fbc..223aa61e1ca 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -345,7 +345,8 @@ struct st_index_scan_info;
 struct st_ror_scan_info;
 
 static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts);
-static ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
+static ha_rows check_quick_select(PARAM *param, uint idx, ha_rows limit,
+                                  bool index_only,
                                   SEL_ARG *tree, bool update_tbl_stats, 
                                   uint *mrr_flags, uint *bufsize,
                                   Cost_estimate *cost, bool *is_ror_scan);
@@ -356,7 +357,8 @@ QUICK_RANGE_SELECT *get_quick_select(PARAM *param,uint index,
 static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
                                        bool index_read_must_be_used,
                                        bool for_range_access,
-                                       double read_time);
+                                       double read_time, ha_rows limit,
+                                       bool using_table_scan);
 static
 TRP_INDEX_INTERSECT *get_best_index_intersect(PARAM *param, SEL_TREE *tree,
                                               double read_time);
@@ -370,7 +372,9 @@ TRP_ROR_INTERSECT *get_best_covering_ror_intersect(PARAM *param,
                                                    double read_time);
 static
 TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge,
-                                         double read_time, bool named_trace= false);
+                                         double read_time, ha_rows limit,
+                                         bool named_trace,
+                                         bool using_table_scan);
 static
 TABLE_READ_PLAN *merge_same_index_scans(PARAM *param, SEL_IMERGE *imerge,
                                         TRP_INDEX_MERGE *imerge_trp,
@@ -2715,21 +2719,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
   else
   {
     read_time= head->file->ha_scan_and_compare_time(records);
-
-    /*
-      Force the detection of range access if LIMIT is used.
-      The idea is that we want to store all possible range
-      accesses to see if we can use them to resolve an ORDER BY.
-      Ranges with too high costs will be pruned in best_access_path().
-
-      The test for read_time is there only to not modify read_time if
-      ha_scan_and_compare_time() returned a really big value
-    */
-    if (limit < records && read_time < (double) records * 2)
-    {
-      read_time= (double) records * 2; // Force to use index
+    if (limit < records)
       notnull_cond= NULL;
-    }
   }
 
   possible_keys.clear_all();
@@ -2959,7 +2950,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
       /* Get best 'range' plan and prepare data for making other plans */
       if ((range_trp= get_key_scans_params(&param, tree,
                                            only_single_index_range_scan,
-                                           true, best_read_time)))
+                                           true, best_read_time, limit,
+                                           1)))
       {
         best_trp= range_trp;
         best_read_time= best_trp->read_cost;
@@ -3025,7 +3017,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
         Json_writer_array trace_idx_merge(thd, "analyzing_index_merge_union");
         while ((imerge= it++))
         {
-          new_conj_trp= get_best_disjunct_quick(&param, imerge, best_read_time);
+          new_conj_trp= get_best_disjunct_quick(&param, imerge, best_read_time,
+                                                limit, 0, 1);
           if (new_conj_trp)
             param.table->set_opt_range_condition_rows(new_conj_trp->records);
           if (new_conj_trp &&
@@ -5210,7 +5203,9 @@ static double get_sweep_read_cost(const PARAM *param, ha_rows records,
 
 static
 TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge,
-                                         double read_time, bool named_trace)
+                                         double read_time, ha_rows limit,
+                                         bool named_trace,
+                                         bool using_table_scan)
 {
   SEL_TREE **ptree;
   TRP_INDEX_MERGE *imerge_trp= NULL;
@@ -5274,7 +5269,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge,
                                         "tree in SEL_IMERGE"););
     Json_writer_object trace_idx(thd);
     if (!(*cur_child= get_key_scans_params(param, *ptree, TRUE, FALSE,
-                                           read_time)))
+                                           read_time, limit, using_table_scan)))
     {
       /*
         One of index scans in this index_merge is more expensive than entire
@@ -5624,7 +5619,8 @@ TABLE_READ_PLAN *merge_same_index_scans(PARAM *param, SEL_IMERGE *imerge,
   DBUG_ASSERT(imerge->trees_next>imerge->trees);
 
   if (imerge->trees_next-imerge->trees > 1)
-    trp= get_best_disjunct_quick(param, imerge, read_time, true);
+    trp= get_best_disjunct_quick(param, imerge, read_time, HA_POS_ERROR, true,
+                                 0);
   else
   {
     /*
@@ -7594,7 +7590,8 @@ TRP_ROR_INTERSECT *get_best_covering_ror_intersect(PARAM *param,
 static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
                                        bool index_read_must_be_used, 
                                        bool for_range_access,
-                                       double read_time)
+                                       double read_time, ha_rows limit,
+                                       bool using_table_scan)
 {
   uint idx, UNINIT_VAR(best_idx);
   SEL_ARG *key_to_read= NULL;
@@ -7646,7 +7643,7 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
       Json_writer_object trace_idx(thd);
       trace_idx.add("index", param->table->key_info[keynr].name);
 
-      found_records= check_quick_select(param, idx, read_index_only, key,
+      found_records= check_quick_select(param, idx, limit, read_index_only, key,
                                         for_range_access, &mrr_flags,
                                         &buf_size, &cost, &is_ror_scan);
 
@@ -7680,19 +7677,29 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
         trace_range.end();
 
         if (unlikely(trace_idx.trace_started()))
+        {
           trace_idx.
             add("rowid_ordered", is_ror_scan).
             add("using_mrr", !(mrr_flags & HA_MRR_USE_DEFAULT_IMPL)).
             add("index_only", read_index_only).
             add("rows", found_records).
             add("cost", found_read_time);
+          if (using_table_scan && cost.limit_cost != 0.0)
+            trace_idx.add("cost_with_limit", cost.limit_cost);
+        }
       }
       if (is_ror_scan)
       {
         tree->n_ror_scans++;
         tree->ror_scans_map.set_bit(idx);
       }
-      if (read_time > found_read_time)
+      /*
+        Use range if best range so far or if we are comparing to a table scan
+        and the cost with limit approximation is better than the table scan
+      */
+      if (read_time > found_read_time ||
+          (using_table_scan && cost.limit_cost != 0.0 &&
+           read_time > cost.limit_cost))
       {
         read_time=    found_read_time;
         best_records= found_records;
@@ -7700,6 +7707,7 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
         best_idx= idx;
         best_mrr_flags= mrr_flags;
         best_buf_size=  buf_size;
+        using_table_scan= 0;
         trace_idx.add("chosen", true);
       }
       else if (unlikely(trace_idx.trace_started()))
@@ -11703,7 +11711,8 @@ static bool check_if_first_key_part_has_only_one_value(SEL_ARG *arg)
 */
 
 static
-ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
+ha_rows check_quick_select(PARAM *param, uint idx, ha_rows limit,
+                           bool index_only,
                            SEL_ARG *tree, bool update_tbl_stats, 
                            uint *mrr_flags, uint *bufsize, Cost_estimate *cost,
                            bool *is_ror_scan)
@@ -11765,7 +11774,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only,
   */
   if (param->table->pos_in_table_list->is_non_derived())
     rows= file->multi_range_read_info_const(keynr, &seq_if, (void*)&seq, 0,
-                                            bufsize, mrr_flags, cost);
+                                            bufsize, mrr_flags, limit, cost);
   param->quick_rows[keynr]= rows;
   if (rows != HA_POS_ERROR)
   {
diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc
index 5b6af042f68..9e9aa478f3c 100644
--- a/storage/connect/ha_connect.cc
+++ b/storage/connect/ha_connect.cc
@@ -7405,7 +7405,8 @@ int ha_connect::multi_range_read_next(range_id_t *range_info)
 ha_rows ha_connect::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                                void *seq_init_param,
                                                uint n_ranges, uint *bufsz,
-                                               uint *flags, Cost_estimate *cost)
+                                                uint *flags, ha_rows limit,
+                                                Cost_estimate *cost)
 {
   /*
     This call is here because there is no location where this->table would
@@ -7419,7 +7420,7 @@ ha_rows ha_connect::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
     *flags|= HA_MRR_USE_DEFAULT_IMPL;
 
   ha_rows rows= ds_mrr.dsmrr_info_const(keyno, seq, seq_init_param, n_ranges,
-                                        bufsz, flags, cost);
+                                        bufsz, flags, limit, cost);
   xp->g->Mrr= !(*flags & HA_MRR_USE_DEFAULT_IMPL);
   return rows;
 } // end of multi_range_read_info_const
diff --git a/storage/connect/ha_connect.h b/storage/connect/ha_connect.h
index ca3b69bb552..a5b139a5689 100644
--- a/storage/connect/ha_connect.h
+++ b/storage/connect/ha_connect.h
@@ -502,7 +502,8 @@ int index_prev(uchar *buf);
   ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                       void *seq_init_param,
                                       uint n_ranges, uint *bufsz,
-                                      uint *flags, Cost_estimate *cost);
+                                      uint *flags, ha_rows limit,
+                                      Cost_estimate *cost);
   ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys,
                                 uint key_parts, uint *bufsz,
                                 uint *flags, Cost_estimate *cost);
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 6c99313d29f..67ef1b90a95 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -19987,6 +19987,7 @@ ha_innobase::multi_range_read_info_const(
 	uint		n_ranges,
 	uint*		bufsz,
 	uint*		flags,
+        ha_rows         limit,
 	Cost_estimate*	cost)
 {
 	/* See comments in ha_myisam::multi_range_read_info_const */
@@ -19996,8 +19997,9 @@ ha_innobase::multi_range_read_info_const(
 		*flags |= HA_MRR_USE_DEFAULT_IMPL;
 	}
 
-	ha_rows res= m_ds_mrr.dsmrr_info_const(keyno, seq, seq_init_param, n_ranges,
-			bufsz, flags, cost);
+	ha_rows res= m_ds_mrr.dsmrr_info_const(keyno, seq, seq_init_param,
+                                               n_ranges,
+                                               bufsz, flags, limit, cost);
 	return res;
 }
 
diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h
index 92d6dcb9afb..60b56b4a22f 100644
--- a/storage/innobase/handler/ha_innodb.h
+++ b/storage/innobase/handler/ha_innodb.h
@@ -383,6 +383,7 @@ public:
 		uint			n_ranges,
 		uint*			bufsz,
 		uint*			flags,
+                ha_rows                 limit,
 		Cost_estimate*		cost) override;
 
 	/** Initialize multi range read and get information.
diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc
index 275df557dbd..815f77637ad 100644
--- a/storage/maria/ha_maria.cc
+++ b/storage/maria/ha_maria.cc
@@ -4157,7 +4157,8 @@ int ha_maria::multi_range_read_next(range_id_t *range_info)
 ha_rows ha_maria::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                                void *seq_init_param,
                                                uint n_ranges, uint *bufsz,
-                                               uint *flags, Cost_estimate *cost)
+                                              uint *flags, ha_rows limit,
+                                              Cost_estimate *cost)
 {
   /*
     This call is here because there is no location where this->table would
@@ -4166,7 +4167,7 @@ ha_rows ha_maria::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
   */
   ds_mrr.init(this, table);
   return ds_mrr.dsmrr_info_const(keyno, seq, seq_init_param, n_ranges, bufsz,
-                                 flags, cost);
+                                 flags, limit, cost);
 }
 
 ha_rows ha_maria::multi_range_read_info(uint keyno, uint n_ranges, uint keys,
diff --git a/storage/maria/ha_maria.h b/storage/maria/ha_maria.h
index 38919d5c542..1486c2d7904 100644
--- a/storage/maria/ha_maria.h
+++ b/storage/maria/ha_maria.h
@@ -175,7 +175,8 @@ public:
   ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                       void *seq_init_param,
                                       uint n_ranges, uint *bufsz,
-                                      uint *flags, Cost_estimate *cost) override final;
+                                      uint *flags, ha_rows limit,
+                                      Cost_estimate *cost) override final;
   ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys,
                                 uint key_parts, uint *bufsz,
                                 uint *flags, Cost_estimate *cost) override final;
diff --git a/storage/mroonga/ha_mroonga.cpp b/storage/mroonga/ha_mroonga.cpp
index 2d8f372e6df..85d6473ded3 100644
--- a/storage/mroonga/ha_mroonga.cpp
+++ b/storage/mroonga/ha_mroonga.cpp
@@ -12314,6 +12314,7 @@ ha_rows ha_mroonga::wrapper_multi_range_read_info_const(uint keyno,
                                                         uint n_ranges,
                                                         uint *bufsz,
                                                         uint *flags,
+                                                        ha_rows limit,
                                                         Cost_estimate *cost)
 {
   MRN_DBUG_ENTER_METHOD();
@@ -12321,7 +12322,8 @@ ha_rows ha_mroonga::wrapper_multi_range_read_info_const(uint keyno,
   KEY *key_info = &(table->key_info[keyno]);
   if (mrn_is_geo_key(key_info)) {
     rows = handler::multi_range_read_info_const(keyno, seq, seq_init_param,
-                                                n_ranges, bufsz, flags, cost);
+                                                n_ranges, bufsz, flags, limit,
+                                                cost);
     DBUG_RETURN(rows);
   }
   MRN_SET_WRAP_SHARE_KEY(share, table->s);
@@ -12330,7 +12332,7 @@ ha_rows ha_mroonga::wrapper_multi_range_read_info_const(uint keyno,
     set_pk_bitmap();
   rows = wrap_handler->multi_range_read_info_const(keyno, seq, seq_init_param,
                                                    n_ranges, bufsz, flags,
-                                                   cost);
+                                                   limit, cost);
   MRN_SET_BASE_SHARE_KEY(share, table->s);
   MRN_SET_BASE_TABLE_KEY(this, table);
   DBUG_RETURN(rows);
@@ -12342,20 +12344,21 @@ ha_rows ha_mroonga::storage_multi_range_read_info_const(uint keyno,
                                                         uint n_ranges,
                                                         uint *bufsz,
                                                         uint *flags,
+                                                        ha_rows limit,
                                                         Cost_estimate *cost)
 {
   MRN_DBUG_ENTER_METHOD();
   ha_rows rows = handler::multi_range_read_info_const(keyno, seq,
                                                       seq_init_param,
                                                       n_ranges, bufsz, flags,
-                                                      cost);
+                                                      limit, cost);
   DBUG_RETURN(rows);
 }
 
 ha_rows ha_mroonga::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                                 void *seq_init_param,
                                                 uint n_ranges, uint *bufsz,
-                                                uint *flags,
+                                                uint *flags, ha_rows limit,
                                                 Cost_estimate *cost)
 {
   MRN_DBUG_ENTER_METHOD();
@@ -12364,11 +12367,11 @@ ha_rows ha_mroonga::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
   {
     rows = wrapper_multi_range_read_info_const(keyno, seq, seq_init_param,
                                                n_ranges, bufsz,
-                                               flags, cost);
+                                               flags, limit, cost);
   } else {
     rows = storage_multi_range_read_info_const(keyno, seq, seq_init_param,
                                                n_ranges, bufsz,
-                                               flags, cost);
+                                               flags, limit, cost);
   }
   DBUG_RETURN(rows);
 }
diff --git a/storage/mroonga/ha_mroonga.hpp b/storage/mroonga/ha_mroonga.hpp
index 827714f5edb..27219ffd158 100644
--- a/storage/mroonga/ha_mroonga.hpp
+++ b/storage/mroonga/ha_mroonga.hpp
@@ -505,7 +505,8 @@ public:
   ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                       void *seq_init_param,
                                       uint n_ranges, uint *bufsz,
-                                      uint *flags, Cost_estimate *cost) mrn_override;
+                                      uint *flags, ha_rows limit,
+                                      Cost_estimate *cost) mrn_override;
   ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys,
 #ifdef MRN_HANDLER_HAVE_MULTI_RANGE_READ_INFO_KEY_PARTS
                                 uint key_parts,
@@ -1057,6 +1058,7 @@ private:
                                               uint n_ranges,
                                               uint *bufsz,
                                               uint *flags,
+                                              ha_rows limit,
                                               Cost_estimate *cost);
   ha_rows storage_multi_range_read_info_const(uint keyno,
                                               RANGE_SEQ_IF *seq,
@@ -1064,6 +1066,7 @@ private:
                                               uint n_ranges,
                                               uint *bufsz,
                                               uint *flags,
+                                              ha_rows limit,
                                               Cost_estimate *cost);
   ha_rows wrapper_multi_range_read_info(uint keyno, uint n_ranges, uint keys,
 #ifdef MRN_HANDLER_HAVE_MULTI_RANGE_READ_INFO_KEY_PARTS
diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc
index bbae99ce2d3..df47bda9c86 100644
--- a/storage/myisam/ha_myisam.cc
+++ b/storage/myisam/ha_myisam.cc
@@ -2663,7 +2663,8 @@ int ha_myisam::multi_range_read_next(range_id_t *range_info)
 ha_rows ha_myisam::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                                void *seq_init_param, 
                                                uint n_ranges, uint *bufsz,
-                                               uint *flags, Cost_estimate *cost)
+                                               uint *flags, ha_rows limit,
+                                               Cost_estimate *cost)
 {
   /*
     This call is here because there is no location where this->table would
@@ -2672,7 +2673,7 @@ ha_rows ha_myisam::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
   */
   ds_mrr.init(this, table);
   return ds_mrr.dsmrr_info_const(keyno, seq, seq_init_param, n_ranges, bufsz,
-                                 flags, cost);
+                                 flags, limit, cost);
 }
 
 ha_rows ha_myisam::multi_range_read_info(uint keyno, uint n_ranges, uint keys,
diff --git a/storage/myisam/ha_myisam.h b/storage/myisam/ha_myisam.h
index 55ce19494d9..d6ae007a446 100644
--- a/storage/myisam/ha_myisam.h
+++ b/storage/myisam/ha_myisam.h
@@ -165,7 +165,8 @@ public:
   ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq,
                                       void *seq_init_param, 
                                       uint n_ranges, uint *bufsz,
-                                      uint *flags, Cost_estimate *cost);
+                                      uint *flags, ha_rows limit,
+                                      Cost_estimate *cost) override;
   ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys,
                                 uint key_parts, uint *bufsz, 
                                 uint *flags, Cost_estimate *cost);
diff --git a/storage/spider/ha_spider.cc b/storage/spider/ha_spider.cc
index 2a62f6f2bbd..7d458350e79 100644
--- a/storage/spider/ha_spider.cc
+++ b/storage/spider/ha_spider.cc
@@ -3172,6 +3172,7 @@ ha_rows ha_spider::multi_range_read_info_const(
   uint n_ranges,
   uint *bufsz,
   uint *flags,
+  ha_rows limit,
   Cost_estimate *cost
 )
 {
@@ -3211,6 +3212,7 @@ ha_rows ha_spider::multi_range_read_info_const(
       n_ranges,
       bufsz,
       flags,
+      limit,
       cost
     );
   *flags &= ~HA_MRR_USE_DEFAULT_IMPL;
diff --git a/storage/spider/ha_spider.h b/storage/spider/ha_spider.h
index 1c5c867b2f8..989098a7e63 100644
--- a/storage/spider/ha_spider.h
+++ b/storage/spider/ha_spider.h
@@ -252,6 +252,7 @@ public:
     uint n_ranges,
     uint *bufsz,
     uint *flags,
+    ha_rows limit,
     Cost_estimate *cost
   );
   ha_rows multi_range_read_info(