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 (
    80280, 80374, 80371, 80418, 80292, 80460, 
    81648, 81506, 415310, 81671, 80275, 
    80326, 81573, 81586, 80333, 81659, 
    80270, 80430, 81501, 80282, 81605, 
    80385, 80358, 80268, 80412, 80337, 
    80360, 81667, 81552, 80411, 80288, 
    81650, 80432, 81701, 80408, 80351, 
    428033, 80451, 80357, 81638, 80377, 
    81633, 81569, 81653, 81629, 81656, 
    81642, 81561, 81566, 81523, 309541, 
    81612, 81594, 81598, 81540, 81670, 
    81504, 81645, 81680, 81661, 81623, 
    81677, 81588, 81517, 427778, 81678, 
    81564, 81695, 81531, 309548, 81536, 
    309544, 81631, 80354, 309546, 80364, 
    80352, 80355, 80427, 80383, 80286, 
    80366, 80380, 80373, 80425, 80422, 
    81640, 309545, 81562, 81626, 81698, 
    81617, 81554, 81600, 81513, 81599, 
    81618, 81571, 81533, 202631
  ) 
GROUP BY 
  p.product_id

Query time 0.00106

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 (80280,80374,80371,80418,80292,80460,81648,81506,415310,81671,80275,80326,81573,81586,80333,81659,80270,80430,81501,80282,81605,80385,80358,80268,80412,80337,80360,81667,81552,80411,80288,81650,80432,81701,80408,80351,428033,80451,80357,81638,80377,81633,81569,81653,81629,81656,81642,81561,81566,81523,309541,81612,81594,81598,81540,81670,81504,81645,81680,81661,81623,81677,81588,81517,427778,81678,81564,81695,81531,309548,81536,309544,81631,80354,309546,80364,80352,80355,80427,80383,80286,80366,80380,80373,80425,80422,81640,309545,81562,81626,81698,81617,81554,81600,81513,81599,81618,81571,81533,202631)) 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"
            ]
          }
        }
      ]
    }
  }
}