Skip to content

Limiting the Estimation of Records in a Query

Availability: This feature is *technical preview 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.

innodb_records_in_range

Option Description
Command-line --innodb-records-in-range
Scope Global
Dynamic Yes
Data type Numeric
Default 0

Availability: This feature is technical preview quality.

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

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

innodb_force_index_records_in_range

Option Description
Command-line –innodb-force-index-records-in-range
Scope Global
Dynamic Yes
Data type Numeric
Default 0

Availability: This feature is technical preview quality.

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

Using the favor_range_scan optimizer switch

Availability: The feature is technical preview quality.

In specific scenarios, the optimizer chooses to scan a table instead of using a range scan. The conditions are the following:

  • Table with an extremely large number of rows

  • Compound primary keys made of two or more columns

  • WHERE clause contains multiple range conditions

The optimizer_switch controls the behavior of the optimizer. The favor_range_scan switch arbitrarily lowers the cost of a range scan by a factor of 10.

The available values are:

  • ON

  • OFF (Default)

  • DEFAULT

mysql> SET optimizer_switch='favor_range_scan=on';