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

Query time 0.00126

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "18.26"
    },
    "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": 6,
          "rows_produced_per_join": 6,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.50",
            "eval_cost": "0.60",
            "prefix_cost": "2.10",
            "data_read_per_join": "96"
          },
          "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": 22,
          "filtered": "54.46",
          "using_index": true,
          "cost_info": {
            "read_cost": "3.96",
            "eval_cost": "2.29",
            "prefix_cost": "10.26",
            "data_read_per_join": "17K"
          },
          "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": 22,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "5.72",
            "eval_cost": "2.29",
            "prefix_cost": "18.26",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "feature_id",
            "group_id"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 12915 1973 886
595 12915 1462 886
622 12915 2485 886
623 12915 2486 886
624 12915 1657 886
625 12915 2124 886
626 12915 1723 886
627 12915 1659 886
628 12915 1660 886
636 12915 2344 886
637 12915 2345 886
553 12914 1973 886
595 12914 2455 886
622 12914 2489 886
623 12914 2490 886
624 12914 1657 886
625 12914 2124 886
626 12914 1723 886
627 12914 1659 886
628 12914 1660 886
636 12914 2344 886
637 12914 2345 886
553 12913 1973 886
595 12913 1900 886
622 12913 2489 886
623 12913 2490 886
624 12913 1657 886
625 12913 2124 886
626 12913 1723 886
627 12913 1659 886
628 12913 1660 886
636 12913 2344 886
637 12913 2345 886
553 12912 1973 886
595 12912 1905 886
622 12912 2485 886
623 12912 2486 886
624 12912 1657 886
625 12912 2124 886
626 12912 1723 886
627 12912 1659 886
628 12912 1660 886
636 12912 2344 886
637 12912 2345 886
553 12911 1973 886
595 12911 1787 886
622 12911 2485 886
623 12911 2486 886
624 12911 1657 886
625 12911 2124 886
626 12911 1723 886
627 12911 1659 886
628 12911 1660 886
636 12911 2344 886
637 12911 2345 886
553 12910 1973 886
595 12910 2451 886
622 12910 2485 886
623 12910 2486 886
624 12910 1657 886
625 12910 2124 886
626 12910 1723 886
627 12910 1659 886
628 12910 1660 886
636 12910 2344 886
637 12910 2345 886
553 12909 1973 886
595 12909 1904 886
622 12909 2485 886
623 12909 2486 886
624 12909 1657 886
625 12909 2124 886
626 12909 1723 886
627 12909 1659 886
628 12909 1660 886
636 12909 2344 886
637 12909 2345 886