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 (472)

Query time 0.00063

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "138.60"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 4,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.40",
            "prefix_cost": "1.40",
            "data_read_per_join": "416"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      },
      {
        "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": [
            "const"
          ],
          "rows_examined_per_scan": 49,
          "rows_produced_per_join": 196,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "49.00",
            "eval_cost": "19.60",
            "prefix_cost": "70.00",
            "data_read_per_join": "3K"
          },
          "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": [
            "gaseus.gpf.feature_id",
            "gaseus.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 196,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "49.00",
            "eval_cost": "19.60",
            "prefix_cost": "138.60",
            "data_read_per_join": "151K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 11298 2197 472
553 11297 2197 472
553 11296 2197 472
553 11295 2197 472
553 11294 2197 472
553 11293 2197 472
553 11292 2197 472
553 11291 2197 472
553 11290 2197 472
553 11289 2197 472
553 11288 1324 472
553 11287 1324 472
553 11286 1324 472
553 11285 1324 472
553 11284 1324 472
553 11283 1324 472
553 11282 1324 472
553 11281 1324 472
553 11280 1324 472
553 11279 1324 472
624 11298 1705 472
624 11297 1705 472
624 11296 1705 472
624 11295 1705 472
624 11294 1705 472
624 11293 1705 472
624 11292 1705 472
624 11291 1705 472
624 11290 1705 472
624 11289 1705 472
624 11288 1705 472
624 11287 1705 472
624 11286 1705 472
624 11285 1705 472
624 11284 1705 472
624 11283 1705 472
624 11282 1705 472
624 11281 1705 472
624 11280 1705 472
624 11279 1705 472
623 11298 2090 472
623 11297 2089 472
623 11296 2088 472
623 11295 2087 472
623 11294 2086 472
623 11293 2085 472
623 11292 2097 472
623 11291 2096 472
623 11290 2095 472
623 11289 1983 472
623 11288 2090 472
623 11287 2089 472
623 11286 2088 472
623 11285 2087 472
623 11284 2086 472
623 11283 2085 472
623 11282 2097 472
623 11281 2096 472
623 11280 2095 472
623 11279 1983 472