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 (
    153378, 
    146250, 
    151676, 
    170573, 
    132911, 
    151528, 
    168300, 
    146008, 
    149505, 
    137270, 
    118463, 
    175108, 
    120360, 
    125752, 
    145670, 
    165520, 
    164728, 
    168291, 
    3459, 
    135232, 
    134222, 
    163348, 
    147116, 
    141352, 
    173196, 
    133367, 
    136278, 
    164224, 
    169761, 
    120371, 
    126781, 
    174454, 
    159721, 
    122698, 
    146691, 
    132361, 
    145504, 
    151584, 
    163029, 
    142300, 
    148407, 
    159680, 
    166851, 
    135515, 
    158366, 
    167558, 
    118856, 
    150294, 
    120895, 
    164939, 
    148060, 
    170707, 
    154297, 
    169846, 
    159381, 
    165473, 
    152983, 
    168016, 
    159560, 
    157441, 
    160602, 
    125438, 
    174205, 
    125076, 
    141675, 
    141823, 
    120656, 
    126863, 
    158696, 
    145139, 
    137529, 
    141871, 
    139050, 
    175166, 
    171127, 
    120496, 
    169901, 
    138735, 
    146824, 
    158471, 
    162470, 
    154893, 
    146279, 
    121442, 
    125787, 
    120264, 
    138776, 
    164451, 
    138985, 
    121657, 
    139237, 
    172341, 
    169627, 
    121067, 
    122178, 
    131028, 
    133688, 
    135620, 
    129139, 
    156331, 
    177744, 
    160951, 
    160534, 
    171706, 
    128069, 
    163441, 
    131982, 
    170708, 
    136526, 
    152889, 
    146907, 
    169481, 
    172066, 
    165323, 
    120928, 
    143092, 
    160218, 
    146004, 
    169267, 
    151452, 
    165666, 
    149523, 
    131594, 
    129845, 
    133281, 
    148051, 
    163097, 
    144228
  )

Query time 0.00051

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.05491401,
    "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_abhi.g.id"],
          "loops": 2,
          "rows": 2,
          "cost": 0.0072656,
          "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_abhi.g.id"],
          "loops": 4,
          "rows": 9,
          "cost": 0.04070912,
          "filtered": 100,
          "index_condition": "p.product_id in (153378,146250,151676,170573,132911,151528,168300,146008,149505,137270,118463,175108,120360,125752,145670,165520,164728,168291,3459,135232,134222,163348,147116,141352,173196,133367,136278,164224,169761,120371,126781,174454,159721,122698,146691,132361,145504,151584,163029,142300,148407,159680,166851,135515,158366,167558,118856,150294,120895,164939,148060,170707,154297,169846,159381,165473,152983,168016,159560,157441,160602,125438,174205,125076,141675,141823,120656,126863,158696,145139,137529,141871,139050,175166,171127,120496,169901,138735,146824,158471,162470,154893,146279,121442,125787,120264,138776,164451,138985,121657,139237,172341,169627,121067,122178,131028,133688,135620,129139,156331,177744,160951,160534,171706,128069,163441,131982,170708,136526,152889,146907,169481,172066,165323,120928,143092,160218,146004,169267,151452,165666,149523,131594,129845,133281,148051,163097,144228)"
        }
      }
    ]
  }
}