mysql with date function query running slow

Question

I found something weird while executing query today and i want to know how this happens.

Below is my query:

select sum(price) as total from table_a where testing_date = '2020-06-10' 

this query takes 2-5 seconds while searching for the data. Now i did small change in the query as follow:

select sum(price) as total from table_a where date(testing_date) = '2020-06-10' 

In this case query takes 2-3 minutes. Here testing_date column data in dateTime format for example : 2020-06-01 00:00:00

Here total records size is more than 7 million.

Answer 1

Don't use a function on the column you filter on. This makes the query non-SARGeable, meaning that the database cannot take advantage of an existing index. Basically, you are forcing the database to do the computation on each and every value in the column before the filtering can happen.

If you want to filter on a given day, you can use inequalities with half-open intervals:

where testing_date >= '2020-06-10' and testing_date < '2020-06-11'