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 
  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_ult_product_descriptions shared_descr ON products.product_id = shared_descr.product_id 
  LEFT JOIN cscart_aval_statuses ON cscart_aval_statuses.aval_status_id = products.aval_status 
  LEFT JOIN cscart_aval_statuses_descr ON cscart_aval_statuses.aval_status_id = cscart_aval_statuses_descr.aval_status_id 
  AND cscart_aval_statuses.status = 'A' 
  AND cscart_aval_statuses_descr.lang_code = 'en' 
  LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id 
WHERE 
  cscart_product_filters.filter_id IN (13, 48, 49, 68) 
  AND cscart_product_features_values.feature_id IN (30, 147, 148, 167) 
  AND cscart_product_features_values.lang_code = 'en' 
  AND cscart_categories.category_id IN (436) 
  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 cscart_product_features.feature_type IN ('S', 'M', 'E') 
GROUP BY 
  cscart_product_features_values.variant_id

Query time 0.00452

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_categories const PRIMARY,c_status,p_category_id,idx_category_id PRIMARY 3 const 1 Using temporary; Using filesort
1 SIMPLE products_categories ref PRIMARY,pt,product_id_idx,category_id_idx,product_category_idx,category_product_idx PRIMARY 3 const 75 Using index
1 SIMPLE products eq_ref PRIMARY,status,product_id_idx,status_idx PRIMARY 3 mahm3t_cs443.products_categories.product_id 1 Using where
1 SIMPLE shared_descr ref PRIMARY,product_id product_id 3 mahm3t_cs443.products_categories.product_id 11 Using index
1 SIMPLE cscart_aval_statuses eq_ref PRIMARY PRIMARY 4 mahm3t_cs443.products.aval_status 1
1 SIMPLE cscart_aval_statuses_descr ref aval_status_id aval_status_id 10 mahm3t_cs443.cscart_aval_statuses.aval_status_id,const 1 Using where; Using index
1 SIMPLE cscart_product_filters range PRIMARY,feature_id,feature_id_status_idx,filter_id_idx,feature_filter_idx PRIMARY 3 4 Using index condition; Using where; Using join buffer (flat, BNL join)
1 SIMPLE cscart_product_features eq_ref PRIMARY PRIMARY 3 mahm3t_cs443.cscart_product_filters.feature_id 1 Using where
1 SIMPLE cscart_product_features_values ref PRIMARY,fl,lang_code,product_id,fpl,product_id_idx,feature_id_idx,feature_value_product_idx,product_feature_idx PRIMARY 6 mahm3t_cs443.cscart_product_filters.feature_id,mahm3t_cs443.products_categories.product_id 5 Using where; Using index

Result

filter_id variant_id
13 5773
13 5870
13 5936
48 6063
48 6065
48 6066
48 6067
49 6069
49 6070
49 6071
49 6072
49 6073
49 6074
49 6075
49 6076
49 6077
49 6078
49 6080
48 6294