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 (
    5696, 
    5621, 
    5892, 
    5719, 
    5706, 
    5720, 
    5703, 
    4894, 
    4962, 
    5691, 
    5687, 
    3733, 
    4915, 
    3589, 
    4976, 
    3811, 
    6093, 
    6194, 
    4935, 
    3734, 
    3641, 
    5700, 
    6064, 
    3798, 
    4931, 
    3730, 
    3638, 
    3729, 
    3851, 
    6204, 
    3942, 
    3732, 
    3448, 
    3728, 
    3852, 
    3850, 
    3731, 
    3642, 
    3943, 
    3623, 
    3645, 
    3637, 
    3950, 
    6074, 
    3644, 
    3864, 
    3940, 
    3865, 
    6117, 
    3869, 
    3948, 
    3941, 
    6068, 
    3951, 
    5812, 
    3949, 
    6097, 
    8612, 
    6073, 
    8464, 
    8469, 
    10025, 
    8724, 
    8445, 
    8811, 
    8441, 
    8446, 
    8440, 
    8593, 
    8595, 
    8444, 
    8471, 
    8594, 
    8457, 
    8828, 
    8466, 
    8831, 
    8276, 
    8365, 
    8585, 
    8615, 
    8729, 
    8730, 
    8797, 
    8810, 
    8814, 
    8829, 
    8244, 
    8277, 
    8377, 
    8403, 
    8431, 
    8587, 
    8798, 
    8271, 
    8274, 
    8278, 
    8333, 
    8389, 
    8430, 
    8516, 
    8518, 
    8537, 
    8755, 
    10044, 
    8334, 
    8383, 
    8462, 
    8539, 
    10046, 
    8401, 
    8386, 
    11588, 
    11597, 
    11608, 
    11580, 
    11609, 
    11589, 
    11591, 
    11596, 
    11604, 
    11611, 
    11582, 
    11584, 
    11586, 
    11595, 
    11606, 
    11603
  )

Query time 0.00158

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "181.51"
    },
    "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": 130,
          "rows_produced_per_join": 130,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`p`.`product_id` in (5696,5621,5892,5719,5706,5720,5703,4894,4962,5691,5687,3733,4915,3589,4976,3811,6093,6194,4935,3734,3641,5700,6064,3798,4931,3730,3638,3729,3851,6204,3942,3732,3448,3728,3852,3850,3731,3642,3943,3623,3645,3637,3950,6074,3644,3864,3940,3865,6117,3869,3948,3941,6068,3951,5812,3949,6097,8612,6073,8464,8469,10025,8724,8445,8811,8441,8446,8440,8593,8595,8444,8471,8594,8457,8828,8466,8831,8276,8365,8585,8615,8729,8730,8797,8810,8814,8829,8244,8277,8377,8403,8431,8587,8798,8271,8274,8278,8333,8389,8430,8516,8518,8537,8755,10044,8334,8383,8462,8539,10046,8401,8386,11588,11597,11608,11580,11609,11589,11591,11596,11604,11611,11582,11584,11586,11595,11606,11603))",
          "cost_info": {
            "read_cost": "77.51",
            "eval_cost": "13.00",
            "prefix_cost": "90.51",
            "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": 130,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "32.50",
            "eval_cost": "13.00",
            "prefix_cost": "136.01",
            "data_read_per_join": "50K"
          },
          "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": 130,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "32.50",
            "eval_cost": "13.00",
            "prefix_cost": "181.51",
            "data_read_per_join": "13K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}