MySQL Query IN() Clause Slow on Indexed Column
You are accessing 420 rows by primary key which will probably lead to an index access path. This could access 2 index pages and one data page per key. If these are in cache, the query should run fast. If not, every page access that goes to disk will incur the usual disk latency. If we assume 5ms disk latency and 80% cache hits, we arrive at 420*3*0.2*5ms=1.2 seconds which is on the order of what you're seeing.
The problem is that IN
is basically treated as a bunch of OR
s (e.g.
col IN (1,2,3)
is
col = 1 OR col = 2 OR col = 3
This is a LOT slower than a join.
What you should do is to generate the SQL code which creates the temporary table, populates it with the values in the "IN" clause, and then join with that temp table
CREATE TEMPORARY TABLE numbers (n INT)
Then in a loop, add
INSERT numbers VALUES ($next_number)
Then at the end
SELECT * FROM numbers, Recipe_Data
WHERE numbers.n = RHD_No