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

Query time 0.00069

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "68.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": 19,
          "rows_produced_per_join": 95,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "23.75",
            "eval_cost": "9.50",
            "prefix_cost": "35.00",
            "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": 95,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "23.75",
            "eval_cost": "9.50",
            "prefix_cost": "68.25",
            "data_read_per_join": "73K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 11172 2197 466
553 11171 2197 466
553 11170 2197 466
553 11169 2197 466
553 11168 2197 466
553 11167 2197 466
553 11166 2197 466
553 11165 2197 466
553 11164 2197 466
553 11163 2197 466
553 11162 1324 466
553 11161 1324 466
553 11160 1324 466
553 11159 1324 466
553 11158 1324 466
553 11157 1324 466
553 11156 1324 466
553 11155 1324 466
553 11154 1324 466
553 11153 1324 466
624 11172 1803 466
624 11171 1803 466
624 11170 1803 466
624 11169 1803 466
624 11168 1803 466
624 11167 1803 466
624 11166 1803 466
624 11165 1803 466
624 11164 1803 466
624 11163 1803 466
624 11162 1803 466
624 11161 1803 466
624 11160 1803 466
624 11159 1803 466
624 11158 1803 466
624 11157 1803 466
624 11156 1803 466
624 11155 1803 466
624 11154 1803 466
624 11153 1803 466
623 11172 2090 466
623 11171 2089 466
623 11170 2088 466
623 11169 2087 466
623 11168 2086 466
623 11167 2085 466
623 11166 2097 466
623 11165 2096 466
623 11164 2095 466
623 11163 1983 466
623 11162 2090 466
623 11161 2089 466
623 11160 2088 466
623 11159 2087 466
623 11158 2086 466
623 11157 2085 466
623 11156 2097 466
623 11155 2096 466
623 11154 2095 466
623 11153 1983 466