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 (885)

Query time 0.00121

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "27.39"
    },
    "nested_loop": [
      {
        "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": [
            "const"
          ],
          "rows_examined_per_scan": 9,
          "rows_produced_per_join": 9,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.25",
            "eval_cost": "0.90",
            "prefix_cost": "3.15",
            "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": "idx_product_feature_variant_id",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "test2_gaseus.gp.product_id"
          ],
          "rows_examined_per_scan": 7,
          "rows_produced_per_join": 34,
          "filtered": "54.46",
          "using_index": true,
          "cost_info": {
            "read_cost": "5.93",
            "eval_cost": "3.43",
            "prefix_cost": "15.38",
            "data_read_per_join": "26K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ],
          "attached_condition": "(`test2_gaseus`.`pfv`.`lang_code` = 'en')"
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id",
            "group_id"
          ],
          "key_length": "6",
          "ref": [
            "test2_gaseus.pfv.feature_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 34,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "8.58",
            "eval_cost": "3.43",
            "prefix_cost": "27.39",
            "data_read_per_join": "3K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 12908 1973 885
595 12908 1902 885
622 12908 2489 885
623 12908 2490 885
624 12908 1675 885
625 12908 2139 885
626 12908 2003 885
627 12908 1659 885
628 12908 1660 885
636 12908 2344 885
637 12908 2345 885
553 12907 1973 885
595 12907 1462 885
622 12907 2485 885
623 12907 2486 885
624 12907 1675 885
625 12907 2123 885
626 12907 1721 885
627 12907 1659 885
628 12907 1660 885
636 12907 2344 885
637 12907 2345 885
553 12906 1973 885
595 12906 2455 885
622 12906 2489 885
623 12906 2490 885
624 12906 1675 885
625 12906 2123 885
626 12906 1721 885
627 12906 1659 885
628 12906 1660 885
636 12906 2344 885
637 12906 2345 885
553 12905 1973 885
595 12905 1900 885
622 12905 2489 885
623 12905 2490 885
624 12905 1675 885
625 12905 2123 885
626 12905 1721 885
627 12905 1659 885
628 12905 1660 885
636 12905 2344 885
637 12905 2345 885
553 12900 1973 885
595 12900 1904 885
622 12900 2485 885
623 12900 2486 885
624 12900 1675 885
625 12900 2123 885
626 12900 1721 885
627 12900 1659 885
628 12900 1660 885
636 12900 2344 885
637 12900 2345 885
553 12901 1973 885
595 12901 2000 885
622 12901 2489 885
623 12901 2490 885
624 12901 2447 885
625 12901 2413 885
626 12901 1718 885
627 12901 1659 885
628 12901 1660 885
636 12901 2344 885
637 12901 2345 885
553 12902 1973 885
595 12902 2451 885
622 12902 2485 885
623 12902 2486 885
624 12902 1675 885
625 12902 2123 885
626 12902 1721 885
627 12902 1659 885
628 12902 1660 885
636 12902 2344 885
637 12902 2345 885
553 12903 1973 885
595 12903 1787 885
622 12903 2485 885
623 12903 2486 885
624 12903 1675 885
625 12903 2123 885
626 12903 1721 885
627 12903 1659 885
628 12903 1660 885
636 12903 2344 885
637 12903 2345 885
553 12904 1973 885
595 12904 1905 885
622 12904 2485 885
623 12904 2486 885
624 12904 1675 885
625 12904 2123 885
626 12904 1721 885
627 12904 1659 885
628 12904 1660 885
636 12904 2344 885
637 12904 2345 885