There is such a request:

SELECT sum(money) FROM `accounts` WHERE date >= (CURDATE()-1) AND date < CURDATE() AND type IN (101,225)

You need to change the type, perform several queries, for example:

 SELECT sum(money) FROM `accounts` WHERE date >= (CURDATE()-1) AND date < CURDATE() AND type IN (353,448) ...

How can I optimize such a query?

  • And what do you mean by optimization? Want to execute in one query? - cheops
  • @cheops yes, I know that you can just through UNION ALL, but I would also like to know the most optimal ways. - XenK
  • This range - there is already difficult to do something else. - cheops
  • 2
    When it comes to optimization, you should start by looking at the execution plans. If the plan does not suit something - look for solutions. And the optimizer any "cunning" ways can easily lead to one plan. And the plan is highly dependent on your specific table. Its size, selectivity index. And on one table, one method will be faster, on the other hand, on the contrary, it will be slower. - Mike
  • @Mike, I didn’t work much with the MySQL optimizer, but I managed to notice that it builds the plan literally more often to the text of the query than the MS SQL and Oracle optimizers, for example. In this regard, any "tricky" ways with difficulty brought to the same plan. - pegoopik

1 answer 1

Try on your data option with JOIN. At least reduce the number of disk accesses (compared to UNION ALL ).

 SELECT sum(money) FROM `accounts` JOIN ( SELECT 101 as param_type UNION ALL SELECT 225 UNION ALL SELECT 353 UNION ALL SELECT 448 ) T ON param_type = type WHERE date >= (CURDATE()-1) AND date < CURDATE()