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 (
    9237, 9233, 9234, 9803, 9799, 10690, 
    10695, 7607, 7579, 7567, 10688, 9236, 
    10703, 10693, 9240, 10700, 7598, 7600, 
    9239
  )

Query time 0.00555

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "352.07"
    },
    "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": 24,
          "rows_produced_per_join": 24,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "5.84",
            "eval_cost": "4.80",
            "prefix_cost": "10.64",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ],
          "attached_condition": "(`webmarco`.`gpf`.`group_id` in (9237,9233,9234,9803,9799,10690,10695,7607,7579,7567,10688,9236,10703,10693,9240,10700,7598,7600,9239))"
        }
      },
      {
        "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": [
            "webmarco.gpf.group_id"
          ],
          "rows_examined_per_scan": 9,
          "rows_produced_per_join": 226,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "24.22",
            "eval_cost": "45.32",
            "prefix_cost": "80.17",
            "data_read_per_join": "3K"
          },
          "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": [
            "webmarco.gpf.feature_id",
            "webmarco.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 226,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "226.58",
            "eval_cost": "45.32",
            "prefix_cost": "352.07",
            "data_read_per_join": "175K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
860 309585 64723 7567
860 309586 64691 7567
548 359805 8175 7579
548 435519 61161 7579
860 359805 64721 7579
860 435519 64721 7579
548 359744 1193 7598
548 359746 1195 7598
548 359752 1195 7600
548 359753 1194 7600
860 359772 64721 7607
626 395983 7159 9233
626 395984 7160 9233
626 395985 7161 9233
626 395986 7162 9233
626 395987 7163 9233
626 395988 7164 9233
626 395989 7163 9234
626 395990 7168 9234
626 395991 7169 9234
626 395992 7164 9234
626 395993 7165 9234
626 395994 7166 9234
626 395995 7167 9234
626 396002 7164 9236
626 396003 7165 9236
626 396004 7166 9236
626 396005 7167 9236
626 396006 7163 9237
626 396007 7168 9237
626 396008 7169 9237
626 396009 7164 9237
626 396010 7165 9237
626 396011 7166 9237
626 396012 7167 9237
626 396018 7159 9239
626 396019 7160 9239
626 396020 7161 9239
626 396021 7162 9239
626 396022 7163 9239
626 396023 7164 9239
626 396024 7165 9239
626 396025 7166 9239
626 396027 67587 9240
626 396028 7159 9240
626 396029 7160 9240
626 396030 7161 9240
626 396031 7162 9240
626 396032 7163 9240
626 396033 7164 9240
626 396034 7165 9240
626 396035 7166 9240
626 414408 7161 9799
626 414409 7162 9799
626 414410 7163 9799
626 414411 7164 9799
626 414412 7165 9799
626 414413 7166 9799
626 414436 7159 9803
626 414437 7160 9803
626 414438 7161 9803
626 414439 7162 9803
626 414440 7163 9803
626 414441 7164 9803
626 431542 7161 10688
626 431543 7168 10688
626 431544 7169 10688
626 431545 7170 10688
626 431546 7162 10688
626 431547 7163 10688
626 431548 7164 10688
626 431549 7165 10688
626 431550 7166 10688
626 431551 7167 10688
626 431677 7168 10688
626 431678 7169 10688
626 431679 7170 10688
626 431680 7161 10688
626 431681 7162 10688
626 431682 7163 10688
626 431683 7164 10688
626 431684 7165 10688
626 431685 7166 10688
626 431686 7167 10688
860 431542 64475 10688
860 431543 64475 10688
860 431544 64475 10688
860 431545 64475 10688
860 431546 64475 10688
860 431547 64475 10688
860 431548 64475 10688
860 431549 64475 10688
860 431550 64475 10688
860 431551 64475 10688
860 431677 64236 10688
860 431678 64236 10688
860 431679 64236 10688
860 431680 64236 10688
860 431681 64236 10688
860 431682 64236 10688
860 431683 64236 10688
860 431684 64236 10688
860 431685 64236 10688
860 431686 64236 10688
626 431561 7160 10690
626 431562 7168 10690
626 431563 7161 10690
626 431564 7162 10690
626 431565 7163 10690
626 431566 7164 10690
626 431567 7165 10690
626 431568 7166 10690
626 431569 7167 10690
626 431587 7168 10690
626 431588 7168 10690
626 431589 7160 10690
626 431590 7160 10690
626 431591 7161 10690
626 431592 7161 10690
626 431593 7162 10690
626 431594 7162 10690
626 431595 7163 10690
626 431596 7163 10690
626 431597 7164 10690
626 431598 7164 10690
626 431599 7165 10690
626 431600 7165 10690
626 431601 7166 10690
626 431602 7166 10690
626 431603 7167 10690
626 431604 7167 10690
860 431561 64236 10690
860 431562 64236 10690
860 431563 64236 10690
860 431564 64236 10690
860 431565 64236 10690
860 431566 64236 10690
860 431567 64236 10690
860 431568 64236 10690
860 431569 64236 10690
860 431587 64545 10690
860 431588 65265 10690
860 431589 64545 10690
860 431590 65265 10690
860 431591 64545 10690
860 431592 65265 10690
860 431593 64545 10690
860 431594 65265 10690
860 431595 64545 10690
860 431596 65265 10690
860 431597 64545 10690
860 431598 65265 10690
860 431599 64545 10690
860 431600 65265 10690
860 431601 64545 10690
860 431602 65265 10690
860 431603 64545 10690
860 431604 65265 10690
626 431605 7161 10693
626 431606 7168 10693
626 431607 7169 10693
626 431608 7170 10693
626 431609 7162 10693
626 431610 7163 10693
626 431611 7164 10693
626 431612 7165 10693
626 431613 7166 10693
626 431614 7167 10693
860 431605 64545 10693
860 431606 64545 10693
860 431607 64545 10693
860 431608 64545 10693
860 431609 64545 10693
860 431610 64545 10693
860 431611 64545 10693
860 431612 64545 10693
860 431613 64545 10693
860 431614 64545 10693
626 431659 7160 10695
626 431660 7161 10695
626 431661 7162 10695
626 431662 7163 10695
626 431663 7164 10695
626 431664 7165 10695
626 431665 7166 10695
626 431666 7167 10695
860 431659 64475 10695
860 431660 64475 10695
860 431661 64475 10695
860 431662 64475 10695
860 431663 64475 10695
860 431664 64475 10695
860 431665 64475 10695
860 431666 64475 10695
626 431734 7160 10700
626 431735 7168 10700
626 431736 7161 10700
626 431737 7162 10700
626 431738 7163 10700
626 431739 7164 10700
626 431740 7165 10700
626 431741 7166 10700
626 431742 7167 10700
626 431767 7161 10703
626 431768 7168 10703
626 431769 7169 10703
626 431770 7162 10703
626 431771 7163 10703
626 431772 7164 10703
626 431773 7165 10703
626 431774 7166 10703
626 431775 7167 10703