Sandbox

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 (63, 38, 29, 57, 34, 39, 35, 37, 58, 40)

Query time 0.00056

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.08770094,
    "nested_loop": [
      {
        "table": {
          "table_name": "gp",
          "access_type": "range",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "idx_group_id",
          "key_length": "3",
          "used_key_parts": ["group_id"],
          "loops": 1,
          "rows": 36,
          "cost": 0.01515818,
          "filtered": 100,
          "attached_condition": "gp.group_id in (63,38,29,57,34,39,35,37,58,40)",
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "idx_group_id",
          "key_length": "3",
          "used_key_parts": ["group_id"],
          "ref": ["u428615623_ecartifygonje.gp.group_id"],
          "loops": 36,
          "rows": 1,
          "cost": 0.03586178,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "pfv",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "lang_code",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "lang_code",
          "key_length": "12",
          "used_key_parts": ["lang_code", "feature_id", "product_id"],
          "ref": [
            "const",
            "u428615623_ecartifygonje.gpf.feature_id",
            "u428615623_ecartifygonje.gp.product_id"
          ],
          "loops": 36,
          "rows": 1,
          "cost": 0.03668098,
          "filtered": 100,
          "attached_condition": "pfv.lang_code = 'en'",
          "using_index": true
        }
      }
    ]
  }
}

Result

feature_id	product_id	variant_id	group_id
37	165	5699	29
37	205	5700	29
37	206	5701	29
37	236	5707	29
37	237	5708	29
37	221	5699	34
37	222	5700	34
37	223	5701	34
37	238	5707	34
37	239	5708	34
37	224	5699	35
37	225	5700	35
37	226	5701	35
37	240	5707	35
37	241	5708	35
39	228	5706	37
39	229	5705	37
40	196	5709	38
40	230	5710	38
40	231	5711	38
40	193	5709	39
40	232	5710	39
40	233	5711	39
40	200	5709	40
40	234	5710	40
40	235	5711	40
32	701	5674	57
32	702	5673	57
32	708	5675	57
50	727	5760	58
51	727	5762	58
50	728	5761	58
51	728	5762	58
50	729	5761	58
51	729	5763	58
50	730	5759	58
51	730	5762	58
50	731	5759	58
51	731	5763	58
51	813	5762	63
51	814	5763	63