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 (
    163, 56, 161, 150, 167, 57, 493, 169, 168, 
    172
  )

Query time 0.00052

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.07135077,
    "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": 30,
          "cost": 0.01427807,
          "filtered": 100,
          "attached_condition": "gp.group_id in (163,56,161,150,167,57,493,169,168,172)",
          "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": 30,
          "rows": 1,
          "cost": 0.03002135,
          "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": 30,
          "rows": 1,
          "cost": 0.02705135,
          "filtered": 100,
          "using_index": true
        }
      }
    ]
  }
}

Result

feature_id product_id variant_id group_id
601 13464 12566 56
601 13492 12568 56
601 13465 12566 57
601 13493 12568 57
607 13933 12646 150
607 13934 12648 150
607 13935 12650 150
618 13998 12879 161
618 13999 12881 161
618 14000 12883 161
618 14005 12893 163
618 14006 12829 163
618 14007 12895 163
620 14130 12919 167
620 14131 12920 167
620 14132 12921 167
622 14133 12923 168
622 14134 12924 168
622 14135 12925 168
624 14138 12927 169
624 14139 12928 169
549 15734 12535 172
549 15735 12532 172
549 15736 12538 172
549 15737 12539 172
549 15738 12528 172
549 15739 13062 172
549 15740 13063 172
665 19930 13380 493
665 19931 13186 493