SELECT 
  p.product_id, 
  ifnull(
    count(dp.post_id), 
    0
  ) as discussion_amount_posts 
FROM 
  cscart_discussion 
  INNER JOIN cscart_products as p ON (
    cscart_discussion.object_id = p.product_id
  ) 
  INNER JOIN cscart_discussion_posts as dp ON (
    cscart_discussion.thread_id = dp.thread_id 
    AND cscart_discussion.object_type = 'P'
  ) 
WHERE 
  dp.status = 'A' 
  and 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
  ) 
GROUP BY 
  p.product_id

Query time 0.00252

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.31"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "dp",
            "access_type": "ALL",
            "possible_keys": [
              "thread_id",
              "thread_id_2"
            ],
            "rows_examined_per_scan": 13,
            "rows_produced_per_join": 1,
            "filtered": "10.00",
            "cost_info": {
              "read_cost": "1.70",
              "eval_cost": "0.13",
              "prefix_cost": "1.83",
              "data_read_per_join": "582"
            },
            "used_columns": [
              "post_id",
              "thread_id",
              "status"
            ],
            "attached_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`dp`.`status` = 'A')"
          }
        },
        {
          "table": {
            "table_name": "cscart_discussion",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "object_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "thread_id"
            ],
            "key_length": "3",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.dp.thread_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "0.33",
              "eval_cost": "0.01",
              "prefix_cost": "2.28",
              "data_read_per_join": "1"
            },
            "used_columns": [
              "thread_id",
              "object_id",
              "object_type"
            ],
            "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_discussion`.`object_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)) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_discussion`.`object_type` = 'P'))"
          }
        },
        {
          "table": {
            "table_name": "p",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.cscart_discussion.object_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "0.02",
              "eval_cost": "0.01",
              "prefix_cost": "2.31",
              "data_read_per_join": "294"
            },
            "used_columns": [
              "product_id"
            ]
          }
        }
      ]
    }
  }
}