How to optimize WHERE IN in a query when there are a lot of values?

Question

PHP

Good day. I ask for help in solving my problem.

SELECT `price`,`currency`,`status` FROM `orders` WHERE `currency` = $currency AND `date_add` >= $q[0] AND `date_add` <= $q[1] AND `user_id` IN ($ids)

You need to get the amount of orders for a certain period (this is always a week, for example, from 01/18/21 00:00:00 to 01/24/21 23:59:59) in the desired currency, the required partner and get the entire array of orders. The problem is that the partner has a lot of invited clients now, more than 2.5 thousand (growing every day) - this is the value of the $ids variable in WHERE IN. More than 3 million orders per week. Information on orders is stored for a month, everything that was earlier is transferred to another database and deleted. Now the table has 12 million rows. Such a request takes a very long time. Can you suggest how to speed up and optimize this process? Combined index on currency, date_add, user_id available

Answer 1

JOIN users ON users.id = orders.user_id
WHERE ... AND users.partner_id = $partner_id