| Bug #105566 | MySQL force index is invalid | ||
|---|---|---|---|
| Submitted: | 14 Nov 2021 15:10 | Modified: | 9 Dec 2021 11:04 |
| Reporter: | lei yue | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Nov 2021 14:05]
MySQL Verification Team
Hi Mr. yue, Thank you for your bug report. However, this is not a bug. FORCE INDEX ..... can be used in all situations where can be used. However, for the query that you have designed, there is no possibility that any index can be used. If a column is of the INT UNSIGNED domain, then a filtering conditions `column_name` > 0, actually, does not do any filtering, bug returns the entire table. Under those conditions, no index usage is considered at all. Simply, the entire table is scanned sequentially. FORCE INDEX should work for filtering condition that return (approximately) one fifth of the rows or less, depending the the row width. Not a bug.
[15 Nov 2021 15:32]
lei yue
hi， thank you for your reply. but I have two questions to confirm: 1. Why is version 5.7 effective? 2. Force index is the hint usage of MySQL, so why isn't the user forced to specify the index executed according to it?
[15 Nov 2021 15:57]
MySQL Verification Team
Because, you can not FORCE INDEX when scanning is the obligatory algorithm for a query like yours. This is sufficiently described in our Manual.
[16 Nov 2021 10:20]
lei yue
hi， Only in the scenario where id>=0, the force index is invalid. The description of the force index of version 8.0 in the manual is not much different from version 5.7 ### id>=0 plan mysql> explain SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`testht` WHERE `id` >= 0 /*key_len*/; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+ | 1 | SIMPLE | testht | NULL | ALL | NULL | NULL | NULL | NULL | 99912 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) ### id>0 plan mysql> explain SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`testht` WHERE `id` > 0 /*key_len*/; +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | testht | NULL | range | PRIMARY,idx1 | PRIMARY | 4 | NULL | 49956 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) ### id>=1 plan mysql> explain SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`testht` WHERE `id` >= 1 /*key_len*/; +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | testht | NULL | range | PRIMARY,idx1 | PRIMARY | 4 | NULL | 49956 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ 1 row in set, 2 warnings (0.01 sec)
[9 Dec 2021 11:04]
lei yue
hi, Should MySQL 8.0 documents be updated? https://dev.mysql.com/doc/refman/8.0/en/index-hints.html: The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.
[9 Dec 2021 13:02]
MySQL Verification Team
Hi, Actually, the algorithms used with USE / FORCE INDEX are far more complicated than what is described in the Documentation. Full description of all the variants would take lots of pages and would be quite perplexing. Describing all the variants that could occur is reserved only for our internal documentation.
Description: 1. pt-osc error # pt-online-schema-change ... --check-plan ... ... 2021-11-14T22:35:18 Cannot determine the key_len of the chunk index because MySQL chose no index instead of the PRIMARY index for the first lower boundary statement. See --[no]check-plan in the documentation for more information. 2. MySQL 8.0 force index invalid ### MySQL 8.0.27 plan mysql> explain SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`testht` FORCE INDEX (`PRIMARY`) WHERE `id` >= 0 /*key_len*/; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+ | 1 | SIMPLE | testht | NULL | ALL | NULL | NULL | NULL | NULL | 98505 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+ 1 row in set, 2 warnings (0.01 sec) ### MySQL 5.7.36 plan mysql> explain SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`.`testht` FORCE INDEX (`PRIMARY`) WHERE `id` >= 0 /*key_len*/; +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | testht | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 40959 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+ 1 row in set, 2 warnings (0.01 sec) How to repeat: ### table mysql> show create table testht\G *************************** 1. row *************************** Table: testht Create Table: CREATE TABLE `testht` ( `id` int unsigned NOT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `company` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `idx1` (`id`,`company`(20)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin 1 row in set (0.00 sec) ### rows mysql> select count(*) from testht; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.05 sec)