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 (
    812, 819, 815, 820, 813, 818, 814, 772, 
    765, 817, 767, 768, 809, 771, 769, 773, 
    770, 766, 807, 958, 823, 798, 838, 840, 
    800, 799, 822, 839, 841, 816, 842, 821, 
    956, 969, 981, 966, 983, 982, 980, 867
  )

Query time 0.00248

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "112.01"
    },
    "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": 40,
          "rows_produced_per_join": 40,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gpf`.`group_id` in (812,819,815,820,813,818,814,772,765,817,767,768,809,771,769,773,770,766,807,958,823,798,838,840,800,799,822,839,841,816,842,821,956,969,981,966,983,982,980,867))",
          "cost_info": {
            "read_cost": "24.01",
            "eval_cost": "4.00",
            "prefix_cost": "28.01",
            "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": 120,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "30.00",
            "eval_cost": "12.00",
            "prefix_cost": "70.01",
            "data_read_per_join": "1K"
          },
          "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": 120,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "30.00",
            "eval_cost": "12.00",
            "prefix_cost": "112.01",
            "data_read_per_join": "92K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
766 7608 8553 765
766 7279 8556 765
766 7609 8551 766
766 7610 8557 766
766 7611 8560 766
766 7261 8558 766
766 7612 8555 767
766 7614 8560 767
766 7613 8557 767
766 7248 8558 767
766 7615 8558 768
766 7281 8553 768
766 7283 8558 769
766 7616 8557 769
766 7285 8553 770
766 7620 8560 770
766 7619 8558 770
766 7618 8557 770
766 7617 8551 770
766 7290 8557 771
766 7622 8560 771
766 7621 8551 771
766 7624 8554 772
766 7277 8551 772
766 7623 8553 772
766 7293 8560 773
766 7625 8559 773
766 5985 8551 798
766 8264 8558 798
766 5986 8558 799
766 8265 8557 799
766 5971 8553 800
766 8266 8557 800
766 8267 8558 800
766 8474 8557 807
766 8475 8558 807
766 2441 8553 807
766 2465 8558 809
766 8478 8557 809
766 2432 8553 812
766 8485 8558 812
766 8484 8557 812
766 8483 8560 812
766 2455 8553 813
766 8488 8558 813
766 8487 8557 813
766 8486 8560 813
766 8490 8558 814
766 8489 8557 814
766 2438 8551 814
766 2459 8558 815
766 8492 8551 815
766 8491 8560 815
766 8493 8557 815
766 6590 8556 816
766 8494 8553 816
766 8495 8551 817
766 1538 8558 817
766 8496 8557 817
766 1517 8557 818
766 8498 8558 818
766 8497 8805 818
766 8500 8551 819
766 1532 8558 819
766 8502 8553 819
766 8501 8557 819
766 8499 8556 819
766 8504 8557 820
766 1528 8558 820
766 8505 8553 820
766 8503 8556 820
766 6336 8558 821
766 8506 8553 821
766 6373 8557 822
766 8509 8553 822
766 8508 8558 822
766 8507 8556 822
766 4949 8551 823
766 8510 8560 823
766 8511 8557 823
766 8513 8553 823
766 8512 8558 823
766 6459 8556 838
766 8712 8553 838
766 8711 8558 838
766 8710 8911 838
766 6447 8558 839
766 8715 8557 839
766 8714 8551 839
766 8713 8560 839
766 6451 8556 840
766 8717 8557 840
766 8716 8551 840
766 6440 8556 841
766 8720 8553 841
766 8719 8558 841
766 8718 8557 841
766 6444 8553 842
766 8722 8558 842
766 8721 8557 842
766 8947 8558 867
766 8946 8553 867
766 10122 8551 956
766 10127 8557 956
766 10128 8558 956
766 10133 8556 956
766 10135 8557 958
766 10136 8805 958
766 10137 8806 958
766 10138 8558 958
766 10198 8557 966
766 10199 8556 966
766 10200 8558 966
766 10201 8553 966
766 10217 8553 969
766 10216 8558 969
766 10309 8556 969
766 10311 8553 980
766 10310 8558 980
766 10312 8557 981
766 10315 8558 981
766 10317 8553 981
766 10313 8557 982
766 10316 8558 982
766 10318 8553 982
766 10322 8556 982
766 10314 8557 983
766 10319 8558 983
766 10320 8553 983
766 10321 8556 983