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 (
    89, 527, 415, 202, 208, 819, 221, 229, 
    228, 246, 354, 433, 167, 505, 509, 372, 
    217, 219, 175, 471, 541, 642, 610, 289, 
    234, 284, 247, 249, 207, 235, 815, 195, 
    643, 646
  )

Query time 0.00317

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "123.26"
    },
    "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": 45,
          "rows_produced_per_join": 45,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gpf`.`group_id` in (89,527,415,202,208,819,221,229,228,246,354,433,167,505,509,372,217,219,175,471,541,642,610,289,234,284,247,249,207,235,815,195,643,646))",
          "cost_info": {
            "read_cost": "24.26",
            "eval_cost": "4.50",
            "prefix_cost": "28.76",
            "data_read_per_join": "4K"
          },
          "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": 135,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "33.75",
            "eval_cost": "13.50",
            "prefix_cost": "76.01",
            "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": 135,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "33.75",
            "eval_cost": "13.50",
            "prefix_cost": "123.26",
            "data_read_per_join": "104K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
643 1273 1587 89
643 1420 1587 89
643 1421 1587 89
643 1422 1587 89
643 1423 1587 89
643 1424 1587 89
656 1273 1778 89
656 1420 1778 89
656 1421 1755 89
656 1422 1755 89
656 1423 1558 89
656 1424 1558 89
648 1273 1562 89
648 1420 1563 89
648 1421 1562 89
648 1422 1563 89
648 1423 1562 89
648 1424 1563 89
656 2302 1560 167
656 2301 1916 167
656 2363 2758 175
656 2364 1600 175
656 2365 2315 175
656 2430 1560 195
656 2431 1559 195
656 2473 2903 202
656 2475 1559 202
656 2476 1560 202
656 2477 1596 202
656 2503 2919 207
656 2504 1559 207
656 2505 1560 207
656 2506 1596 208
656 2507 1600 208
656 2508 2077 208
656 2550 2441 217
656 2549 2441 217
656 2548 1599 217
656 2544 1599 217
656 2551 1597 217
656 2552 1597 217
648 2550 1563 217
648 2549 1562 217
648 2548 1562 217
648 2544 1563 217
648 2551 1562 217
648 2552 1563 217
656 2559 1560 219
656 2560 1559 219
656 3663 1596 219
656 2534 1599 221
656 2564 2984 221
656 2565 2985 221
656 2579 1595 228
656 2580 1560 228
643 2300 1526 229
643 2582 1526 229
643 2583 1592 229
643 2584 1592 229
656 2300 1916 229
656 2582 1560 229
656 2583 1560 229
656 2584 1916 229
656 2596 1560 234
656 2597 1560 234
656 2598 1595 234
656 2599 1595 234
656 2600 1596 234
656 2601 1596 234
648 2596 1563 234
648 2597 1562 234
648 2598 1562 234
648 2599 1563 234
648 2600 1562 234
648 2601 1563 234
643 2602 1587 235
643 2604 1526 235
643 2605 1526 235
643 2606 1526 235
643 2607 1587 235
643 2608 1587 235
656 2602 1560 235
656 2604 1560 235
656 2605 1596 235
656 2606 1607 235
656 2607 1596 235
656 2608 1607 235
648 2602 1563 235
648 2604 1562 235
648 2605 1562 235
648 2606 1562 235
648 2607 1563 235
648 2608 1563 235
656 2637 1600 246
656 2638 1560 246
648 2641 1563 247
648 2291 1562 247
648 2639 1563 247
648 2640 1562 247
656 2641 1916 247
656 2291 1560 247
656 2639 1560 247
656 2640 1916 247
648 2647 1564 249
648 2646 1563 249
656 2822 1560 284
656 2823 1559 284
656 2824 1755 284
656 2825 1682 284
656 2826 1558 284
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 3155 1560 354
656 3156 1559 354
656 2737 1560 372
656 3255 1559 372
656 2502 1559 415
656 3639 1560 415
656 3834 1559 433
656 3835 1560 433
656 4126 1600 471
656 4127 1600 471
656 4128 1600 471
656 4129 1600 471
648 4126 1686 471
648 4127 1562 471
648 4128 1563 471
648 4129 1687 471
656 4294 1912 505
656 4295 2077 505
656 4319 1607 509
656 4320 1560 509
656 4321 1607 509
656 4311 1560 509
656 4315 1607 509
656 4316 1560 509
656 4317 1607 509
656 4318 1560 509
658 4319 5474 509
658 4320 5475 509
658 4321 5475 509
658 4311 5472 509
658 4315 5472 509
658 4316 5473 509
658 4317 5473 509
658 4318 5474 509
656 4526 1554 527
656 4527 1554 527
656 4528 1760 527
656 4529 1760 527
656 4530 1761 527
656 4531 1761 527
656 4532 1762 527
656 4533 1762 527
648 4526 1562 527
648 4527 1563 527
648 4528 1562 527
648 4529 1563 527
648 4530 1562 527
648 4531 1563 527
648 4532 1562 527
648 4533 1563 527
656 4796 1914 541
656 4797 1607 541
656 5624 1559 610
656 5625 1560 610
656 5894 6991 642
656 5893 1784 642
656 5892 7064 642
656 5900 1560 643
656 5899 1559 643
656 5934 1559 646
656 5935 1560 646
766 2459 8558 815
766 8492 8551 815
766 8491 8560 815
766 8493 8557 815
766 8500 8551 819
766 1532 8558 819
766 8502 8553 819
766 8501 8557 819
766 8499 8556 819