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 (
    175, 289, 293, 177, 351, 176, 309, 263, 
    287, 553
  )

Query time 0.00204

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "28.01"
    },
    "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": 10,
          "rows_produced_per_join": 10,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gpf`.`group_id` in (175,289,293,177,351,176,309,263,287,553))",
          "cost_info": {
            "read_cost": "6.01",
            "eval_cost": "1.00",
            "prefix_cost": "7.01",
            "data_read_per_join": "1K"
          },
          "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": [
            "softwarepirmam_hewadelivard_cscart_4.gpf.group_id"
          ],
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 30,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "7.50",
            "eval_cost": "3.00",
            "prefix_cost": "17.51",
            "data_read_per_join": "480"
          },
          "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": [
            "softwarepirmam_hewadelivard_cscart_4.gpf.feature_id",
            "softwarepirmam_hewadelivard_cscart_4.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 30,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "7.50",
            "eval_cost": "3.00",
            "prefix_cost": "28.01",
            "data_read_per_join": "23K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
656 2363 2758 175
656 2364 1600 175
656 2365 2315 175
656 2369 1560 176
656 2370 1600 176
656 2371 2315 176
656 2373 1560 177
656 2374 1600 177
656 2708 1560 263
656 2709 1600 263
656 2710 2315 263
656 2802 1560 287
656 2836 1559 287
656 2842 1560 289
656 2843 1559 289
656 2844 1596 289
656 2845 1600 289
656 2846 2315 289
656 2847 1558 289
656 2854 1558 293
656 2855 1559 293
656 2856 1560 293
656 2857 1596 293
656 2858 1600 293
656 2859 2315 293
656 2938 1560 309
656 2939 1559 309
656 2940 1596 309
656 2941 1609 309
656 3142 1560 351
656 3143 1596 351
656 3144 1600 351
656 4928 1790 553
656 4916 1560 553