Description:
Even when a query uses "Multi-Range Read", the status variable is not getting incremented.
How to repeat:
== Created a table with the below structure:
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`calendar` date DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`num_2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_calendar` (`calendar`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
== Populated the table with random data. I'll be attaching the dump file to the report. But it works with any random data.
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
== Set Value of optimizer_switch to 'mrr_cost_based=off'
mysql> set optimizer_switch='mrr_cost_based=off';
Query OK, 0 rows affected (0.00 sec)
== From "Explain", we can see that MRR is used.
mysql> explain select * from t1 where calendar>'2021-08-15' limit 3;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | range | idx_calendar | idx_calendar | 4 | NULL | 49 | 100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
== Executed the query and could see that the status variable doesn't get changed.
mysql> show status like '%mrr%';select * from t1 where calendar>'2021-08-15' limit 3;show status like '%mrr%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Handler_mrr_init | 0 |
+------------------+-------+
1 row in set (0.00 sec)
+----+------------+------+-------+
| id | calendar | num | num_2 |
+----+------------+------+-------+
| 1 | 2021-08-20 | 18 | 118 |
| 3 | 2021-08-23 | 99 | 66 |
| 5 | 2021-08-25 | 161 | 129 |
+----+------------+------+-------+
3 rows in set (0.00 sec)
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Handler_mrr_init | 0 |
+------------------+-------+
1 row in set (0.00 sec)
---
TESTED IT ON MySQL 5.7.35 and 8.0.26
Description: Even when a query uses "Multi-Range Read", the status variable is not getting incremented. How to repeat: == Created a table with the below structure: mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `calendar` date DEFAULT NULL, `num` int(11) DEFAULT NULL, `num_2` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_calendar` (`calendar`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) == Populated the table with random data. I'll be attaching the dump file to the report. But it works with any random data. mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 100 | +----------+ 1 row in set (0.00 sec) == Set Value of optimizer_switch to 'mrr_cost_based=off' mysql> set optimizer_switch='mrr_cost_based=off'; Query OK, 0 rows affected (0.00 sec) == From "Explain", we can see that MRR is used. mysql> explain select * from t1 where calendar>'2021-08-15' limit 3; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+ | 1 | SIMPLE | t1 | NULL | range | idx_calendar | idx_calendar | 4 | NULL | 49 | 100.00 | Using index condition; Using MRR | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec) == Executed the query and could see that the status variable doesn't get changed. mysql> show status like '%mrr%';select * from t1 where calendar>'2021-08-15' limit 3;show status like '%mrr%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Handler_mrr_init | 0 | +------------------+-------+ 1 row in set (0.00 sec) +----+------------+------+-------+ | id | calendar | num | num_2 | +----+------------+------+-------+ | 1 | 2021-08-20 | 18 | 118 | | 3 | 2021-08-23 | 99 | 66 | | 5 | 2021-08-25 | 161 | 129 | +----+------------+------+-------+ 3 rows in set (0.00 sec) +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Handler_mrr_init | 0 | +------------------+-------+ 1 row in set (0.00 sec) --- TESTED IT ON MySQL 5.7.35 and 8.0.26