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, 68, 69, 70, 71, 72, 73, 74) 
  AND cscart_product_features_values.feature_id IN (30, 167, 169, 170, 171, 175, 173, 174) 
  AND cscart_product_features_values.lang_code = 'en' 
  AND cscart_categories.category_id IN (417) 
  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.00637

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 12 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 index PRIMARY,feature_id,feature_id_status_idx,filter_id_idx,feature_filter_idx feature_filter_idx 6 51 Using where; Using index; 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
70 6216
70 6217
70 6218
70 6219
70 6220
13 6221
13 6222
13 6223
71 6224
71 6225
71 6226
71 6227
73 6228
73 6229
74 6232
74 6233
74 6234
74 6235
72 6236
72 6237
13 6243