CODEWITHSUNDEEP
phpmysqloptimization
de3
de3

Reputation: 2000

PHP and MYSQL optimized way to select by date interval

Which is the fastest way?

a) $query="SELECT id FROM example WHERE date_sent>='".$date1."' AND date_sent<='".$date2."'";

or

b) $query="SELECT id FROM example WHERE date_sent BETWEEN '".$date1."' AND '".$date2."'";

Also, what is faster and better if I have a date in the format '02/12/2010' ?

a) to transform in php the date to a format like '2010-12-02' and compare it directly 'WHERE date_sent>=2010-12-02'

or

b) to use the mysql function: 'WHERE date>=str_to_date('02/12/2010','%d/%m/%Y')' ?

Thanks

Upvotes: 2

Views: 550

Answers (2)

Rahly
Rahly

Reputation: 1511

its always faster to convert the date string into whatever native date format the sql server uses. You don't have to run a convert function, nor does the server have to run a conversion.

BETWEEN is generally faster, because its handled by specific code on the backend. Where the <= >= is generalized code and has to be tested for. Also make sure you have proper indexes.

Use PDO too, $query = "SELECT id FROM example WHERE date_sent BETWEEN ? AND ?";

Upvotes: 2

Linus Kleen
Linus Kleen

Reputation: 34632

MySQL manual says:

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.

Also, what is faster and better if I have a date in the format '02/12/2010' ?

Convert that column to a DATETIME type or similar if it's a VARCHAR.

Upvotes: 2

Related Questions