optimize query with date type field in mysql

Question

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, 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, reading a little note that 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?

Gordon Linoff · Accepted Answer · 2019-07-09 21:33:57Z

5

I would suggest writing the query as:

select sum(i.amount) as total
from incomes i
where i.date >= '2019-07-01' and
      i.date < '2019-08-01' and
      i.deleted_at is null;

This query can take advantage of an index on incomes(deleted_at, date, amount):

create index idx_incomes_deleted_at_date_amount on incomes(deleted_at, date, amount)

edited Jul 9, 2019 at 21:33

answered Jul 9, 2019 at 20:04

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FeRcHo Over a year ago

thanks for answering, I do not understand this part well: incomes (deleted_at, date, amount) means that I must create an index composed of these 3 columns? or for this case based on the query of your answer, an index for the date column is sufficient

Rick James Over a year ago

And the change to the WHERE makes it 'sargable'.

Collectives™ on Stack Overflow

optimize query with date type field in mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related