How to improve performances for SQL queries with IN and thousands of values

Today I’ve had to optimize an SQL query with this structure:

1
2
3

SELECT ...
FROM ... tbl
WHERE tbl.id IN (435, 3456, 77432, ...) -- thousands of values here

A very simple query, but having thousands of values after IN causes very bad performances.

Some Internet searches have allowed me to find an equivalent query to do that, but faster:

1
2
3
4
5
6

CREATE TEMPORARY TABLE tmp (id INT NOT NULL, PRIMARY KEY (id));
INSERT INTO tmp (id) VALUES (435), (3456), (77432), .....  -- thousands of values here
SELECT ...
FROM ... tbl
INNER JOIN tmp ON tmp.id = tbl.id;
DROP TABLE tmp; -- optional, it depends on following requests

I’ve had to create a temporary table and insert into it all IDs and INNER JOIN it. Optionally, you can drop the table after the request, but the table lives only during the session anyway.