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

Query time 0.00052

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "16.50"
    },
    "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": 8,
          "rows_produced_per_join": 16,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "4.00",
            "eval_cost": "1.60",
            "prefix_cost": "6.30",
            "data_read_per_join": "256"
          },
          "used_columns": [
            "product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "lang_code",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id",
            "fl"
          ],
          "key": "product_id",
          "used_key_parts": [
            "product_id",
            "feature_id"
          ],
          "key_length": "6",
          "ref": [
            "cscart_db.gp.product_id",
            "cscart_db.gpf.feature_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 10,
          "filtered": "50.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "8.15",
            "eval_cost": "1.03",
            "prefix_cost": "16.50",
            "data_read_per_join": "410"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id",
            "lang_code"
          ],
          "attached_condition": "(`cscart_db`.`pfv`.`lang_code` = 'en')"
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
102 4729 27789 535
102 4734 22146 535
102 4733 22146 535
102 4732 22146 535
102 4728 27789 535
102 4730 27789 535
102 4731 22146 535
102 4727 27789 535
103 4729 27865 535
103 4734 25210 535
103 4733 27865 535
103 4732 27864 535
103 4728 27864 535
103 4730 25210 535
103 4731 27333 535
103 4727 27333 535