What's mysql's "BETWEEN" performance over..?

Question

Is there any better performance when querying in (particularly) mysql of the following:

SELECT * FROM `table` WHERE `unix_date` BETWEEN 1291736700 AND 1291737300 

over:

SELECT * FROM `table` WHERE `unix_date` >= 1291736700 AND `unix_date` <= 1291737300 

or BETWEEN syntax is just being substituted with the second sql?

Answer 1

As I recall, there's no difference. But see for yourself if:

explain plan for  SELECT * FROM `table` WHERE `unix_date` BETWEEN 1291736700 AND 1291737300 

and:

explain plan for SELECT * FROM `table` WHERE `unix_date` >= 1291736700 AND `unix_date` <= 1291737300 

produce the same plans.

Answer 2

From the documentation:

• expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 11.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

So it really is just syntax sugar.