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 (
    146, 141, 144, 143, 148, 147, 145, 149, 
    142, 151, 150
  )

Query time 0.00045

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.06419951,
    "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": 26,
          "cost": 0.01451805,
          "filtered": 100,
          "attached_condition": "gp.group_id in (146,141,144,143,148,147,145,149,142,151,150)",
          "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": "product_id",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["u508912950_dev_2025.gp.product_id"],
          "loops": 26,
          "rows": 1,
          "cost": 0.02612773,
          "filtered": 100,
          "attached_condition": "pfv.lang_code = 'en'",
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "gpf",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "idx_group_id",
          "key_length": "6",
          "used_key_parts": ["group_id", "feature_id"],
          "ref": [
            "u508912950_dev_2025.gp.group_id",
            "u508912950_dev_2025.pfv.feature_id"
          ],
          "loops": 26,
          "rows": 1,
          "cost": 0.02355373,
          "filtered": 100,
          "using_index": true
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
607 13902 12624 141
607 13903 12627 141
607 13906 12624 142
607 13907 12627 142
607 13909 12627 143
607 13910 12628 143
607 13911 12630 143
607 13913 12624 144
607 13914 12627 144
607 13915 12632 144
607 13917 12624 145
607 13918 12627 145
607 13919 12624 146
607 13920 12627 146
607 13921 12636 147
607 13922 12638 147
607 13923 12640 147
607 13924 12642 148
607 13925 12643 148
607 13926 12624 149
607 13927 12634 149
607 13933 12646 150
607 13934 12648 150
607 13935 12650 150
607 13936 12652 151
607 13937 12654 151