Description:
In https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html, it says that a tight index scan can be used when "The GROUP BY does not begin with the first part of the key, but there is a condition that provides a constant for that part". But when a list of constants satisfies this condition, then a temporary table is used, which is not performant.
How to repeat:
CREATE TABLE `test_table` (
`a` bigint NOT NULL,
`b` bigint NOT NULL,
`c` bigint NOT NULL,
PRIMARY KEY (`a`),
KEY `bc_idx` (`b`,`c`)
)
explain select c, count(*) from test_table where b IN (1, 2, 3) group by c;
(note that this uses a temporary table, which is not performant)
explain select c, count(*) from test_table where b = 1 group by c;
(this one can use just the index)
explain select c from test_table where b in (1, 2, 3);
(this is the closest available workaround (still have to sum rows after the query), which avoids using a temporary table)
Suggested fix:
"The GROUP BY does not begin with the first part of the key, but there is a condition that provides a constant for that part" (https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html)
should also apply to a list of constants.
