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
WHEREclause is wrapped with a function, in this case
So is this correct? What steps should I follow to improve its performance? Should I try to restructure my query?