The following filter query takes 50 seconds on a dedicated server with tons of ram and cpu.
I've made mysqld adjustments so that temp tables are in memory (not to disk) and it still takes 50 seconds.
The filter is looking for products that match 2 features:
122 - 36 associated variants
93 - 1776 associated variants
I have verified that all the JOIN references are indexed, etc.
This is a version 4.7 cs-cart but I don't see any difference running it in a 4.9.3 environment.
Any thoughts on how to get this to perform?
SELECT cscart_product_filters.filter_id, cscart_product_features_values.variant_id FROM cscart_product_features_values LEFT JOIN cscart_products as products ON products.product_id = cscart_product_features_values.product_id LEFT JOIN cscart_product_filters ON cscart_product_filters.feature_id = cscart_product_features_values.feature_id LEFT JOIN cscart_product_features ON cscart_product_features.feature_id = cscart_product_features_values.feature_id LEFT JOIN cscart_product_options_inventory as inventory ON inventory.product_id = products.product_id INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_supplier_links ON cscart_supplier_links.object_id = products.product_id AND cscart_supplier_links.object_type = 'P' LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id LEFT JOIN cscart_product_sales as product_sales ON product_sales.product_id = products.product_id LEFT JOIN cscart_product_features_values as var_val_122 ON var_val_122.product_id = products.product_id AND var_val_122.lang_code = 'en' AND var_val_122.feature_id = 122 LEFT JOIN cscart_product_features_values as var_val_93 ON var_val_93.product_id = products.product_id AND var_val_93.lang_code = 'en' AND var_val_93.feature_id = 93 WHERE cscart_product_filters.filter_id IN (36, 37, 32) AND cscart_product_features_values.feature_id IN (122, 93, 102) AND cscart_product_features_values.lang_code = 'en' AND cscart_categories.category_id IN (1) AND cscart_categories.company_id = 1 AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND ((var_val_122.variant_id IN (12995) AND var_val_93.variant_id IN (12844) AND cscart_product_features_values.feature_id NOT IN (122, 93)) OR (var_val_93.variant_id IN (12844) AND cscart_product_features_values.feature_id = 122) OR (var_val_122.variant_id IN (12995) AND cscart_product_features_values.feature_id = 93)) AND cscart_product_features.feature_type IN ('S', 'M', 'E') GROUP BY cscart_product_features_values.variant_id