Description:
After upgrading from MySQL 8.0.x to MySQL 8.4.x LTS, a serious optimizer issue occurs:
When querying a VARCHAR(N) column with a WHERE ... IN () clause that mixes valid length values and over-length values, the optimizer ignores the existing UNIQUE index and forces a full table scan.
However, the single equality query (both valid length and over-length constant) can use the index normally.
This regression bug causes performance degradation for business queries that cannot be modified.
How to repeat:
Step 1: Create Test Table
CREATE TABLE t_tttt (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`userid` varchar(32) NOT NULL COMMENT 'USERID',
PRIMARY KEY (`id`),
UNIQUE KEY `index_userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Step 2: Insert Test Data
insert into t_tttt(`userid`)
SELECT replace(UUID(),'-','') from mysql.help_topic limit 500;
Step 3: Execute Test Queries
-- Query 1: IN clause (1 valid 32-char value + 1 over-length value) → BUG OCCURS
explain analyze select * from t_tttt where userid in ('75a1e4f73d6c11f18a80005056a6a695','75a1e4f73d6c11f18a80005056a6a695__2')\G
-- Query 2: Single equality (valid value) → NORMAL (uses index)
explain analyze select * from t_tttt where userid ='75a1e4f73d6c11f18a80005056a6a695'\G
-- Query 3: Single equality (over-length value) → NORMAL (uses index)
explain analyze select * from t_tttt where userid ='553f59902c3c425896d572d189c5d5ec_delete'\G
Actual Result
Query 1 (IN clause):
Optimizer performs full table scan
Ignores index_userid UNIQUE index
Execution plan: ALL type, key = NULL
Query 2 / Query 3 (equality):
Optimizer uses index_userid correctly
Execution plan: ref/range type, key = index_userid
Normal index lookup, no full scan
Description: After upgrading from MySQL 8.0.x to MySQL 8.4.x LTS, a serious optimizer issue occurs: When querying a VARCHAR(N) column with a WHERE ... IN () clause that mixes valid length values and over-length values, the optimizer ignores the existing UNIQUE index and forces a full table scan. However, the single equality query (both valid length and over-length constant) can use the index normally. This regression bug causes performance degradation for business queries that cannot be modified. How to repeat: Step 1: Create Test Table CREATE TABLE t_tttt ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', `userid` varchar(32) NOT NULL COMMENT 'USERID', PRIMARY KEY (`id`), UNIQUE KEY `index_userid` (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Step 2: Insert Test Data insert into t_tttt(`userid`) SELECT replace(UUID(),'-','') from mysql.help_topic limit 500; Step 3: Execute Test Queries -- Query 1: IN clause (1 valid 32-char value + 1 over-length value) → BUG OCCURS explain analyze select * from t_tttt where userid in ('75a1e4f73d6c11f18a80005056a6a695','75a1e4f73d6c11f18a80005056a6a695__2')\G -- Query 2: Single equality (valid value) → NORMAL (uses index) explain analyze select * from t_tttt where userid ='75a1e4f73d6c11f18a80005056a6a695'\G -- Query 3: Single equality (over-length value) → NORMAL (uses index) explain analyze select * from t_tttt where userid ='553f59902c3c425896d572d189c5d5ec_delete'\G Actual Result Query 1 (IN clause): Optimizer performs full table scan Ignores index_userid UNIQUE index Execution plan: ALL type, key = NULL Query 2 / Query 3 (equality): Optimizer uses index_userid correctly Execution plan: ref/range type, key = index_userid Normal index lookup, no full scan