mysql with date function query running slow

user_5177853 0
Viewed 854

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.

date-arithmetic where-clause query-optimization mysql sql
1 Answers

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'
user_106767160
Related