Is there a performance difference between BETWEEN and IN with MySQL or in SQL in general?

sql mysql between
pr1001 picture pr1001 · Jul 22, 2010 · Viewed 20.1k times · Source

I have a set of consecutive rows I want to get based upon their primary key, which is an auto-incrementing integer. Assuming that there are no holes, is there any performance between between:

SELECT * FROM `theTable` WHERE `id` IN (n, ... nk);

and:

SELECT * FROM `theTable` WHERE `id` BETWEEN n AND nk;

Answer

Andomar picture Andomar · Jul 22, 2010

a between b and c is a macro that expands to b <= a and a <= c.

a in (b,c,d) is a macro that expands to a=b or a=c or a=d.

Assuming your n and nk are integer, both should end up meaning the same. The between variant should be much faster because it's only two compares, versus nk - n compares for the in variant.

Related questions

generate days from date range

I would like to run a query like select ... as days where `date` is between '2010-01-20' and '2010-01-24' And return data like: days ---------- 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24

Read More
MySQL "between" clause not inclusive?

If I run a query with a between clause, it seems to exclude the ending value. For example: select * from person where dob between '2011-01-01' and '2011-01-31' This gets all results with dob from …

Read More
Mysql: Selecting values between two columns

I'm trying to select a value between 2 columns. Here is my dataset id from to price 1 0.00 2.00 2.50 2 2.00 3.00 3.00 3 3.00 4.00 4.50 My goal, if I have a value of 2 is to select the line with the ID 1 (between from and to). So here is the …

Read More