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 (
    45, 340, 273, 66, 219, 284, 161, 272, 158, 
    307, 268, 350, 115, 320, 65, 114, 116, 
    159, 160, 162, 297, 299, 278, 280, 308, 
    279, 318, 470, 315, 356, 331, 300, 316, 
    386, 379, 355, 319, 342, 281, 294, 276, 
    275, 220, 402, 580, 277, 574, 576, 837, 
    784, 783, 785, 999
  )

Query time 0.00311

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "148.41"
    },
    "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": 53,
          "rows_produced_per_join": 53,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gpf`.`group_id` in (45,340,273,66,219,284,161,272,158,307,268,350,115,320,65,114,116,159,160,162,297,299,278,280,308,279,318,470,315,356,331,300,316,386,379,355,319,342,281,294,276,275,220,402,580,277,574,576,837,784,783,785,999))",
          "cost_info": {
            "read_cost": "31.81",
            "eval_cost": "5.30",
            "prefix_cost": "37.11",
            "data_read_per_join": "5K"
          },
          "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": 159,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "39.75",
            "eval_cost": "15.90",
            "prefix_cost": "92.76",
            "data_read_per_join": "2K"
          },
          "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": 159,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "39.75",
            "eval_cost": "15.90",
            "prefix_cost": "148.41",
            "data_read_per_join": "122K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
656 908 1653 45
656 134 1652 45
658 1142 1701 65
658 853 1700 65
656 1146 1707 66
656 1145 1610 66
656 1144 1609 66
656 1143 1600 66
656 856 1599 66
656 1540 1912 114
656 1541 1559 114
656 1542 1559 115
656 1543 1607 115
656 1544 1607 116
656 1545 1559 116
656 2258 1558 158
656 2257 1600 158
656 2256 1560 158
656 2255 1559 158
656 2260 2443 159
656 2259 2442 159
656 2251 2441 159
656 2262 2444 160
656 2261 1606 160
656 2252 1597 160
656 2265 1558 161
656 2264 1600 161
656 2263 1559 161
656 2253 1560 161
656 2267 1560 162
656 2266 1559 162
656 2254 1600 162
656 2559 1560 219
656 2560 1559 219
656 3663 1596 219
656 3833 1607 220
656 2562 1560 220
656 2563 1725 220
656 2729 1560 268
656 2730 1559 268
656 2731 1600 268
656 2732 1558 268
656 2746 1559 272
656 2747 1609 272
656 2749 1560 272
656 2750 1724 272
656 2751 1783 272
656 2752 1724 273
656 2753 1559 273
656 2754 1560 273
656 2755 1609 273
656 2756 1783 273
656 2757 2444 273
656 2768 1558 275
656 2767 1560 275
656 2773 1560 276
656 2775 1559 276
656 2786 1560 277
656 2787 1558 277
656 2788 1560 278
656 2789 1600 278
656 2790 1607 278
656 2791 1609 278
656 2792 1600 279
656 2793 1560 279
656 2794 1607 279
656 2795 1560 280
656 2796 2460 280
656 2800 1560 281
656 2801 1674 281
656 2822 1560 284
656 2823 1559 284
656 2824 1755 284
656 2825 1682 284
656 2826 1558 284
656 2860 1560 294
656 2861 1559 294
656 2869 1560 297
656 2870 1600 297
656 2871 2460 297
656 2877 1600 299
656 2878 1559 299
656 2879 2460 299
656 2880 1559 300
656 2881 1560 300
656 2927 1560 307
656 2928 1559 307
656 2929 1600 307
656 2930 1609 307
656 2934 1560 308
656 2935 1559 308
656 2936 1682 308
656 2937 1558 308
656 3011 1609 315
656 3012 1559 315
656 3013 1560 315
656 3014 1607 316
656 3015 1559 316
656 3021 1560 318
656 3022 1559 318
656 3023 1560 319
656 3024 3421 319
656 3026 1596 320
656 3030 1560 320
656 3029 1559 320
656 3060 1560 331
656 3061 1559 331
656 3062 1609 331
656 3090 1560 340
656 3091 1559 340
656 3092 1596 340
656 3093 1600 340
656 3094 1558 340
656 3099 1560 342
656 3100 1559 342
656 3140 1560 350
656 3141 1559 350
656 3160 1600 355
656 3161 1558 355
656 2760 1560 356
656 3164 1559 356
656 3165 1783 356
656 3366 1559 379
656 3367 1560 379
656 3404 1783 386
656 3405 1559 386
656 3548 1559 402
656 3549 1560 402
656 3373 1558 470
656 4124 1559 470
656 4125 1600 470
656 5129 1559 574
656 5126 1914 574
656 5135 1559 576
656 5133 1560 576
656 5151 1560 580
656 5153 1559 580
656 7871 1560 783
656 7872 1559 783
656 7873 1560 784
656 7875 1559 784
656 7876 1600 784
656 7874 1560 785
656 7877 1559 785
656 8703 1559 837
656 8704 1596 837
656 8705 1600 837
656 8706 1558 837
656 10454 1560 999
656 10455 1559 999