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 (697, 989)

Query time 0.00106

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.16"
    },
    "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": 3,
          "rows_produced_per_join": 3,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`gpf`.`group_id` in (697,989))",
          "cost_info": {
            "read_cost": "1.56",
            "eval_cost": "0.30",
            "prefix_cost": "1.86",
            "data_read_per_join": "312"
          },
          "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": 9,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.25",
            "eval_cost": "0.90",
            "prefix_cost": "5.01",
            "data_read_per_join": "144"
          },
          "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": 9,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.25",
            "eval_cost": "0.90",
            "prefix_cost": "8.16",
            "data_read_per_join": "6K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
656 6261 3612 697
656 6262 3615 697
656 6263 3618 697
656 6264 4850 697
656 6270 3607 697
704 10356 9231 989
704 10364 9231 989
656 10356 1558 989
656 10364 6283 989