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 (
    262921, 262933, 262939, 262912, 262448, 
    262453, 262944, 262927, 262926, 379728, 
    262462, 262460, 262461, 262953, 262463, 
    262948, 262454, 262459, 262928, 309480, 
    309483, 309481, 309484, 309428, 309482, 
    262832, 270490, 270492, 309429, 270537, 
    262833, 271488, 270583, 220026, 220035, 
    220028, 220016, 220024, 220020, 220034, 
    308648, 282779, 282782, 359434, 359433, 
    359436, 359435, 282780, 359432, 282770, 
    282781, 282769, 282768, 359437, 286549, 
    282765, 381623, 381616, 286550, 381621, 
    381622, 381614, 381615, 381625, 389777, 
    282767, 286548, 390957, 390956, 381624, 
    308641, 390954, 390955, 390953, 263845, 
    263765, 308670, 263744, 263773, 263704, 
    286579, 286581, 286551, 288, 263781, 
    286580, 263740, 263847, 263700, 263920, 
    206928, 114763, 207020, 391626, 391624, 
    309477, 309473, 206943, 309471, 206995
  ) 
GROUP BY 
  p.product_id

Query time 0.00103

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 (262921,262933,262939,262912,262448,262453,262944,262927,262926,379728,262462,262460,262461,262953,262463,262948,262454,262459,262928,309480,309483,309481,309484,309428,309482,262832,270490,270492,309429,270537,262833,271488,270583,220026,220035,220028,220016,220024,220020,220034,308648,282779,282782,359434,359433,359436,359435,282780,359432,282770,282781,282769,282768,359437,286549,282765,381623,381616,286550,381621,381622,381614,381615,381625,389777,282767,286548,390957,390956,381624,308641,390954,390955,390953,263845,263765,308670,263744,263773,263704,286579,286581,286551,288,263781,286580,263740,263847,263700,263920,206928,114763,207020,391626,391624,309477,309473,206943,309471,206995)) 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"
            ]
          }
        }
      ]
    }
  }
}