I currently have the following query prepared:

  select sum(amount) as total 
  from incomes 
  where (YEAR(date) = '2019' and MONTH(date) = '07') 
     and incomes.deleted_at is null

When reviewing it a bit, I notice that it takes too long to have a lot of data in the table, since it goes through all this. I do not know much about optimizing queries, but I want to start documenting and researching for this case. I have read a little note that states,

"although it is possible to create an index for a date type field, MySQL will not use an index once a column of the WHERE clause is wrapped with a function, in this case YEAR and MONTH."

So is this correct? What steps should I follow to improve its performance? Should I try to restructure my query?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.