SELECT 
  pfv.feature_id, 
  pfv.product_id, 
  pfv.variant_id, 
  gp.group_id 
FROM 
  cscart_product_features_values AS pfv 
  INNER JOIN cscart_product_variation_group_products AS gp ON pfv.product_id = gp.product_id 
  INNER JOIN cscart_product_variation_group_features AS gpf ON gpf.group_id = gp.group_id 
  AND gpf.feature_id = pfv.feature_id 
WHERE 
  pfv.lang_code = 'en' 
  AND gp.group_id IN (8575, 8571, 8570, 8573, 8574, 8572)

Query time 0.00150

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "88.77"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 6,
          "rows_produced_per_join": 6,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "2.22",
            "eval_cost": "1.20",
            "prefix_cost": "3.42",
            "data_read_per_join": "624"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ],
          "attached_condition": "(`webmarco`.`gpf`.`group_id` in (8575,8571,8570,8573,8574,8572))"
        }
      },
      {
        "table": {
          "table_name": "gp",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "webmarco.gpf.group_id"
          ],
          "rows_examined_per_scan": 9,
          "rows_produced_per_join": 56,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "6.06",
            "eval_cost": "11.33",
            "prefix_cost": "20.80",
            "data_read_per_join": "906"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "lang_code",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "fpl",
          "used_key_parts": [
            "feature_id",
            "product_id",
            "lang_code"
          ],
          "key_length": "12",
          "ref": [
            "webmarco.gpf.feature_id",
            "webmarco.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 56,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "56.65",
            "eval_cost": "11.33",
            "prefix_cost": "88.78",
            "data_read_per_join": "43K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
1117 392159 66167 8570
1117 392160 66174 8570
1117 392161 66180 8570
1117 392162 66221 8570
1117 392163 66169 8571
1117 392164 66176 8571
1117 392165 66182 8571
1117 392169 66170 8572
1117 392170 66177 8572
1117 392171 66189 8572
1117 392172 66168 8573
1117 392173 66181 8573
1117 392174 66187 8573
1117 392175 66168 8574
1117 392176 66175 8574
1117 392177 66181 8574
1117 392178 66187 8574
1117 392179 66161 8575
1117 392180 66168 8575
1117 392181 66169 8575
1117 392182 66170 8575
1117 392183 66171 8575
1117 392184 66175 8575
1117 392185 66176 8575
1117 392186 66177 8575
1117 392187 66178 8575
1117 392188 66181 8575
1117 392189 66182 8575
1117 392190 66183 8575
1117 392191 66187 8575
1117 392192 66188 8575
1117 392193 66189 8575
1117 392194 66190 8575
1117 392195 66194 8575
1117 392196 66195 8575
1117 392197 66196 8575
1117 392198 66201 8575
1117 392199 66202 8575
1117 392200 66203 8575
1117 392201 66208 8575
1117 392202 66209 8575
1117 392203 66210 8575
1117 392204 66215 8575
1117 392205 66216 8575
1117 392206 66222 8575