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

MysqlBetween

Mysql Problem Overview

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?

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

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.

Solution 3 - Mysql

BETWEEN shows clearer intent and reads better (something SQL set out to do).

Solution 4 - Mysql

The three following statement will produce the same result:

  1. i BETWEEN x AND y
  2. x <= i AND i <= Y
  3. i >= x AND i <= Y

But in configuration 3 mysql might (depending on your indexes) use different indexes: order matters and you should be testing with EXPLAIN query to see the difference

Categories
Recommended Mysql Solutions
Recommended Between Solutions

Previous Solution:

What does it really mean to target a framework, and how do I maximize compatibility?

C#.NetAssembliesCompatibilityBackwards Compatibility

Next Solution:

Email from PHP has broken Subject header encoding

PhpEncodingMimeEmail Headers

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDaveelView Question on Stackoverflow
Solution 1 - MysqltpdiView Answer on Stackoverflow
Solution 2 - MysqlPaolo BergantinoView Answer on Stackoverflow
Solution 3 - MysqlalexView Answer on Stackoverflow
Solution 4 - MysqlSoufiane GhzalView Answer on Stackoverflow