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

Query time 0.00146

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "39.90"
    },
    "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": 28,
          "rows_produced_per_join": 56,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "14.00",
            "eval_cost": "5.60",
            "prefix_cost": "20.30",
            "data_read_per_join": "896"
          },
          "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": 56,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "14.00",
            "eval_cost": "5.60",
            "prefix_cost": "39.90",
            "data_read_per_join": "43K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ]
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
553 11909 1324 516
553 11910 1324 516
553 11911 1324 516
553 11912 1324 516
553 11913 1324 516
553 11914 1324 516
553 11915 1324 516
553 11916 1324 516
553 11917 1324 516
553 11918 1324 516
553 11919 2197 516
553 11920 2197 516
553 11921 2197 516
553 11922 2197 516
553 11923 2197 516
553 11924 2197 516
553 11925 2197 516
553 11926 2197 516
553 11927 2197 516
553 11928 2197 516
623 11909 2094 516
623 11910 2095 516
623 11911 2096 516
623 11912 2084 516
623 11913 2085 516
623 11914 2086 516
623 11915 2087 516
623 11916 2088 516
623 11917 2089 516
623 11918 2090 516
623 11919 2094 516
623 11920 2095 516
623 11921 2096 516
623 11922 2084 516
623 11923 2085 516
623 11924 2086 516
623 11925 2087 516
623 11926 2088 516
623 11927 2089 516
623 11928 2090 516