Using IN clause vs. multiple SELECTs

Question

I was wondering which of these would be faster (performance-wise) to query (on MySQL 5.x CentOS 5.x if this matters):

SELECT * FROM table_name WHERE id=1;
SELECT * FROM table_name WHERE id=2;
.
.
.
SELECT * FROM table_name WHERE id=50;

or...

SELECT * FROM table_name WHERE id IN (1,2,...,50);

I have around 50 ids to query for. I know usually DB connections are expensive, but I've seen the IN clause isn't so fast either [sometimes].

Almost certainly the same: BUT this would be faster SELECT * FROM table_name WHERE id BETWEEN 1 AND 50 — Mitch Wheat, May 13 '12 at 13:11
Yeah, that probably would, but I don't have my IDs in consecutive order, it was merely an example. — Eduard Luca, May 13 '12 at 13:12
please don't give unrepresentative examples: otherwise you might get unrepresentative answers. — Mitch Wheat, May 13 '12 at 13:14

score 5 · Accepted Answer · edited May 23 '17 at 10:31

5

I'm pretty sure the second option gives you the best performance; one query, one result. You have to start looking for > 100 items before it may become an issue.

See also the accepted answer from here: MySQL "IN" operator performance on (large?) number of values

edited May 23 '17 at 10:31

Community

1
1

answered May 13 '12 at 13:12

Ja͢ck

170,779
38
263
309

score 3 · Answer 2 · answered May 13 '12 at 13:12

3

IMHO you should try it and measure response time: IN should give you better performances...
Anyway if your ids are sequential you could try

SELECT * FROM table_name WHERE id BETWEEN 1 AND 50

answered May 13 '12 at 13:12

Marco

56,740
14
129
152

score 0 · Answer 3 · edited May 23 '17 at 12:25

0

Here is another post where the discuss the performance of using OR vs IN. IN vs OR in the SQL WHERE Clause

You suggested using multiple queries, but using OR would also work.

edited May 23 '17 at 12:25

Community

1
1

answered May 13 '12 at 13:14

marteljn

6,446
3
30
43

score 0 · Answer 4 · answered May 13 '12 at 13:30

2nd will be faster because resources are consumed when query gets interpreted and during php communication with mysql for sending query and waiting for result , if your data is sequential you can also do just

   SELECT * FROM table_name WHERE id <= 50;

score 0 · Answer 5 · answered May 30 '12 at 15:21

I was researching this after experimenting with 3000+ values in an IN clause. It turned out to be multitudes faster than individual SELECTs since the column referenced in the IN was not keyed. My guess is that in my case it only needed to build a temporary index for that column once instead of 3000 separate times.

Using IN clause vs. multiple SELECTs

5 Answers5