-1

Sorry if this is a repeat. Which query will run faster? Using mySql.

select fieldA from myTable where fieldB IN (2,4,6,8)

vs

select fieldA from myTable where (fieldB=2 OR fieldB=4 OR fieldB=6 OR fieldB=8)

Since the lists are relatively small, for this it probably wouldnt make such a difference between the two queries, but say it was something like 1000 parameters instead of just 4.

chiliNUT
  • 18,989
  • 14
  • 66
  • 106

2 Answers2

1

As was commented: when in doubt, benchmark it.

As an actual answer,

  1. It is highly likely that the SQL engine will optimize the query into whatever works best anyway
  2. If it does not, it is likely that IN will be faster for a large number of parameters, on the grounds that it is a lookup type test (I believe in most cases the engine will internally create a temporary table with the contents of the IN, and then use a binary search type algorithm to avoid having to test against all values in the table).
  3. Benchmark to make sure out.
  4. Use EXPLAIN to see what the engine proposes it will do internally.
zebediah49
  • 7,467
  • 1
  • 33
  • 50
0

If you're using MSSQL right click within the query and press display estimted execution plan. It will give you estimates on different sections of the query. Besides that, if you need a lot of values, it is much easier to use an IN statement.

Consider this individual's answer as well https://stackoverflow.com/a/1220433/1504882

If your values are simple ints then the query will go fast, but chars and strings will take longer because it needs to compare more.

Overall, I doubt you'd see much if any difference in small queries like the one above

Community
  • 1
  • 1
Elias
  • 2,602
  • 5
  • 28
  • 57