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.

4
  • 2
    These are also equivalent with WHERE id BETWEEN 50 AND 52 Commented May 19, 2018 at 5:55
  • Q1 and Q2 are eqs. Q3 and Q by Lennart are eqs. But this pairs of queries are NOT equivalent formally (for example, nothing in the text above prevent id to be floating-point number).
    – Akina
    Commented May 19, 2018 at 18:22
  • @Lennart I'm asking about performance, would one perform better than others if I used many multiple ids?
    – evilReiko
    Commented May 22, 2018 at 10:37
  • I don't think IN turns into OR, nor vice versa: See here
    – Rick James
    Commented May 30, 2018 at 21:34

1 Answer 1

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 >= && <=
4
  • 1
    Was your Query-1 with OR? Or separate SELECTs? Was the cache warm?
    – Rick James
    Commented May 30, 2018 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
    Commented May 31, 2018 at 5: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
    Commented May 31, 2018 at 16:18
  • What version were these tests performed in? Commented Sep 19, 2019 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.