1問題描述:根據始末日期查詢數據,由於資料庫數據量大,用between and很慢,就添加了year,month,day字段,在比較年月日的時候有substr和date兩種方法,兩種都嘗試過每次載入時間都在變化,也沒找到相關的技術文章。理論上是兩種速度都差不多,還是有相對快速的方法?感謝每一個答題者。
2.相關程式碼片段:
//substr
if(substr($startTime,0,4) == substr($endTime,0,4)){
$str .= " L.year = ".substr($startTime,0,4);
if(substr($startTime,4,2) == substr($endTime,4,2)){
$str .= " and L.month = ".substr($startTime,4,2);
if(date('Ynd',strtotime($startTime)) == date('Ynd',strtotime($endTime))){
$str .= " and day = ".date('d',strtotime($startTime));
}else{
$str .= " and log_date between '$startTime' and '$endTime' ";
}
}else{
$str .= " and log_date between '$startTime' and '$endTime' ";
}
}else{
$str .= " L.log_date between $startTime and $endTime";
}
//date
if(date('Y',strtotime($startTime)) == date('Y',strtotime($endTime))){
$str = " where year = ".date('Y',strtotime($startTime));
if(date('Yn',strtotime($startTime)) == date('Yn',strtotime($endTime))){
$str .= " and month = ".date('n',strtotime($startTime));
if(date('Ynd',strtotime($startTime)) == date('Ynd',strtotime($endTime))){
$str .= " and day = ".date('d',strtotime($startTime));
}else{
$str .= " and log_date between '$startTime' and '$endTime' ";
}
}else{
$str .= " and log_date between '$startTime' and '$endTime' ";
}
}else{
$str = " where log_date between '$startTime' and '$endTime' ";
}
给我你的怀抱2017-05-16 13:11:05
不管是用substr截取前4位,還是date('Y'),你都是php的執行而已,php這點時差忽略不記,最終得到的sql是一樣的,並不會影響到資料庫的查詢效率。
你應該要貼出需要執行的sql語句來讓大家看看是否能再最佳化