SELECT 
  cscart_product_features_values.feature_id, 
  cscart_product_features_values.variant_id 
FROM 
  cscart_product_features_values 
  INNER JOIN cscart_products AS products ON products.product_id = cscart_product_features_values.product_id 
  LEFT JOIN cscart_product_features_values as var_val_18 ON var_val_18.product_id = products.product_id 
  AND var_val_18.lang_code = 'en' 
  AND var_val_18.feature_id = 18 
WHERE 
  cscart_product_features_values.feature_id IN (18, 548, 549, 3529, 3534, 3541, 3542) 
  AND products.parent_product_id = 0 
  AND products.status = 'A' 
  AND products.amount != 0 
  AND cscart_product_features_values.lang_code = 'en' 
  AND (
    var_val_18.variant_id IN (5181) 
    OR cscart_product_features_values.feature_id = 18
  ) 
GROUP BY 
  cscart_product_features_values.variant_id 
ORDER BY 
  NULL

Query time 0.35613

JSON explain

{
  "query_block": {
    "select_id": 1,
    "temporary_table": {
      "table": {
        "table_name": "products",
        "access_type": "index_merge",
        "possible_keys": ["PRIMARY", "status", "idx_parent_product_id"],
        "key_length": "3,4",
        "index_merge": {
          "intersect": {
            "range": {
              "key": "status",
              "used_key_parts": ["status"]
            },
            "range": {
              "key": "idx_parent_product_id",
              "used_key_parts": ["parent_product_id"]
            }
          }
        },
        "rows": 28991,
        "filtered": 100,
        "attached_condition": "products.parent_product_id = 0 and products.`status` = 'A' and products.amount <> 0"
      },
      "table": {
        "table_name": "var_val_18",
        "access_type": "ref",
        "possible_keys": [
          "PRIMARY",
          "fl",
          "lang_code",
          "product_id",
          "fpl",
          "idx_product_feature_variant_id"
        ],
        "key": "product_id",
        "key_length": "6",
        "used_key_parts": ["product_id", "feature_id"],
        "ref": ["staging2_wesave_com.products.product_id", "const"],
        "rows": 1,
        "filtered": 38.51078796,
        "attached_condition": "trigcond(var_val_18.lang_code = 'en')",
        "using_index": true
      },
      "table": {
        "table_name": "cscart_product_features_values",
        "access_type": "ref",
        "possible_keys": [
          "PRIMARY",
          "fl",
          "lang_code",
          "product_id",
          "fpl",
          "idx_product_feature_variant_id"
        ],
        "key": "product_id",
        "key_length": "3",
        "used_key_parts": ["product_id"],
        "ref": ["staging2_wesave_com.products.product_id"],
        "rows": 2,
        "filtered": 100,
        "attached_condition": "cscart_product_features_values.feature_id in (18,548,549,3529,3534,3541,3542) and cscart_product_features_values.lang_code = 'en' and (var_val_18.variant_id = 5181 or cscart_product_features_values.feature_id = 18)",
        "using_index": true
      }
    }
  }
}