I have a list with filters and I have to count how many items are in each one filter, but the following query gets slower and much slower when multiple filters are set
SELECT COUNT(*) FROM ( SELECT itf.`filter_id` FROM `item_to_filter` AS `itf` JOIN `item_to_inventory` AS `iti` ON (itf.`item_id` = iti.`item_id` AND iti.`quantity` > 0) WHERE 1 = 1 AND ( (itf.`filter_group_id` = 2 AND itf.`filter_id` IN (1)) OR (itf.`filter_group_id` = 4 AND itf.`filter_id` IN (55)) //gets slower OR (itf.`filter_group_id` = 1 AND itf.`filter_id` IN (107, 108)) //gets much slower ) GROUP BY itf.`item_id` HAVING COUNT(DISTINCT itf.`filter_group_id`) = 3 ) AS `total_items`
Is there any other way to write the query to count items from each filter
Here you can see the tables structure and data (are the indexes correctly sets?)