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

Query time 0.00238

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "71.05"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gpf",
          "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": 7,
          "rows_produced_per_join": 7,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.75",
            "eval_cost": "0.70",
            "prefix_cost": "2.45",
            "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": [
            "const"
          ],
          "rows_examined_per_scan": 14,
          "rows_produced_per_join": 98,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "24.50",
            "eval_cost": "9.80",
            "prefix_cost": "36.75",
            "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": [
            "gaseus.gpf.feature_id",
            "gaseus.gp.product_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 98,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "24.50",
            "eval_cost": "9.80",
            "prefix_cost": "71.05",
            "data_read_per_join": "75K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
627 7492 1659 809
627 7493 1659 809
627 5894 1659 809
627 2626 1659 809
627 6129 1659 809
627 2466 1659 809
627 2358 1659 809
627 2467 1659 809
627 2295 1659 809
627 2537 1659 809
627 6066 1659 809
627 5958 1659 809
627 6067 1659 809
627 6219 1659 809
623 7492 2002 809
623 7493 2002 809
623 5894 2002 809
623 2626 2002 809
623 6129 2002 809
623 2466 2002 809
623 2358 2002 809
623 2467 2002 809
623 2295 2002 809
623 2537 2002 809
623 6066 2002 809
623 5958 2002 809
623 6067 2002 809
623 6219 2002 809
622 7492 2029 809
622 7493 2029 809
622 5894 2029 809
622 2626 2029 809
622 6129 2029 809
622 2466 2029 809
622 2358 2029 809
622 2467 2029 809
622 2295 2029 809
622 2537 2029 809
622 6066 2029 809
622 5958 2029 809
622 6067 2029 809
622 6219 2029 809
626 7492 2043 809
626 7493 2043 809
626 5894 2043 809
626 2626 2043 809
626 6129 2043 809
626 2466 2043 809
626 2358 2043 809
626 2467 2043 809
626 2295 2043 809
626 2537 2043 809
626 6066 2043 809
626 5958 2043 809
626 6067 2043 809
626 6219 2043 809
553 7492 1973 809
553 7493 1912 809
553 5894 1912 809
553 2626 1912 809
553 6129 1912 809
553 2466 1912 809
553 2358 1912 809
553 2467 1912 809
553 2295 1973 809
553 2537 1973 809
553 6066 1973 809
553 5958 1973 809
553 6067 1973 809
553 6219 1973 809
625 7492 2334 809
625 7493 2334 809
625 5894 2334 809
625 2626 2334 809
625 6129 2334 809
625 2466 2334 809
625 2358 2334 809
625 2467 2334 809
625 2295 2334 809
625 2537 2334 809
625 6066 2334 809
625 5958 2334 809
625 6067 2334 809
625 6219 2334 809
624 7492 1803 809
624 7493 1803 809
624 5894 1803 809
624 2626 1803 809
624 6129 1803 809
624 2466 1803 809
624 2358 1803 809
624 2467 1803 809
624 2295 1803 809
624 2537 1803 809
624 6066 1803 809
624 5958 1803 809
624 6067 1803 809
624 6219 1803 809
628 7492 1660 809
628 7493 1660 809
628 5894 1660 809
628 2626 1660 809
628 6129 1660 809
628 2466 1660 809
628 2358 1660 809
628 2467 1660 809
628 2295 1660 809
628 2537 1660 809
628 6066 1660 809
628 5958 1660 809
628 6067 1660 809
628 6219 1660 809
595 7492 1748 809
595 7493 1748 809
595 5894 1904 809
595 2626 1905 809
595 6129 1900 809
595 2466 1787 809
595 2358 1899 809
595 2467 1789 809
595 2295 1904 809
595 2537 1900 809
595 6066 1787 809
595 5958 1899 809
595 6067 1789 809
595 6219 1905 809