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

Question

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 1

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.

Answer 2

For most modern RDBMS, including MySQL, there will be no practical performance difference between the two.

Using BETWEEN gives you much nicer code, so use it wherever you can.