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

Query time 0.00068

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "75.25"
    },
    "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": 5,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.25",
            "eval_cost": "0.50",
            "prefix_cost": "1.75",
            "data_read_per_join": "520"
          },
          "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": 105,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "26.25",
            "eval_cost": "10.50",
            "prefix_cost": "38.50",
            "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": 105,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "26.25",
            "eval_cost": "10.50",
            "prefix_cost": "75.25",
            "data_read_per_join": "81K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 11106 2197 463
553 11107 2197 463
553 11105 2197 463
553 11104 2197 463
553 11103 2197 463
553 11102 2197 463
553 11101 2197 463
553 11100 2197 463
553 11099 1324 463
553 11098 1324 463
553 11097 1324 463
553 11096 1324 463
553 11095 1324 463
553 11094 1324 463
553 11093 1324 463
553 11092 1324 463
553 11091 1324 463
553 11090 1324 463
553 11089 1324 463
553 11110 2197 463
553 11109 2197 463
553 11108 2197 463
624 11106 1803 463
624 11107 1803 463
624 11105 1803 463
624 11104 1803 463
624 11103 1803 463
624 11102 1803 463
624 11101 1803 463
624 11100 1803 463
624 11099 1803 463
624 11098 1803 463
624 11097 1803 463
624 11096 1803 463
624 11095 1803 463
624 11094 1803 463
624 11093 1803 463
624 11092 1803 463
624 11091 1803 463
624 11090 1803 463
624 11089 1803 463
624 11110 1803 463
624 11109 1803 463
624 11108 1803 463
623 11106 2086 463
623 11107 2087 463
623 11105 2085 463
623 11104 2084 463
623 11103 2083 463
623 11102 2092 463
623 11101 2082 463
623 11100 1975 463
623 11099 2093 463
623 11098 2089 463
623 11097 2088 463
623 11096 2087 463
623 11095 2086 463
623 11094 2085 463
623 11093 2084 463
623 11092 2083 463
623 11091 2092 463
623 11090 2082 463
623 11089 1975 463
623 11110 2093 463
623 11109 2089 463
623 11108 2088 463