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 ORs (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