Limiting the Estimation of Records in a Query

Availability:This feature is Experimental quality.

This page describes an alternative when running queries against a large number of table partitions. When a query runs, InnoDB estimates the records in each partition. This process can result in more pages read and more disk I/O, if the buffer pool must fetch the pages from disk. This process increases the query time if there are a large number of partitions.

The addition of two variables make it possible to override records_in_range which effectively bypasses the process.

Warning

The use of these variables may result in improper index selection by the optimizer.

variable innodb_records_in_range
Command Line:--innodb-records-in-range
Dynamic:Yes
Scope:Global
Variable Type:Numeric
Default Value:0

The variable provides a method to limit the number of records estimated for a query.

mysql> SET @@GLOBAL.innodb_records_in_range=100;
100
variable innodb_force_index_records_in_range
Command Line:--innodb-force-index-records-in-range
Dynamic:Yes
Scope:Global
Variable Type:Numeric
Default Value:0

This variable provides a method to override the records_in_range result when a FORCE INDEX is used in a query.

mysql> SET @@GLOBAL.innodb_force_index_records_in_range=100;
100