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 (814, 769, 815, 773, 770, 766, 767)

Query time 0.00102

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19.61"
    },
    "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": 7,
          "rows_produced_per_join": 7,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gpf`.`group_id` in (814,769,815,773,770,766,767))",
          "cost_info": {
            "read_cost": "4.21",
            "eval_cost": "0.70",
            "prefix_cost": "4.91",
            "data_read_per_join": "728"
          },
          "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": 21,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "5.25",
            "eval_cost": "2.10",
            "prefix_cost": "12.26",
            "data_read_per_join": "336"
          },
          "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": 21,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "5.25",
            "eval_cost": "2.10",
            "prefix_cost": "19.61",
            "data_read_per_join": "16K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
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 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 7293 8560 773
766 7625 8559 773
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