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 (
    432251, 432259, 432260, 432261, 432262, 
    432263, 432264, 432265, 432266, 432320, 
    432332, 432333, 432346, 432347, 432348, 
    432349, 432350, 432351, 432352, 432353, 
    432355, 432356, 432357, 432358, 432359, 
    432460, 432462, 432463, 432472, 432473, 
    432474, 432475, 432476, 432477, 432478, 
    432479, 432480, 432481, 432482, 432483, 
    432484, 432485, 432486, 432487, 432488, 
    432489, 432490, 432491, 432492, 432493, 
    432494, 432495, 432449, 432515, 432516, 
    432517, 432518, 432519, 432520, 432521, 
    432522, 432419, 432523, 432524, 432541, 
    432542, 432543, 432544, 432545, 432546, 
    432547, 432548, 432549, 432550, 432551, 
    432552, 432589, 432590, 432591, 432592, 
    432593, 432594, 432595, 432596, 432597, 
    432642, 432643, 432644, 432645, 432646, 
    432647, 432652, 432660, 432661, 432662, 
    432668, 432669, 432670, 432671, 432673, 
    432677, 432679, 432683, 432688, 432689, 
    432694, 432695
  ) 
GROUP BY 
  p.product_id

Query time 0.00131

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7.29"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "0.10"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "dp",
            "access_type": "index",
            "possible_keys": [
              "thread_id",
              "thread_id_2"
            ],
            "key": "thread_id_2",
            "used_key_parts": [
              "thread_id",
              "status"
            ],
            "key_length": "6",
            "rows_examined_per_scan": 19,
            "rows_produced_per_join": 1,
            "filtered": "10.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "4.42",
              "eval_cost": "0.38",
              "prefix_cost": "4.80",
              "data_read_per_join": "851"
            },
            "used_columns": [
              "post_id",
              "thread_id",
              "status"
            ],
            "attached_condition": "(`webmarco`.`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": [
              "webmarco.dp.thread_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "1.90",
              "eval_cost": "0.02",
              "prefix_cost": "7.08",
              "data_read_per_join": "2"
            },
            "used_columns": [
              "thread_id",
              "object_id",
              "object_type"
            ],
            "attached_condition": "((`webmarco`.`cscart_discussion`.`object_id` in (432251,432259,432260,432261,432262,432263,432264,432265,432266,432320,432332,432333,432346,432347,432348,432349,432350,432351,432352,432353,432355,432356,432357,432358,432359,432460,432462,432463,432472,432473,432474,432475,432476,432477,432478,432479,432480,432481,432482,432483,432484,432485,432486,432487,432488,432489,432490,432491,432492,432493,432494,432495,432449,432515,432516,432517,432518,432519,432520,432521,432522,432419,432523,432524,432541,432542,432543,432544,432545,432546,432547,432548,432549,432550,432551,432552,432589,432590,432591,432592,432593,432594,432595,432596,432597,432642,432643,432644,432645,432646,432647,432652,432660,432661,432662,432668,432669,432670,432671,432673,432677,432679,432683,432688,432689,432694,432695)) and (`webmarco`.`cscart_discussion`.`object_type` = 'P'))"
          }
        },
        {
          "table": {
            "table_name": "p",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "age_verification",
              "status",
              "idx_parent_product_id",
              "idx_master_product_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "ref": [
              "webmarco.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.10",
              "eval_cost": "0.02",
              "prefix_cost": "7.19",
              "data_read_per_join": "760"
            },
            "used_columns": [
              "product_id"
            ]
          }
        }
      ]
    }
  }
}