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 (
    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
  ) 
GROUP BY 
  p.product_id

Query time 0.00115

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 (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)) 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"
            ]
          }
        }
      ]
    }
  }
}