DIFFERENCE between AND <= & >=

[OAFC_Rob]

What is the difference between the two statement apart from  the fact I'm using between and greater than. If there any reason to why one would be prefered, part from terms of length of coding?

 

SELECT * FROM table WHERE date >= '2012-01-01' AND <= '2012-04-01'

SELECT * FROM table WHERE date BETWEEN '2012-01-01' AND '2012-04-01'

[AyKay47]

performance wise I really do not know if there is a difference.. I would imagine that using the BETWEEN function would be a bit quicker.. however I do not know the infrastructure of the function so its hard to say.. BETWEEN is the preferred method simply because it is there for this purpose and decreases any potential of syntax mistakes etc.. perhaps someone else can shed some more light on the performance side of this question.

[Buddski]

I just ran a very small test, 10000 of each query calling a result set of 10. I wouldnt say its a benchmark test but it does show what its like in a development env.

 

$query = "SELECT * FROM `chas` WHERE `date` >= '2011-10-01' AND  `date` <= '2011-10-30'"; // 6.6296169757843 seconds
$query2 = "SELECT * FROM `chas` WHERE `date` BETWEEN '2011-10-01' AND '2012-10-30'";  // 5.5761868953705

[AyKay47]

  Quote

I just ran a very small test, 10000 of each query calling a result set of 10. I wouldnt say its a benchmark test but it does show what its like in a development env.

 

$query = "SELECT * FROM `chas` WHERE `date` >= '2011-10-01' AND  `date` <= '2011-10-30'"; // 6.6296169757843 seconds
$query2 = "SELECT * FROM `chas` WHERE `date` BETWEEN '2011-10-01' AND '2012-10-30'";  // 5.5761868953705

nice.. it appears the the BETWEEN function is an optimized way of writing the lengthened code above,no surprise really, and should always be used.. thank you for the tests/results...

[Buddski]

Any excuse to write something

[AyKay47]

  Quote

Any excuse to write something

lol.. that one made me laugh.

[OAFC_Rob]

haha! I thought that Between would be quicker but there doesn't seem to be much documentation to back one or the other    I guess it comes down to personal preference and waht you're actually using it for

[AyKay47]

as i said before, the BETWEEN function is there for a reason, it is optimized.. use it.. embrace it.. 

[OAFC_Rob]

I already am 

[PFMaBiSmAd]

Due to mysql's query cache, any timing differences seen between repeatedly executing two different queries are in the time taken to send the query statement from php to the mysql server and the parsing of the query statement on the mysql server, because after the first time each of the two different queries is executed, the result set for each specific query statement is held in the query cache. The only thing you can determine from this is that a shorter query statement takes less time to send and to parse.

 

You would need either disable the query cache or cause a change to the table data between each query so that the cache is flushed to actually see any timing difference due to the query being used.

[Buddski]

FYI:

  Quote

query_cache_type OFF

query_cache_size 0

[fenway]

Or, use SQL_NO_CACHE.