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

Query time 0.00071

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "60.20"
    },
    "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": 21,
          "rows_produced_per_join": 84,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "21.00",
            "eval_cost": "8.40",
            "prefix_cost": "30.80",
            "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": [
            "gaseus.gpf.feature_id",
            "gaseus.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 84,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "21.00",
            "eval_cost": "8.40",
            "prefix_cost": "60.20",
            "data_read_per_join": "64K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 11071 1324 462
553 11088 2197 462
553 11087 2197 462
553 11086 2197 462
553 11085 2197 462
553 11084 2197 462
553 11083 2197 462
553 11082 2197 462
553 11081 2197 462
553 11080 2197 462
553 11079 2197 462
553 11078 2197 462
553 11077 1324 462
553 11076 1324 462
553 11067 1324 462
553 11068 1324 462
553 11072 1324 462
553 11074 1324 462
553 11069 1324 462
553 11070 1324 462
553 11073 1324 462
553 11075 1324 462
624 11071 1803 462
624 11088 1803 462
624 11087 1803 462
624 11086 1803 462
624 11085 1803 462
624 11084 1803 462
624 11083 1803 462
624 11082 1803 462
624 11081 1803 462
624 11080 1803 462
624 11079 1803 462
624 11078 1803 462
624 11077 1803 462
624 11076 1803 462
624 11067 1803 462
624 11068 1803 462
624 11072 1803 462
624 11074 1803 462
624 11069 1803 462
624 11070 1803 462
624 11073 1803 462
624 11075 1803 462
623 11071 2084 462
623 11088 2093 462
623 11087 2089 462
623 11086 2088 462
623 11085 2087 462
623 11084 2086 462
623 11083 2085 462
623 11082 2084 462
623 11081 2083 462
623 11080 2092 462
623 11079 2082 462
623 11078 1975 462
623 11077 2093 462
623 11076 2089 462
623 11067 1975 462
623 11068 2082 462
623 11072 2085 462
623 11074 2087 462
623 11069 2092 462
623 11070 2083 462
623 11073 2086 462
623 11075 2088 462