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 (
    10757, 10766, 10745, 10771, 10761, 10746, 
    10781, 10762, 10775, 10737, 10779, 
    10787, 10743, 10789, 10783, 10785
  )

Query time 0.00397

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "363.27"
    },
    "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": 107,
          "rows_produced_per_join": 107,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "22.53",
            "eval_cost": "21.40",
            "prefix_cost": "43.93",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ],
          "attached_condition": "(`webmarco`.`gp`.`group_id` in (10757,10766,10745,10771,10761,10746,10781,10762,10775,10737,10779,10787,10743,10789,10783,10785))"
        }
      },
      {
        "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": [
            "webmarco.gp.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 151,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "107.05",
            "eval_cost": "30.33",
            "prefix_cost": "181.30",
            "data_read_per_join": "15K"
          },
          "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": [
            "webmarco.gpf.feature_id",
            "webmarco.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 151,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "151.64",
            "eval_cost": "30.33",
            "prefix_cost": "363.27",
            "data_read_per_join": "117K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
860 432251 64682 10737
1013 432251 64581 10737
860 432259 64539 10737
1013 432259 64581 10737
860 432260 64539 10737
1013 432260 64557 10737
860 432261 64539 10737
1013 432261 64558 10737
860 432262 64575 10737
1013 432262 64581 10737
860 432263 64575 10737
1013 432263 64557 10737
860 432264 64575 10737
1013 432264 64558 10737
860 432265 64682 10737
1013 432265 64557 10737
860 432266 64682 10737
1013 432266 64558 10737
860 432320 64475 10743
1013 432320 64557 10743
860 432332 64475 10743
1013 432332 64558 10743
860 432333 67794 10745
1013 432333 64557 10745
860 432346 65265 10745
1013 432346 64557 10745
860 432347 65265 10745
1013 432347 64558 10745
860 432348 65265 10745
1013 432348 64559 10745
860 432349 65265 10745
1013 432349 64560 10745
860 432350 67794 10745
1013 432350 64558 10745
860 432351 67794 10745
1013 432351 64559 10745
860 432352 67794 10745
1013 432352 64560 10745
860 432353 64537 10746
1013 432353 64581 10746
860 432355 64537 10746
1013 432355 64557 10746
860 432356 64544 10746
1013 432356 64581 10746
860 432357 64544 10746
1013 432357 64557 10746
860 432358 64692 10746
1013 432358 64581 10746
860 432359 64692 10746
1013 432359 64557 10746
860 432460 64677 10757
1013 432460 64561 10757
860 432462 64677 10757
1013 432462 64562 10757
860 432463 64677 10757
1013 432463 64563 10757
860 432472 60615 10757
1013 432472 64561 10757
860 432473 60615 10757
1013 432473 64562 10757
860 432474 60615 10757
1013 432474 64563 10757
860 432475 64062 10757
1013 432475 64561 10757
860 432476 64062 10757
1013 432476 64562 10757
860 432477 64062 10757
1013 432477 64563 10757
860 432478 64477 10757
1013 432478 64561 10757
860 432479 64477 10757
1013 432479 64562 10757
860 432480 64477 10757
1013 432480 64563 10757
860 432481 64575 10757
1013 432481 64561 10757
860 432482 64575 10757
1013 432482 64562 10757
860 432483 64575 10757
1013 432483 64563 10757
860 432484 64732 10757
1013 432484 64561 10757
860 432485 64732 10757
1013 432485 64562 10757
860 432486 64732 10757
1013 432486 64563 10757
860 432487 64753 10757
1013 432487 64561 10757
860 432488 64753 10757
1013 432488 64562 10757
860 432489 64753 10757
1013 432489 64563 10757
860 432490 66261 10757
1013 432490 64561 10757
860 432491 66261 10757
1013 432491 64562 10757
860 432492 66261 10757
1013 432492 64563 10757
860 432493 67568 10757
1013 432493 64561 10757
860 432494 67568 10757
1013 432494 64562 10757
860 432495 67568 10757
1013 432495 64563 10757
860 432449 64677 10761
1013 432449 64561 10761
860 432515 60615 10761
1013 432515 64561 10761
860 432516 60615 10761
1013 432516 64562 10761
860 432517 60615 10761
1013 432517 64563 10761
860 432518 64236 10761
1013 432518 64561 10761
860 432519 64236 10761
1013 432519 64562 10761
860 432520 64236 10761
1013 432520 64563 10761
860 432521 64677 10761
1013 432521 64562 10761
860 432522 64677 10761
1013 432522 64563 10761
860 432419 64475 10762
1013 432419 64561 10762
860 432523 64475 10762
1013 432523 64562 10762
860 432524 64475 10762
1013 432524 64563 10762
860 432541 64475 10766
1013 432541 64561 10766
860 432542 64062 10766
1013 432542 64561 10766
860 432543 64062 10766
1013 432543 64562 10766
860 432544 64062 10766
1013 432544 64563 10766
860 432545 64475 10766
1013 432545 64562 10766
860 432546 64475 10766
1013 432546 64563 10766
860 432547 64677 10766
1013 432547 64561 10766
860 432548 64677 10766
1013 432548 64562 10766
860 432549 64677 10766
1013 432549 64563 10766
860 432550 66121 10766
1013 432550 64561 10766
860 432551 66121 10766
1013 432551 64562 10766
860 432552 66121 10766
1013 432552 64563 10766
860 432589 64575 10771
1013 432589 64561 10771
860 432590 64545 10771
1013 432590 64561 10771
860 432591 64545 10771
1013 432591 64562 10771
860 432592 64545 10771
1013 432592 64563 10771
860 432593 64575 10771
1013 432593 64562 10771
860 432594 64575 10771
1013 432594 64563 10771
860 432595 66121 10771
1013 432595 64561 10771
860 432596 66121 10771
1013 432596 64562 10771
860 432597 66121 10771
1013 432597 64563 10771
860 432642 64477 10775
1013 432642 64561 10775
860 432643 64477 10775
1013 432643 64562 10775
860 432644 64477 10775
1013 432644 64563 10775
860 432645 66121 10775
1013 432645 64561 10775
860 432646 66121 10775
1013 432646 64562 10775
860 432647 66121 10775
1013 432647 64563 10775
860 432652 64691 10779
1013 432652 67603 10779
860 432660 64691 10779
1013 432660 64560 10779
860 432661 64691 10779
1013 432661 64561 10779
860 432662 64691 10779
1013 432662 64563 10779
860 432668 64545 10781
1013 432668 67603 10781
860 432669 64545 10781
1013 432669 64560 10781
860 432670 64545 10781
1013 432670 64561 10781
860 432671 64545 10781
1013 432671 64563 10781
1128 432673 67642 10783
1128 432677 67643 10783
860 432679 64475 10785
1128 432679 67642 10785
860 432683 64475 10785
1128 432683 67643 10785
860 432688 64475 10787
1128 432688 67642 10787
860 432689 64475 10787
1128 432689 67643 10787
860 432694 64475 10789
1128 432694 67642 10789
860 432695 64475 10789
1128 432695 67643 10789