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 (
    153, 143, 148, 147, 139, 132, 166, 157, 
    155, 134, 159, 160
  )

Query time 0.00066

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.130844565,
    "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": 59,
          "cost": 0.020185375,
          "filtered": 100,
          "attached_condition": "gp.group_id in (153,143,148,147,139,132,166,157,155,134,159,160)",
          "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": 59,
          "rows": 1,
          "cost": 0.058250095,
          "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": 59,
          "rows": 1,
          "cost": 0.052409095,
          "filtered": 100,
          "using_index": true
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
617 13866 12777 132
617 13867 12779 132
617 13868 12770 132
617 13869 12775 132
617 13873 12788 134
617 13874 12790 134
617 13875 12792 134
617 13897 12808 139
617 13898 12810 139
607 13909 12627 143
607 13910 12628 143
607 13911 12630 143
607 13921 12636 147
607 13922 12638 147
607 13923 12640 147
607 13924 12642 148
607 13925 12643 148
609 13944 12660 153
609 13945 12661 153
618 13951 12819 155
618 13952 12821 155
618 13953 12823 155
618 13954 12825 155
618 13955 12827 155
618 13956 12829 155
618 13957 12831 155
618 13958 12833 155
618 13959 12835 155
618 13960 12837 155
618 13961 12839 155
618 13962 12841 155
618 13969 12854 157
618 13970 12856 157
618 13974 12823 159
618 13975 12825 159
618 13976 12829 159
618 13977 12864 159
618 13978 12831 159
618 13979 12866 159
618 13980 12868 159
618 13981 12833 159
618 13982 12870 159
618 13983 12872 159
618 13984 12837 159
618 13985 12873 159
618 13986 12875 159
618 13987 12877 159
618 13988 12839 159
618 13989 12823 160
618 13990 12829 160
618 13991 12864 160
618 13992 12831 160
618 13993 12870 160
618 13994 12837 160
618 13995 12875 160
618 13996 12877 160
618 13997 12841 160
613 14021 12764 166
613 14022 12766 166