Performance of mysql WHERE ... IN

Question

I have mysql queries with a WHERE IN statement.

SELECT * FROM table1 WHERE id IN (1, 2, 15, 17, 150 ....)

How will it perform with hundreds of ids in the IN clause? is it designed to work with many arguments? (my table will have hundreds of thousands of rows and id is the primary field)

is there a better way to do it?

EDIT: I am getting the Ids from the result set of a search server query. So not from the database. I guess a join statement wouldn't work.

Answer 1

I am not sure how WHERE ... IN performes but for me it sounds like a JOIN or maybe a subselect would be the better choice here.

See also: MYSQL OR vs IN performance and http://www.slideshare.net/techdude/how-to-kill-mysql-performance

Answer 2

You should put the IN clause "arguments" into table2 for instance.

Afterwords you make this:

SELECT t1.* FROM table1 t1
INNER JOIN table2 t2 ON t1.Id = t2.Id