AGE [1, 2, 3] vs. AGE BETWEEN 1 AND 3

Question

I have an InnoDB table and an index on age column just like this

   CREATE TABLE Person (

      ....
      age int(11) not null;
      key (age);
      ....
   ) engine=InnoDB;

I just want to know the real things behind these queries:

SELECT * FROM Person WHERE age IN [1, 2, 3];

and

SELECT * FROM Person WHERE age BETWEEN 1 AND 3;

As what I've learnt, the first one MySQL will use the index on column age while the second one it can't use. Is it? Which is better for performance?

Answer 1

Both queries will use an index.

Query A will translated to:

select * from person where age = 1 or age = 2 or age 3;

Query B will translate to

select * from person where age >= 1 and age <= 3;

So query A will do 3 tests using OR.
Query B will do 2 tests using AND.

Query B is faster.

In general, queries using AND are faster than queries using OR.
Also Query B is doing fewer tests and because it's testing a range it can more easily exclude results that it does not want.