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 (
    5750, 
    7013, 
    5739, 
    5727, 
    4432, 
    1553, 
    2574, 
    1, 
    228, 
    247, 
    944, 
    478, 
    8, 
    985, 
    3754, 
    525, 
    2478, 
    1552, 
    10284, 
    6151, 
    1274, 
    908, 
    1930, 
    1557, 
    1554, 
    1560, 
    1556, 
    2527, 
    1561, 
    1134, 
    5696, 
    4022, 
    2490, 
    9, 
    2698, 
    1406, 
    5721, 
    508, 
    1555, 
    3090, 
    2372, 
    1587, 
    4013, 
    5621, 
    4416, 
    1266, 
    5724, 
    3974, 
    851, 
    10, 
    1558, 
    1126, 
    4067, 
    8483, 
    3605, 
    2509, 
    2752, 
    71, 
    1428, 
    1263, 
    3883, 
    2471, 
    2576, 
    5944, 
    4143, 
    2395, 
    2620, 
    4399, 
    1589, 
    4279, 
    1143, 
    1256, 
    3776, 
    4951, 
    4, 
    1584, 
    2359, 
    1319, 
    1577, 
    2310, 
    4219, 
    1273, 
    5830, 
    2340, 
    4526, 
    1559, 
    2502, 
    5887, 
    2187, 
    2473, 
    2508, 
    4463, 
    1532, 
    1568, 
    2565, 
    2300, 
    2580, 
    3354, 
    1591, 
    2638, 
    4215, 
    3155, 
    3834, 
    2301, 
    4294, 
    4316, 
    2737, 
    2550, 
    1590, 
    2275, 
    2559, 
    1564, 
    3308, 
    1579, 
    2363, 
    4126, 
    4796, 
    1567, 
    1583, 
    4562, 
    5892, 
    3306, 
    1578, 
    5625, 
    5848, 
    2845, 
    1585, 
    1573, 
    1572, 
    1574, 
    8499, 
    2599, 
    1549, 
    2822, 
    8502, 
    8501, 
    1592, 
    4170, 
    2640, 
    2399, 
    1576, 
    2646, 
    1665, 
    1586, 
    3397, 
    2352, 
    2503, 
    2602, 
    2459, 
    1593, 
    3363, 
    2431, 
    5899, 
    2201, 
    5934, 
    5939, 
    3929, 
    5918, 
    3841, 
    4172
  )

Query time 0.00257

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "236.66"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "p",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 171,
          "rows_produced_per_join": 171,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`p`.`product_id` in (5750,7013,5739,5727,4432,1553,2574,1,228,247,944,478,8,985,3754,525,2478,1552,10284,6151,1274,908,1930,1557,1554,1560,1556,2527,1561,1134,5696,4022,2490,9,2698,1406,5721,508,1555,3090,2372,1587,4013,5621,4416,1266,5724,3974,851,10,1558,1126,4067,8483,3605,2509,2752,71,1428,1263,3883,2471,2576,5944,4143,2395,2620,4399,1589,4279,1143,1256,3776,4951,4,1584,2359,1319,1577,2310,4219,1273,5830,2340,4526,1559,2502,5887,2187,2473,2508,4463,1532,1568,2565,2300,2580,3354,1591,2638,4215,3155,3834,2301,4294,4316,2737,2550,1590,2275,2559,1564,3308,1579,2363,4126,4796,1567,1583,4562,5892,3306,1578,5625,5848,2845,1585,1573,1572,1574,8499,2599,1549,2822,8502,8501,1592,4170,2640,2399,1576,2646,1665,1586,3397,2352,2503,2602,2459,1593,3363,2431,5899,2201,5934,5939,3929,5918,3841,4172))",
          "cost_info": {
            "read_cost": "99.86",
            "eval_cost": "17.10",
            "prefix_cost": "116.96",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 171,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "42.75",
            "eval_cost": "17.10",
            "prefix_cost": "176.81",
            "data_read_per_join": "66K"
          },
          "used_columns": [
            "id",
            "code"
          ]
        }
      },
      {
        "table": {
          "table_name": "f",
          "access_type": "ref",
          "possible_keys": [
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 171,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "42.75",
            "eval_cost": "17.10",
            "prefix_cost": "236.66",
            "data_read_per_join": "17K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}