SELECT 
  f.feature_id, 
  f.purpose, 
  p.product_id, 
  p.parent_product_id, 
  g.id, 
  g.code 
FROM 
  cscart_product_variation_group_features AS f 
  INNER JOIN cscart_product_variation_groups AS g ON f.group_id = g.id 
  INNER JOIN cscart_product_variation_group_products AS p ON f.group_id = p.group_id 
WHERE 
  p.product_id IN (
    461277, 461276, 461275, 461274, 461273, 
    461272, 461271, 461270, 461269, 461268, 
    461267, 461266, 461265, 461264, 461263, 
    461262, 461261, 461260, 461259, 461258, 
    461257, 461256, 461255, 461254, 461253, 
    461252, 461251, 461250, 461249, 461248, 
    461247, 461246, 461245, 461244, 461243, 
    461242, 461218, 461039, 461038, 461037, 
    461036, 461035, 461034, 461033, 461032, 
    461031, 461030, 461029, 461026, 461025, 
    461022, 461020, 461018, 461017, 461013, 
    461010, 461009, 461008, 460961, 460958, 
    460957, 460955, 460952, 460951, 460948, 
    460947, 460946, 460918, 460921, 460936, 
    460804, 460816, 460828, 460835, 460856, 
    460859, 460861, 460869, 460870, 460873, 
    460888, 460902, 460734, 460754, 460758, 
    460765, 460774, 460778, 460795, 460638, 
    460693, 460698, 460504, 460515, 460518, 
    460521
  )

Query time 0.00052

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.03140489,
    "nested_loop": [
      {
        "table": {
          "table_name": "g",
          "access_type": "index",
          "possible_keys": ["PRIMARY"],
          "key": "idx_group_code",
          "key_length": "387",
          "used_key_parts": ["code"],
          "loops": 1,
          "rows": 2,
          "cost": 0.00693929,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "f",
          "access_type": "ref",
          "possible_keys": ["idx_group_id"],
          "key": "idx_group_id",
          "key_length": "3",
          "used_key_parts": ["group_id"],
          "ref": ["u985510652_ecartify.g.id"],
          "loops": 2,
          "rows": 1,
          "cost": 0.00527872,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "p",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "idx_group_id",
          "key_length": "3",
          "used_key_parts": ["group_id"],
          "ref": ["u985510652_ecartify.g.id"],
          "loops": 2,
          "rows": 8,
          "cost": 0.01918688,
          "filtered": 100,
          "index_condition": "p.product_id in (461277,461276,461275,461274,461273,461272,461271,461270,461269,461268,461267,461266,461265,461264,461263,461262,461261,461260,461259,461258,461257,461256,461255,461254,461253,461252,461251,461250,461249,461248,461247,461246,461245,461244,461243,461242,461218,461039,461038,461037,461036,461035,461034,461033,461032,461031,461030,461029,461026,461025,461022,461020,461018,461017,461013,461010,461009,461008,460961,460958,460957,460955,460952,460951,460948,460947,460946,460918,460921,460936,460804,460816,460828,460835,460856,460859,460861,460869,460870,460873,460888,460902,460734,460754,460758,460765,460774,460778,460795,460638,460693,460698,460504,460515,460518,460521)"
        }
      }
    ]
  }
}