Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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):
OR? Or separate SELECTs? Was the cache warm?
May 30, 2018 at 21:36
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.)
May 31, 2018 at 16:18
WHERE id BETWEEN 50 AND 52Q1andQ2are eqs.Q3andQ by Lennartare eqs. But this pairs of queries are NOT equivalent formally (for example, nothing in the text above preventidto be floating-point number).INturns intoOR, nor vice versa: See here