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 (
    396068, 396069, 396070, 396071, 396072, 
    396073, 414480, 414481, 414482, 414483, 
    414484, 414502, 414504, 431672, 431673, 
    431674, 431675, 431676, 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, 432688, 432689, 432694, 
    432695
  ) 
GROUP BY 
  p.product_id

Query time 0.00151

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 (396068,396069,396070,396071,396072,396073,414480,414481,414482,414483,414484,414502,414504,431672,431673,431674,431675,431676,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,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"
            ]
          }
        }
      ]
    }
  }
}