PHP和MYSQL优化的方式按日期间隔选择

Question

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

Answer 1

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 ?";