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.

Don't know the answer to your question, but if your `IN` clause is very large, maybe using a `JOIN` with some selection table would be better? — Xophmeister, Dec 13 '11 at 15:17
Why not try a benchmark yourself if you have all in place (the means and the data) .. ? — darma, Dec 13 '11 at 15:20
Maybe just a bit more context would help, including how you assemble the "IN" statement in the first place. I've used table value parameters to do stuff like this in SQL Server, but I'm not sure that's an option here. — D. Lambert, Dec 13 '11 at 15:21
possible duplicate of [Performance of MYSQL "IN"](http://stackoverflow.com/questions/1537675/performance-of-mysql-in) — ajreal, Dec 13 '11 at 15:38

score 3 · Answer 1 · edited May 23 '17 at 12:11

3

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

edited May 23 '17 at 12:11

Community

1
1

answered Dec 13 '11 at 15:20

PiTheNumber

22,828
17
107
180

If he has thousands of values to put in the `IN` clause he shouldn't do any `OR` or `IN` selects. – aF. Dec 13 '11 at 15:21
He should not. Behind the link are answer saying that. There is also some talk about performance. I think it is related to this question. – PiTheNumber Dec 13 '11 at 15:28

score 2 · Accepted Answer · answered Dec 13 '11 at 15:20

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

answered Dec 13 '11 at 15:20

aF.

64,980
43
135
198

So you're saying i add my id set in a new table and join on it? – applechief Dec 13 '11 at 15:34
@chaft, it's the most sane way :P – aF. Dec 13 '11 at 15:35
Hi will this lead to better/worse performance? Because I have heard that JOINs will lead to poor performance... Thanks! – ch271828n Apr 24 '20 at 04:14

Performance of mysql WHERE ... IN

2 Answers2