Description:
SELECT query, that should use primary index, deteriorates to a full table scan when filtering part of WHERE refers in IN to 2 or more values, where at at least one of them is longer than defined length of the column.
How to repeat:
MySQL 8.4.5 with default settings, installed from Oracle's MySQL APT repository.
CREATE TABLE t ( c varchar(16) NOT NULL , PRIMARY KEY (c) );
some test data:
INSERT INTO t(c) VALUES('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u');
INSERT IGNORE INTO t(c) SELECT SUBSTR(MD5(CONCAT(t1.c, ROW_NUMBER() OVER ( PARTITION BY 'x')) ),1,16) FROM t t1 JOIN t t2 ORDER BY t1.c;
INSERT IGNORE INTO t(c) SELECT SUBSTR(MD5(CONCAT(t1.c, ROW_NUMBER() OVER ( PARTITION BY 'x')) ),1,16) FROM t t1 JOIN t t2 ORDER BY t1.c;
this query works as expected, there's no full table scan:
mysql> EXPLAIN SELECT * FROM t WHERE c IN ( '012345678901234567');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
but adding one more value into the IN list, even short one - leads to full table scan:
mysql> explain SELECT * FROM t WHERE c IN ( '012345678901234567','a' );
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | t | NULL | index | PRIMARY | PRIMARY | 66 | NULL | 223855 | 20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
Suggested fix:
in the ideal world - query planner should make use of the index even if some of the values in IN list have length longer than maximum allowed column length; for them there'll be no matching row.
thank you!
Description: SELECT query, that should use primary index, deteriorates to a full table scan when filtering part of WHERE refers in IN to 2 or more values, where at at least one of them is longer than defined length of the column. How to repeat: MySQL 8.4.5 with default settings, installed from Oracle's MySQL APT repository. CREATE TABLE t ( c varchar(16) NOT NULL , PRIMARY KEY (c) ); some test data: INSERT INTO t(c) VALUES('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'); INSERT IGNORE INTO t(c) SELECT SUBSTR(MD5(CONCAT(t1.c, ROW_NUMBER() OVER ( PARTITION BY 'x')) ),1,16) FROM t t1 JOIN t t2 ORDER BY t1.c; INSERT IGNORE INTO t(c) SELECT SUBSTR(MD5(CONCAT(t1.c, ROW_NUMBER() OVER ( PARTITION BY 'x')) ),1,16) FROM t t1 JOIN t t2 ORDER BY t1.c; this query works as expected, there's no full table scan: mysql> EXPLAIN SELECT * FROM t WHERE c IN ( '012345678901234567'); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ but adding one more value into the IN list, even short one - leads to full table scan: mysql> explain SELECT * FROM t WHERE c IN ( '012345678901234567','a' ); +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | index | PRIMARY | PRIMARY | 66 | NULL | 223855 | 20.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+ Suggested fix: in the ideal world - query planner should make use of the index even if some of the values in IN list have length longer than maximum allowed column length; for them there'll be no matching row. thank you!