php - To compare years, is it faster to use substr to intercept the first 4 digits, or is it faster to use date('Y')?

Question

1 Problem description: Query data based on the beginning and ending dates. Due to the large amount of database data, using between and is very slow, so we added year, month, day fields. There are two methods, substr and date, when comparing year, month and day. I tried both and the loading time changed every time, and I couldn't find any relevant technical articles. In theory, are the two speeds similar, or is there a relatively faster method? Thanks to everyone who answered.
2. Relevant code snippets:

//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;