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 (
    9237, 9233, 9234, 9803, 9799, 9797, 10687, 
    9244, 9789, 9248
  )

Query time 0.00281

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "161.91"
    },
    "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": 11,
          "rows_produced_per_join": 11,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "3.22",
            "eval_cost": "2.20",
            "prefix_cost": "5.42",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ],
          "attached_condition": "(`webmarco`.`gpf`.`group_id` in (9237,9233,9234,9803,9799,9797,10687,9244,9789,9248))"
        }
      },
      {
        "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": 103,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "11.10",
            "eval_cost": "20.77",
            "prefix_cost": "37.29",
            "data_read_per_join": "1K"
          },
          "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": 103,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "103.85",
            "eval_cost": "20.77",
            "prefix_cost": "161.91",
            "data_read_per_join": "80K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
626 395983 7159 9233
626 395984 7160 9233
626 395985 7161 9233
626 395986 7162 9233
626 395987 7163 9233
626 395988 7164 9233
626 395989 7163 9234
626 395990 7168 9234
626 395991 7169 9234
626 395992 7164 9234
626 395993 7165 9234
626 395994 7166 9234
626 395995 7167 9234
626 396006 7163 9237
626 396007 7168 9237
626 396008 7169 9237
626 396009 7164 9237
626 396010 7165 9237
626 396011 7166 9237
626 396012 7167 9237
626 396058 7166 9244
626 396059 67588 9244
626 396060 7168 9244
626 396061 7169 9244
626 396062 7170 9244
626 396063 7171 9244
626 396064 7167 9244
626 396078 7161 9248
626 396079 7168 9248
626 396080 7162 9248
626 396081 7163 9248
626 396082 7164 9248
626 396083 7165 9248
626 396084 7166 9248
626 396085 7167 9248
626 414329 7159 9789
626 414330 7160 9789
626 414331 7161 9789
626 414332 7162 9789
626 414333 7163 9789
626 414334 7164 9789
626 414393 7160 9797
626 414394 7161 9797
626 414395 7162 9797
626 414396 7163 9797
626 414397 7164 9797
626 414398 7165 9797
626 414399 7166 9797
626 414408 7161 9799
626 414409 7162 9799
626 414410 7163 9799
626 414411 7164 9799
626 414412 7165 9799
626 414413 7166 9799
626 414436 7159 9803
626 414437 7160 9803
626 414438 7161 9803
626 414439 7162 9803
626 414440 7163 9803
626 414441 7164 9803
626 431514 7165 10687
626 431515 7168 10687
626 431516 7168 10687
626 431517 7168 10687
626 431518 7169 10687
626 431519 7169 10687
626 431520 7169 10687
626 431521 7170 10687
626 431522 7170 10687
626 431523 7170 10687
626 431524 7171 10687
626 431525 7171 10687
626 431526 7171 10687
626 431527 38285 10687
626 431528 38285 10687
626 431529 38285 10687
626 431530 7165 10687
626 431531 7165 10687
626 431532 7166 10687
626 431533 7166 10687
626 431534 7166 10687
626 431535 7167 10687
626 431536 7167 10687
626 431537 7167 10687
860 431514 60615 10687
860 431515 60615 10687
860 431516 64546 10687
860 431517 67701 10687
860 431518 60615 10687
860 431519 64546 10687
860 431520 67701 10687
860 431521 60615 10687
860 431522 64546 10687
860 431523 67701 10687
860 431524 60615 10687
860 431525 64546 10687
860 431526 67701 10687
860 431527 60615 10687
860 431528 64546 10687
860 431529 67701 10687
860 431530 64546 10687
860 431531 67701 10687
860 431532 60615 10687
860 431533 64546 10687
860 431534 67701 10687
860 431535 60615 10687
860 431536 64546 10687
860 431537 67701 10687