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

Query time 0.00053

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "35.70"
    },
    "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": 2,
          "rows_produced_per_join": 2,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.50",
            "eval_cost": "0.20",
            "prefix_cost": "0.70",
            "data_read_per_join": "208"
          },
          "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": 25,
          "rows_produced_per_join": 50,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "12.50",
            "eval_cost": "5.00",
            "prefix_cost": "18.20",
            "data_read_per_join": "800"
          },
          "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": 50,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "12.50",
            "eval_cost": "5.00",
            "prefix_cost": "35.70",
            "data_read_per_join": "38K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 11869 1324 514
553 11870 1324 514
553 11871 1324 514
553 11872 1324 514
553 11873 1324 514
553 11874 1324 514
553 11875 1324 514
553 11876 1324 514
553 11877 1324 514
553 11878 2197 514
553 11879 2197 514
553 11880 2197 514
553 11881 2197 514
553 11882 2197 514
553 11883 2197 514
553 11884 2197 514
553 11885 2197 514
553 11886 2197 514
623 11869 2002 514
623 11870 2092 514
623 11871 2099 514
623 11872 2083 514
623 11873 2100 514
623 11874 2085 514
623 11875 2101 514
623 11876 2087 514
623 11877 2088 514
623 11878 2002 514
623 11879 2092 514
623 11880 2099 514
623 11881 2083 514
623 11882 2100 514
623 11883 2085 514
623 11884 2101 514
623 11885 2087 514
623 11886 2088 514