MySQL SELECT IN with single value

Question

In MySQL is it very bad to do this:

SELECT ... WHERE foo IN ('bar')

instead of this...

SELECT ... WHERE foo = 'bar'

...given that this dynamically generated WHERE clause is also likely to be

SELECT ... WHERE foo IN ('bar', 'baz', 'buz')

@mcabral: I agree. @Quassnoi's answer is the most applicable to this situation — ChrisLively
– ChrisLively, Commented Mar 29, 2011 at 1:40
@mcabral - out of curiosity how did you find that dupe given that 'in' is a stop word? — Rob Agar
– Rob Agar, Commented Mar 29, 2011 at 1:51
ye olde trick! google for 'site:stackoverflow.com mysql in keyword' — mcabral
– mcabral, Commented Mar 29, 2011 at 2:17

therealsix · Accepted Answer · 2011-03-29 01:10:29Z

2

My understanding is that it would be converted into the same thing before execution. Either way, I would file this under 'really really low concern'.

answered Mar 29, 2011 at 1:10

therealsix

6542 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ChrisLively · Accepted Answer · 2011-03-29 01:08:39Z

1

I'm not sure that I would worry about this.

However, because it's dynamically generated you could always do a test to see how many values you have before building the where clause.

answered Mar 29, 2011 at 1:08

ChrisLively

88.3k27 gold badges174 silver badges249 bronze badges

2 Comments

Rob Agar Over a year ago

I'm not sure either, but if there's no significant performance hit I'd rather keep the generation code clean + concise

ChrisLively Over a year ago

@Rob Agar: This falls under the idea of Preoptimization. If while profiling the app you notice a performance hit, then change it. Otherwise just go with what you are more comfortable with.

paxdiablo · Accepted Answer · 2011-03-29 01:37:10Z

0

It is much better to use, for SQL query optimization and best practices:

WHERE foo = 'bar' OR foo = 'baz'

than:

WHERE foo IN ('bar', 'baz')

Regards

edited Mar 29, 2011 at 1:37

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

answered Mar 29, 2011 at 1:22

Crimsonland

2,1943 gold badges25 silver badges43 bronze badges

3 Comments

Alp Over a year ago

that should actually be foo = 'bar' OR foo = 'baz', but i don't think that changes something in performance at all

mcabral Over a year ago

do you have a source for this? i don't mean to be rude but, i've been reading they are quite the same.

Crimsonland Over a year ago

For small IN clauses there isn't an issue. However, if you have very large IN clauses, especially ones that vary with each execution, there is significant issues with multipage allocators and the procedure cache.

Collectives™ on Stack Overflow

MySQL SELECT IN with single value

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related