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

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

   CREATE TABLE Person (

      ....
      age int(11) not null;
      key (age);
      ....
   ) eng开发者_如何学运维ine=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?

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.

I don't have MySQL server installed on my netbook right now but sticking a EXPLAIN statement might give you the answer you are looking for:

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

You may find the answers to this StackOverflow question helpful:

MYSQL OR vs IN [vs BETWEEN] performance

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

is equivalent to

SELECT * FROM Person WHERE age = 1 OR age = 2 OR age 3

whereas

SELECT * FROM Person WHERE age BETWEEN 1 AND 3;

is equivalent to

SELECT * FROM Person WHERE age >= 1 AND age <= 3

use EXPLAIN to see the actual execution plan for the different forms of the queries. i would be surprised too see mysql not using an index with BETWEEN.

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

should give you an exact answer for your exact query

SELECT * FROM Person WHERE age >= 1 AND age <= 3

is faster than SELECT * FROM Person WHERE age BETWEEN 1 AND 3; and EXPLAIN SELECT * FROM Person WHERE age IN [1,2,3];

you can try with 1 million record of mysql db.