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 (
    10698, 9812, 9797, 10687, 9789, 9248, 
    9242, 10689, 9790, 9802, 9807, 9806, 
    10699, 9793, 9798, 9800, 9243, 10702, 
    10701, 9247
  )

Query time 0.00296

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "381.33"
    },
    "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": 26,
          "rows_produced_per_join": 26,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "6.24",
            "eval_cost": "5.20",
            "prefix_cost": "11.44",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ],
          "attached_condition": "(`webmarco`.`gpf`.`group_id` in (10698,9812,9797,10687,9789,9248,9242,10689,9790,9802,9807,9806,10699,9793,9798,9800,9243,10702,10701,9247))"
        }
      },
      {
        "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": 245,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "26.24",
            "eval_cost": "49.09",
            "prefix_cost": "86.77",
            "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": 245,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "245.47",
            "eval_cost": "49.09",
            "prefix_cost": "381.33",
            "data_read_per_join": "189K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
626 396044 7159 9242
626 396045 7160 9242
626 396046 7161 9242
626 396047 7162 9242
626 396048 7163 9242
626 396049 7164 9242
626 396050 7165 9242
626 396052 7159 9243
626 396053 7160 9243
626 396054 7161 9243
626 396055 7162 9243
626 396056 7163 9243
626 396057 7164 9243
626 396074 67587 9247
626 396075 7159 9247
626 396076 7160 9247
626 396077 7161 9247
626 396078 7161 9248
626 396079 7168 9248
626 396080 7162 9248
626 396081 7163 9248
626 396082 7164 9248
626 396083 7165 9248
626 396084 7166 9248
626 396085 7167 9248
626 414329 7159 9789
626 414330 7160 9789
626 414331 7161 9789
626 414332 7162 9789
626 414333 7163 9789
626 414334 7164 9789
626 414335 7160 9790
626 414336 7161 9790
626 414337 7162 9790
626 414338 7163 9790
626 414339 7164 9790
626 414340 7165 9790
626 414341 7166 9790
626 414360 7160 9793
626 414361 7161 9793
626 414362 7162 9793
626 414363 7163 9793
626 414364 7164 9793
626 414365 7165 9793
626 414366 7166 9793
626 414393 7160 9797
626 414394 7161 9797
626 414395 7162 9797
626 414396 7163 9797
626 414397 7164 9797
626 414398 7165 9797
626 414399 7166 9797
626 414401 7166 9798
626 414402 67588 9798
626 414403 7168 9798
626 414404 7169 9798
626 414405 7170 9798
626 414406 7171 9798
626 414407 7167 9798
626 414415 7166 9800
626 414416 67588 9800
626 414417 7168 9800
626 414418 7169 9800
626 414419 7170 9800
626 414420 7171 9800
626 414421 7167 9800
626 414429 7160 9802
626 414430 7161 9802
626 414431 7162 9802
626 414432 7163 9802
626 414433 7164 9802
626 414434 7165 9802
626 414435 7166 9802
626 414459 7160 9806
626 414460 7161 9806
626 414461 7162 9806
626 414462 7163 9806
626 414463 7164 9806
626 414464 7165 9806
626 414465 7166 9806
626 414468 7159 9807
626 414470 7160 9807
626 414471 7161 9807
626 414472 7162 9807
626 414473 7163 9807
626 414474 7164 9807
626 414475 7165 9807
626 414476 7166 9807
626 414514 67587 9812
626 414516 7159 9812
626 414517 7160 9812
626 414518 7161 9812
626 414519 7162 9812
626 414520 7163 9812
626 414521 7164 9812
626 431514 7165 10687
626 431515 7168 10687
626 431516 7168 10687
626 431517 7168 10687
626 431518 7169 10687
626 431519 7169 10687
626 431520 7169 10687
626 431521 7170 10687
626 431522 7170 10687
626 431523 7170 10687
626 431524 7171 10687
626 431525 7171 10687
626 431526 7171 10687
626 431527 38285 10687
626 431528 38285 10687
626 431529 38285 10687
626 431530 7165 10687
626 431531 7165 10687
626 431532 7166 10687
626 431533 7166 10687
626 431534 7166 10687
626 431535 7167 10687
626 431536 7167 10687
626 431537 7167 10687
860 431514 60615 10687
860 431515 60615 10687
860 431516 64546 10687
860 431517 67701 10687
860 431518 60615 10687
860 431519 64546 10687
860 431520 67701 10687
860 431521 60615 10687
860 431522 64546 10687
860 431523 67701 10687
860 431524 60615 10687
860 431525 64546 10687
860 431526 67701 10687
860 431527 60615 10687
860 431528 64546 10687
860 431529 67701 10687
860 431530 64546 10687
860 431531 67701 10687
860 431532 60615 10687
860 431533 64546 10687
860 431534 67701 10687
860 431535 60615 10687
860 431536 64546 10687
860 431537 67701 10687
626 431552 7160 10689
626 431553 7168 10689
626 431554 7161 10689
626 431555 7162 10689
626 431556 7163 10689
626 431557 7164 10689
626 431558 7165 10689
626 431559 7166 10689
626 431560 7167 10689
860 431552 64691 10689
860 431553 64691 10689
860 431554 64691 10689
860 431555 64691 10689
860 431556 64691 10689
860 431557 64691 10689
860 431558 64691 10689
860 431559 64691 10689
860 431560 64691 10689
626 431687 7160 10698
626 431688 7168 10698
626 431689 7169 10698
626 431690 7170 10698
626 431691 7161 10698
626 431692 7162 10698
626 431693 7163 10698
626 431694 7164 10698
626 431695 7165 10698
626 431696 7166 10698
626 431697 7167 10698
860 431687 64236 10698
860 431688 64236 10698
860 431689 64236 10698
860 431690 64236 10698
860 431691 64236 10698
860 431692 64236 10698
860 431693 64236 10698
860 431694 64236 10698
860 431695 64236 10698
860 431696 64236 10698
860 431697 64236 10698
626 431698 7164 10699
626 431699 7168 10699
626 431700 7168 10699
626 431701 7168 10699
626 431702 7168 10699
626 431703 7169 10699
626 431704 7169 10699
626 431705 7169 10699
626 431706 7169 10699
626 431707 7170 10699
626 431708 7170 10699
626 431709 7170 10699
626 431710 7170 10699
626 431711 7171 10699
626 431712 7171 10699
626 431713 7171 10699
626 431714 7171 10699
626 431715 38285 10699
626 431716 38285 10699
626 431717 38285 10699
626 431718 38285 10699
626 431719 7164 10699
626 431720 7164 10699
626 431721 7164 10699
626 431722 7165 10699
626 431723 7165 10699
626 431724 7165 10699
626 431725 7165 10699
626 431726 7166 10699
626 431727 7166 10699
626 431728 7166 10699
626 431729 7166 10699
626 431730 7167 10699
626 431731 7167 10699
626 431732 7167 10699
626 431733 7167 10699
860 431698 64062 10699
860 431699 64062 10699
860 431700 64236 10699
860 431701 64691 10699
860 431702 65302 10699
860 431703 64062 10699
860 431704 64236 10699
860 431705 64691 10699
860 431706 65302 10699
860 431707 64062 10699
860 431708 64236 10699
860 431709 64691 10699
860 431710 65302 10699
860 431711 64062 10699
860 431712 64236 10699
860 431713 64691 10699
860 431714 65302 10699
860 431715 64062 10699
860 431716 64236 10699
860 431717 64691 10699
860 431718 65302 10699
860 431719 64236 10699
860 431720 64691 10699
860 431721 65302 10699
860 431722 64062 10699
860 431723 64236 10699
860 431724 64691 10699
860 431725 65302 10699
860 431726 64062 10699
860 431727 64236 10699
860 431728 64691 10699
860 431729 65302 10699
860 431730 64062 10699
860 431731 64236 10699
860 431732 64691 10699
860 431733 65302 10699
626 431743 7160 10701
626 431744 7168 10701
626 431745 7169 10701
626 431746 7170 10701
626 431747 7161 10701
626 431748 7162 10701
626 431749 7163 10701
626 431750 7164 10701
626 431751 7165 10701
626 431752 7166 10701
626 431753 7167 10701
860 431743 64475 10701
860 431744 64475 10701
860 431745 64475 10701
860 431746 64475 10701
860 431747 64475 10701
860 431748 64475 10701
860 431749 64475 10701
860 431750 64475 10701
860 431751 64475 10701
860 431752 64475 10701
860 431753 64475 10701
626 431754 67587 10702
626 431755 7159 10702
626 431756 7168 10702
626 431757 7169 10702
626 431758 7170 10702
626 431759 7160 10702
626 431760 7161 10702
626 431761 7162 10702
626 431762 7163 10702
626 431763 7164 10702
626 431764 7165 10702
626 431765 7166 10702
626 431766 7167 10702
860 431754 64236 10702
860 431755 64236 10702
860 431756 64236 10702
860 431757 64236 10702
860 431758 64236 10702
860 431759 64236 10702
860 431760 64236 10702
860 431761 64236 10702
860 431762 64236 10702
860 431763 64236 10702
860 431764 64236 10702
860 431765 64236 10702
860 431766 64236 10702