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 (
    1178, 1180, 1148, 1176, 1059, 1177, 1115, 
    1067, 1077, 1071, 1074, 1052, 1114, 
    1050, 1076
  )

Query time 0.00237

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "54.36"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gp",
          "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": 44,
          "rows_produced_per_join": 44,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gp`.`group_id` in (1178,1180,1148,1176,1059,1177,1115,1067,1077,1071,1074,1052,1114,1050,1076))",
          "cost_info": {
            "read_cost": "19.16",
            "eval_cost": "4.40",
            "prefix_cost": "23.56",
            "data_read_per_join": "704"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "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": [
            "softwarepirmam_hewadelivard_cscart_4.gp.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 44,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "11.00",
            "eval_cost": "4.40",
            "prefix_cost": "38.96",
            "data_read_per_join": "4K"
          },
          "used_columns": [
            "feature_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": 44,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "11.00",
            "eval_cost": "4.40",
            "prefix_cost": "54.36",
            "data_read_per_join": "34K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
779 10982 9506 1050
656 10982 1596 1050
779 10983 9503 1050
656 10983 1560 1050
779 10978 9505 1050
656 10978 1560 1050
656 10996 1596 1052
779 10996 9510 1052
656 10995 1596 1052
779 10995 9503 1052
656 10993 1600 1052
779 10993 9508 1052
656 11008 1560 1052
779 11008 9505 1052
779 11033 9533 1059
656 11033 3037 1059
779 11032 9532 1059
656 11032 9516 1059
779 11031 9531 1059
656 11031 1600 1059
779 11034 9534 1059
656 11034 1600 1059
779 11028 9517 1059
656 11028 1596 1059
779 11029 9520 1059
656 11029 1596 1059
779 11027 9530 1059
656 11027 2077 1059
656 11107 1914 1067
779 11107 9573 1067
656 11106 1596 1067
779 11106 9517 1067
656 11105 1596 1067
779 11105 9506 1067
656 11104 1674 1067
779 11104 9572 1067
656 11122 1600 1071
779 11122 9520 1071
656 11121 1596 1071
779 11121 9517 1071
656 11133 1596 1074
779 11133 9522 1074
656 11135 1596 1074
779 11135 9588 1074
656 11140 9402 1076
779 11140 9520 1076
656 11139 1596 1076
779 11139 9517 1076
656 11142 1596 1077
779 11142 9588 1077
656 11141 1596 1077
779 11141 9522 1077
656 11356 1558 1114
779 11356 9588 1114
656 11357 3037 1114
779 11357 9754 1114
656 11358 3442 1115
779 11358 9588 1115
656 11359 1558 1115
779 11359 9588 1115
656 11464 9967 1148
779 11464 9523 1148
656 11466 1558 1148
779 11466 9588 1148
656 11468 1596 1148
779 11468 9895 1148
656 11549 6360 1176
779 11549 9517 1176
656 11554 3037 1176
779 11554 9870 1176
656 11556 6869 1176
779 11556 10066 1176
771 11550 9422 1177
656 11550 9486 1177
779 11550 9517 1177
771 11552 9422 1177
656 11552 3037 1177
779 11552 10058 1177
771 11553 9422 1177
656 11553 3037 1177
779 11553 9517 1177
656 11557 1600 1178
779 11557 9627 1178
656 11558 1596 1178
779 11558 9970 1178
771 11568 9426 1180
656 11568 1560 1180
779 11568 9517 1180
771 11569 9426 1180
656 11569 1600 1180
779 11569 9523 1180