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 (
    286557, 286620, 286621, 286622, 286625, 
    286602, 286603, 286604, 286605, 286607, 
    277872, 277944, 277945, 277946, 277947, 
    277948, 277954, 286611, 286612, 286613, 
    286614, 286616, 278025, 278026, 278027, 
    278028, 278029, 286574, 286575, 286577, 
    286561, 286562, 286563, 286564, 286565, 
    286569, 278190, 278209, 278210, 278250, 
    279355, 279356, 279359, 279360, 279396, 
    279397, 279398, 279399, 279400, 279401, 
    279402, 279438, 279439, 279440, 282507, 
    282508, 282509, 282510, 282511, 284472, 
    284473, 284474, 284475, 286533, 286536, 
    286537, 286538, 285797, 425349, 425350
  ) 
GROUP BY 
  p.product_id

Query time 0.00093

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 (286557,286620,286621,286622,286625,286602,286603,286604,286605,286607,277872,277944,277945,277946,277947,277948,277954,286611,286612,286613,286614,286616,278025,278026,278027,278028,278029,286574,286575,286577,286561,286562,286563,286564,286565,286569,278190,278209,278210,278250,279355,279356,279359,279360,279396,279397,279398,279399,279400,279401,279402,279438,279439,279440,282507,282508,282509,282510,282511,284472,284473,284474,284475,286533,286536,286537,286538,285797,425349,425350)) 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"
            ]
          }
        }
      ]
    }
  }
}