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 (
    355317, 357785, 357824, 358133, 358225, 
    351131, 359347, 353940, 355373, 358196, 
    359301, 351014, 354944, 357958, 357973, 
    354058, 359264, 359384, 351103, 353864, 
    357388, 357392, 358125, 359180, 350722, 
    358205, 359120, 350843, 351525, 355354, 
    358148, 351632, 353978, 354934, 355328, 
    357029, 357292, 357862, 350855, 351117, 
    355320, 357414, 351084, 357008, 357212, 
    357343, 358141, 358219, 359162, 359206, 
    350765, 356182, 357060, 357406, 359178, 
    353947, 355218, 358014, 359220, 359235, 
    351658, 357095, 357753, 357885, 354053, 
    357699, 357975, 358080, 359140, 355313, 
    356159, 357957, 359170, 359196, 359217, 
    351085, 354928, 356158, 357070, 358055, 
    358202, 358264, 357191, 358117, 351475, 
    356987, 358247, 359219, 354068, 354941, 
    357061, 358252, 359369, 350758, 350833, 
    358254, 359153, 351202, 359237, 351483
  ) 
GROUP BY 
  p.product_id

Query time 0.00133

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 (355317,357785,357824,358133,358225,351131,359347,353940,355373,358196,359301,351014,354944,357958,357973,354058,359264,359384,351103,353864,357388,357392,358125,359180,350722,358205,359120,350843,351525,355354,358148,351632,353978,354934,355328,357029,357292,357862,350855,351117,355320,357414,351084,357008,357212,357343,358141,358219,359162,359206,350765,356182,357060,357406,359178,353947,355218,358014,359220,359235,351658,357095,357753,357885,354053,357699,357975,358080,359140,355313,356159,357957,359170,359196,359217,351085,354928,356158,357070,358055,358202,358264,357191,358117,351475,356987,358247,359219,354068,354941,357061,358252,359369,350758,350833,358254,359153,351202,359237,351483)) 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"
            ]
          }
        }
      ]
    }
  }
}