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
    – Lennart
    May 19 '18 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
    May 19 '18 at 18:22
  • @Lennart I'm asking about performance, would one perform better than others if I used many multiple ids?
    – evilReiko
    May 22 '18 at 10:37
  • I don't think IN turns into OR, nor vice versa: See here
    – Rick James
    May 30 '18 at 21:34
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
    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 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
    May 31 '18 at 16:18
  • What version were these tests performed in? Sep 19 '19 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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