MySQL Bugs: #111696: The end range of where clause will clear the index->search_info->hash

Bug #111696	The end range of where clause will clear the index->search_info->hash_analysis
Submitted:	8 Jul 2023 10:24	Modified:	20 Dec 2023 4:44
Reporter:	shengchun cao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ahi

Description:
When a select SQL read data from table using non-unique index or prefix of unique index or prefix of composite primary key, the end range of where clause will clear the index->search_info->hash_analysis, so that the primary key and secondary index can't build adaptive hash index.

The problem described above is triggered when the start range and end range meeting the following conditions:
1. the operator of start range is '>='(eg. i1 >= 2), and the operator of end range is '<='(eg. i1 <= 10)
2. the operator of start range is '>'(eg. i1 > 2), and the operator of end range is '<'(eg. i1 < 10)
3. a single range(eg. i1 = 10), this is equivalent to i1 >= 10 and i1 <= 10, that is to say it is the same as the number 1.

How to repeat:
Step 1: Create table and insert data for test.

CREATE TABLE `t_ahi` (
  `i1` int unsigned NOT NULL DEFAULT '0',
  `i2` int unsigned NOT NULL DEFAULT '0',
  `i3` int unsigned NOT NULL DEFAULT '0',
  `i4` int unsigned NOT NULL DEFAULT '0',
  `s1` varchar(32) NOT NULL DEFAULT '',
  `s2` varchar(32) NOT NULL DEFAULT '',
  `s3` varchar(32) NOT NULL DEFAULT '',
  `s4` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`i1`,`i2`),
  UNIQUE KEY `uniq_s1_s2_s3` (`s1`,`s2`,`s3`),
  KEY `idx_i3`(`i3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `t_ahi` (`i1`, `i2`, `i3`, `i4`, `s1`, `s2`, `s3`, `s4`) VALUES 
(1, 10, 20, 30, '1', 's10', 's20', 's30'),
(2, 10, 20, 35, '2', 's10', 's20', 's35'),
(3, 10, 20, 38, '3', 's10', 's20', 's38'),
(4, 10, 25, 30, '4', 's10', 's25', 's30'),
(5, 10, 25, 35, '5', 's10', 's25', 's35'),
(6, 10, 25, 38, '6', 's10', 's25', 's38'),
(7, 10, 28, 30, '7', 's10', 's28', 's30'),
(8, 10, 28, 35, '8', 's10', 's28', 's35'),
(9, 10, 28, 38, '9', 's10', 's28', 's38'),
(10, 15, 20, 30, '10', 's10', 's20', 's30'),
(11, 15, 20, 35, '11', 's10', 's20', 's35'),
(12, 15, 20, 38, '12', 's10', 's20', 's38'),
(13, 15, 25, 30, '13', 's10', 's25', 's30'),
(14, 15, 25, 35, '14', 's10', 's25', 's35'),
(15, 15, 25, 38, '15', 's10', 's25', 's38'),
(16, 15, 28, 30, '16', 's10', 's28', 's30'),
(17, 15, 28, 35, '17', 's10', 's28', 's35'),
(18, 15, 28, 38, '18', 's10', 's28', 's38'),
(19, 18, 20, 30, '19', 's10', 's20', 's30'),
(20, 18, 20, 35, '20', 's10', 's20', 's35'),
(21, 18, 20, 38, '21', 's10', 's20', 's38'),
(22, 18, 25, 30, '22', 's10', 's25', 's30'),
(23, 18, 25, 35, '23', 's10', 's25', 's35'),
(24, 18, 25, 38, '24', 's10', 's25', 's38'),
(25, 18, 28, 30, '25', 's10', 's28', 's30'),
(26, 18, 28, 35, '26', 's10', 's28', 's35'),
(27, 18, 28, 38, '27', 's10', 's28', 's38');

Step 2: Login to MySQL 8.0.32.
Step 3: Execute the following SQL to clear the statistics of adaptive hash index, like this: 0.00 hash searches/s, 0.00 non-hash searches/s.
        SHOW ENGINE InnoDB STATUS
Step 4: Execute any of the following SQLs 200 times.
        SQL 1(prefix of composite primary key): SELECT * FROM t_ahi WHERE i1 >= 2 AND i1 <= 10
        SQL 2(prefix of composite primary key): SELECT * FROM t_ahi WHERE i1 > 2 AND i1 < 10
        SQL 3(prefix of unique index): SELECT i1, i2, s1 FROM t_ahi WHERE s1 >= '10' AND s1 <= '20'
        SQL 4(prefix of unique index): SELECT i1, i2, s1 FROM t_ahi WHERE s1 > '10' AND s1 < '20'
        SQL 5(whole of non-unique index): SELECT i1, i2, i3 FROM t_ahi WHERE i3 = 20

        Note: Only include the fields of index in SQL 3/4/5, to avoid read compelete row from cluster index, because that will build and use adaptive hash index of cluster index.

Step 5: Execute the following SQL to get the statistics of adaptive hash index
        SHOW ENGINE InnoDB STATUS

The result of step 5 will like this: 0.00 hash searches/s, 119.98 non-hash searches/s.
The number of hash searches is zero, meaning that MySQL has not use the adaptive hash index, because it has not ever build the adaptive hash index.

Suggested fix:
I have mentioned that the function btr_search_info_update() is invoked while estimating the rows of a range, but it can't use adaptive hash index during the estimation, so adding a condition before invoking btr_search_info_update() during the estimation, the problem may be fixed.

The code in btr_cur_search_to_nth_level():
    if (btr_search_enabled && !index->disable_ahi) {
        btr_search_info_update(cursor);
    }

May be changed to:
    if (!estimate && btr_search_enabled && !index->disable_ahi) {
      btr_search_info_update(cursor);
    }

HI Mr. cao,

Thank you so much for your bug report.

However, this is not a bug. This could be a feature request or performance improvement suggestion.

So, if you would change the severity of this report to one of the two, we might consider it. Do note that 8.0 is now in the maintenance mode, so it will not receive new features or performance enhancements.

If you change severity of this report, as described, send us also the output from the EXPLAIN statements for each of these distinct queries, so that we can make a decision.

We are waiting on your feedback.

HI Mr. cao,

We have analysed further your report and concluded that it is a valid performance enhancement request.

This report is now a fully verified performance request.

Hi, MySQL Verification Team

According to your second reply, do I need to provide the output of EXPLAIN statments for each of the queries mentioned in the Step 4?

Hi Mr. cao,

Yes, that would be welcome.

explain of SQLs for step 4:
- SQL 1(prefix of composite primary key): SELECT * FROM t_ahi WHERE i1 >= 2 AND i1 <= 10
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref    | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+-----------------------+
| 1  | SIMPLE      | t_ahi | <null>     | range | idx_i1_i2_s1_s2 | idx_i1_i2_s1_s2 | 4       | <null> | 9    | 100.0    | Using index condition |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+-----------------------+

- SQL 2(prefix of composite primary key): SELECT * FROM t_ahi WHERE i1 > 2 AND i1 < 10
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref    | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+-----------------------+
| 1  | SIMPLE      | t_ahi | <null>     | range | idx_i1_i2_s1_s2 | idx_i1_i2_s1_s2 | 4       | <null> | 1    | 100.0    | Using index condition |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+-----------------------+

- SQL 3(prefix of unique index): SELECT i1, i2, s1 FROM t_ahi WHERE s1 >= '10' AND s1 <= '20'
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+--------------------------+
| 1  | SIMPLE      | t_ahi | <null>     | index | idx_i1_i2_s1_s2 | idx_i1_i2_s1_s2 | 204     | <null> | 27   | 11.11    | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+--------------------------+

- SQL 4(prefix of unique index): SELECT i1, i2, s1 FROM t_ahi WHERE s1 > '10' AND s1 < '20'
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key             | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+--------------------------+
| 1  | SIMPLE      | t_ahi | <null>     | index | idx_i1_i2_s1_s2 | idx_i1_i2_s1_s2 | 204     | <null> | 27   | 11.11    | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+--------+------+----------+--------------------------+

- SQL 5(whole of non-unique index): SELECT i1, i2, i3 FROM t_ahi WHERE i3 = 20
+----+-------------+-------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref    | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+------+----------+-------------+
| 1  | SIMPLE      | t_ahi | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 27   | 10.0     | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+------+----------+-------------+

Thank you, Mr. cao.