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 (
    2187, 
    2244, 
    2172, 
    2171, 
    1668, 
    8586, 
    8372, 
    2222, 
    2175, 
    2228, 
    2220, 
    2177, 
    2230, 
    2226, 
    1667, 
    2221, 
    2179, 
    1666, 
    8240, 
    2183, 
    8813, 
    2176, 
    8433, 
    2224, 
    2195, 
    2225, 
    8514, 
    2189, 
    2196, 
    2186, 
    2180, 
    2178, 
    2191, 
    2170, 
    2181, 
    2190, 
    2185, 
    2198, 
    2182, 
    2188, 
    2173, 
    8784, 
    2193, 
    2184, 
    2197, 
    2174, 
    2199, 
    2219, 
    2223, 
    2194, 
    2243, 
    2200, 
    2229, 
    8789, 
    8695, 
    8766, 
    8802, 
    8362, 
    8573, 
    8696, 
    8243, 
    8617, 
    8601, 
    8375, 
    8570, 
    8812, 
    8536, 
    8604, 
    8589, 
    8699, 
    8590, 
    8612, 
    8800, 
    8702, 
    8576, 
    8701, 
    8464, 
    8469, 
    8577, 
    8708, 
    8703, 
    8783, 
    8804, 
    8275, 
    8569, 
    8724, 
    8445, 
    8470, 
    8435, 
    8801, 
    8805, 
    8811, 
    8815, 
    8441, 
    8446, 
    8543, 
    8245, 
    8440, 
    8519, 
    8593, 
    8595, 
    8432, 
    8444, 
    8471, 
    8594, 
    8472, 
    8457, 
    8806, 
    8828, 
    8830, 
    8575, 
    8269, 
    8466, 
    8363, 
    8459, 
    8460, 
    8603, 
    8581, 
    8709, 
    8438, 
    8799, 
    8831, 
    8241, 
    8272, 
    8273, 
    8276, 
    8365, 
    8382, 
    8538, 
    8572, 
    8578, 
    8580, 
    8585, 
    8597, 
    8614, 
    8615, 
    8729, 
    8730, 
    8757, 
    8797, 
    8810, 
    8814, 
    8829, 
    8242, 
    8244, 
    8277, 
    8280, 
    8373, 
    8377, 
    8403, 
    8431, 
    8434, 
    8443, 
    8517, 
    8540, 
    8574, 
    8587, 
    8596, 
    8600, 
    8605
  ) 
GROUP BY 
  p.product_id

Query time 0.00112

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 (2187,2244,2172,2171,1668,8586,8372,2222,2175,2228,2220,2177,2230,2226,1667,2221,2179,1666,8240,2183,8813,2176,8433,2224,2195,2225,8514,2189,2196,2186,2180,2178,2191,2170,2181,2190,2185,2198,2182,2188,2173,8784,2193,2184,2197,2174,2199,2219,2223,2194,2243,2200,2229,8789,8695,8766,8802,8362,8573,8696,8243,8617,8601,8375,8570,8812,8536,8604,8589,8699,8590,8612,8800,8702,8576,8701,8464,8469,8577,8708,8703,8783,8804,8275,8569,8724,8445,8470,8435,8801,8805,8811,8815,8441,8446,8543,8245,8440,8519,8593,8595,8432,8444,8471,8594,8472,8457,8806,8828,8830,8575,8269,8466,8363,8459,8460,8603,8581,8709,8438,8799,8831,8241,8272,8273,8276,8365,8382,8538,8572,8578,8580,8585,8597,8614,8615,8729,8730,8757,8797,8810,8814,8829,8242,8244,8277,8280,8373,8377,8403,8431,8434,8443,8517,8540,8574,8587,8596,8600,8605)) 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"
            ]
          }
        }
      ]
    }
  }
}