I have selected (used to give me a number of products activated in the category with a price> 0):
SELECT COUNT(DISTINCT p.id) as ccount FROM product p JOIN product_php_display_price ppdp ON ppdp.shop_id = 1 AND p.id = ppdp.product_id AND ppdp.currency_id = 1 JOIN shop_product_tree spt ON p.id = spt.product_id AND spt.shop_id = 1 AND (state = "enabled" OR state = "disabled_for_buy") AND ppdp.php_display_price > 0
There are multicolumn indexes for each table used (also several different multicolumn indexes).
The product_php_display_price table has a multicolumn index (shop_id, product_id, currency_id, php_display_price).
The table product uses index (product_id, state).
Each table has 208,000 rows. Is there a way, how to speed that up? On my local machine, it takes about 2 seconds. Thank you.