2

I've found posts of = vs IN for single value (link and link), but I'm asking actually for multiple values. Which is likely to perform better:

# Query 1:
SELECT * FROM tbl WHERE id = 50 OR id = 51 OR id = 52

# Query 2:
SELECT * FROM tbl WHERE id IN (50, 51, 52)

# Query 3:
SELECT * FROM tbl WHERE id >= 50 AND id <= 52

Note: id is primary and indexed, obviously.

evilReiko
  • 273
  • 2
  • 5
  • 11

1 Answers1

1

I found a similar post here.

I've done some benchmark on all 3 queries on a table with 500,000 records with index on id. I fetched 10,000 rows. I did the same tests on MyISAM and Innodb, all same results. I made sure that for each query I used different set of numbers, so the query is not cached by MySQL. I did the tests multiple times, and I got similar numbers every time.

Benchmark results (average):

  • Query 1, fetch by = OR =: 3.700 seconds
  • Query 2, fetch by IN: 0.037 seconds
  • Query 3, fetch by >= && <=: 0.005 seconds
  • Query 4, fetch by between: same as >= && <=
evilReiko
  • 273
  • 2
  • 5
  • 11