php - 比较年份，用substr截取前4位快，还是date('Y')快?

Question

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' ";
    }

Answer 1

Whether you use substr to intercept the first 4 digits or date('Y'), you are just executing PHP. Ignore the time difference in PHP. The final SQL will be the same and will not affect the database query. efficiency.

You should post the sql statement that needs to be executed so that everyone can see if it can be optimized

Answer 2

Have the index and table structure been optimized? If not, optimizing it first will improve the speed a lot. Otherwise, consider splitting the table or something.

Answer 3

I think you should try directly writing the year search to see if there is any performance improvement. In this case, just pass the value you want directly from the front desk

Answer 4

Not very familiar with PHP, but if the database has a large amount of data, you should check the query statement to create an index for optimization.

Answer 5

Post the sql statement and add an index!

Answer 6

There is no change in the execution efficiency of these two methods. PHP just splices sql statements together. You should find a way to optimize from the database or sql statements;