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?

Use [`explain`](http://dev.mysql.com/doc/refman/5.0/en/explain.html) to find out exactly how the database is processing these in your specific case. — Mat, Apr 24 '11 at 09:25
also see [sql: BETWEEN v1 AND v2](http://stackoverflow.com/q/1572840/112968) — knittl, Apr 24 '11 at 09:26

Johan · Accepted Answer · 2019-02-12T11:34:32.547

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.

score 2 · Answer 2 · answered Apr 24 '11 at 09:26

2

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;

answered Apr 24 '11 at 09:26

Alix Axel

151,645
95
393
500

score 1 · Answer 3 · edited May 23 '17 at 11:52

1

You may find the answers to this StackOverflow question helpful:

MYSQL OR vs IN [vs BETWEEN] performance

edited May 23 '17 at 11:52

Community

1
1

answered Apr 24 '11 at 09:31

Tim

1,344
1
11
12

score 0 · Answer 4 · answered Apr 17 '15 at 14:36

0

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.

answered Apr 17 '15 at 14:36

Maneesh Singh

555
2
12

knittl · Answer 5 · 2011-04-24T09:34:32.423

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

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

5 Answers5

Linked