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
  • 1
    Was your Query-1 with OR? Or separate SELECTs? Was the cache warm? – Rick James May 30 '18 at 21:36
  • @RickJames Query1 with OR. I'm not sure how to disable cache, but I used different ids set for each test. – evilReiko May 31 '18 at 05:48
  • 1
    SELECT SQL_NO_CACHE ... is a handy way to avoid the Query Cache when timing. But that is not what I meant to ask... Right after a restart, data is sitting only on disk. The first query will be slow due to I/O. To avoid this false timing, run the select twice and take the second timing. (A third run should be the same speed as the second.) – Rick James May 31 '18 at 16:18
  • What version were these tests performed in? – ypercubeᵀᴹ Sep 19 '19 at 16:15