1

I'm cleaning up someone else's code. In going through the very complex system which generates very complex SQL queries, I see a lot of usage of the IN() WHERE clause:

SELECT * FROM table
 WHERE field1 IN( 1, 2, 3, 4, 5 )
   AND type IN( 1, 2, 3 )

... and so on. Is it better for performance to assemble a series of field = 1 OR field = 2 OR... like this:

SELECT * FROM table
 WHERE ( field1 = 1 OR field1 = 2 OR field1 = 3 OR field1 = 4 OR field1 = 5 )
   AND ( type = 1 OR type = 2 OR type = 3 )

or is it better to use IN()? Even for a single value like type IN(1)?

jhqwerty99
  • 128
  • 1
  • 2
  • 11
  • Check `EXPLAIN` for both. "which generates very complex SQL queries" --- do you feel confident to change them actually? – zerkms Jan 07 '14 at 23:02
  • @zerkms: Using EXPLAIN doesn't show any difference. Turning on profiling in phpMyAdmin generates wildly different results. – jhqwerty99 Jan 07 '14 at 23:15

2 Answers2

3

The answer varies by version of MySQL. The developers have worked on improving the optimizer with each major release, so the efficiency of OR versus IN could be quite different in MySQL 5.6 or later. It also depends on how many terms are we talking about, and index selectivity and so on.

The best advice is to benchmark and profile your real queries, on your installed MySQL version, using your real data.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Better to use IN for readability and maintenance. Performance will be the same, if not better. In fact you will probably get a performance boost by the server not having to parse a ridiculous list of or..or..or..or nonsense. Remember the server doesn't just run the SQL, it has to parse it and make sense of it. More readable for us humans is more readable for the server too and thus parses faster.

developerwjk
  • 8,619
  • 2
  • 17
  • 33
  • 1
    "More readable for us humans is more readable for the server too" --- that's never the case. – zerkms Jan 07 '14 at 23:16
  • @developerwjk: Good point on readability. But since I'm assembling the SQL query string in a loop, readability won't matter too much. – jhqwerty99 Jan 07 '14 at 23:18
  • @zerkms, Never is a pretty strong word here. Because in this case it unquestionably is easier for both. It might not always hold true, but here it does. – developerwjk Jan 07 '14 at 23:26
  • 1
    @developerwjk: here it does and only because it contains **less tokens**, not because it's more readable for user. There is no connection between these 2 facts. Less tokens doesn't mean it's readable for user, and readable for user doesn't mean it has less tokens. – zerkms Jan 07 '14 at 23:28