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' 
    AND cscart_discussion.company_id = 1
  ) 
WHERE 
  dp.status = 'A' 
  and p.product_id in (
    9953, 
    9629, 
    9753, 
    9569, 
    9655, 
    9611, 
    10030, 
    9643, 
    9925, 
    9584, 
    9683, 
    8457, 
    9952, 
    9630, 
    9568, 
    9668, 
    9654, 
    9937, 
    9610, 
    9738, 
    8939, 
    9961, 
    9923, 
    9582, 
    9682, 
    8308, 
    9632, 
    9750, 
    9666, 
    9737, 
    8936, 
    8307, 
    9641, 
    9924, 
    9581, 
    9681, 
    9950, 
    9627, 
    9565, 
    9665, 
    9652, 
    9934, 
    9606, 
    8933, 
    9960, 
    9640, 
    9922, 
    9578, 
    9949, 
    9626, 
    9748, 
    9564, 
    9664, 
    9651, 
    9933, 
    9604, 
    9735, 
    8687, 
    9959, 
    9921, 
    9577, 
    9662, 
    9947, 
    9625, 
    9562, 
    9650, 
    9932, 
    9600, 
    9734, 
    7493, 
    9958, 
    9638, 
    9576, 
    9677, 
    9945, 
    9623, 
    9746, 
    8999, 
    9931, 
    9599, 
    8463, 
    6883, 
    9956, 
    9758, 
    9575, 
    9676, 
    9660, 
    9944, 
    9621, 
    9745, 
    8983, 
    9648, 
    9930, 
    9732, 
    8462, 
    9957, 
    9635, 
    9757, 
    9574, 
    9673, 
    9658, 
    9943, 
    9744, 
    9647, 
    9928, 
    9589, 
    9730, 
    8461, 
    9955, 
    9756, 
    9573, 
    9672, 
    9657, 
    9942, 
    9616, 
    9743, 
    8945, 
    9927, 
    9587, 
    9686, 
    8459, 
    9954, 
    9633, 
    9754, 
    9671, 
    9656, 
    9612, 
    8941, 
    10202, 
    9645, 
    9926, 
    9585, 
    9685, 
    8458, 
    9670, 
    9939, 
    9740, 
    8940, 
    9752, 
    9642, 
    9951, 
    9566, 
    9653, 
    9935, 
    9607, 
    9962, 
    9749, 
    9736, 
    8235, 
    9680, 
    8213, 
    9639, 
    9678, 
    9747, 
    8686, 
    9920, 
    9661, 
    9649, 
    9733, 
    9636, 
    9591, 
    9619, 
    8982, 
    9634, 
    9646, 
    9572, 
    9940, 
    9741
  ) 
GROUP BY 
  p.product_id

Query time 0.00203

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "p.product_id",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "dp",
              "access_type": "ALL",
              "possible_keys": ["thread_id", "thread_id_2"],
              "rows": 287,
              "filtered": 100,
              "attached_condition": "dp.`status` = 'A'"
            }
          },
          {
            "table": {
              "table_name": "cscart_discussion",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "object_id", "idx_company_id"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["thread_id"],
              "ref": ["lillyshop_lillyshop.dp.thread_id"],
              "rows": 1,
              "filtered": 99.99187469,
              "attached_condition": "cscart_discussion.company_id = 1 and cscart_discussion.object_type = 'P'"
            }
          },
          {
            "table": {
              "table_name": "p",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["lillyshop_lillyshop.cscart_discussion.object_id"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "p.product_id in (9953,9629,9753,9569,9655,9611,10030,9643,9925,9584,9683,8457,9952,9630,9568,9668,9654,9937,9610,9738,8939,9961,9923,9582,9682,8308,9632,9750,9666,9737,8936,8307,9641,9924,9581,9681,9950,9627,9565,9665,9652,9934,9606,8933,9960,9640,9922,9578,9949,9626,9748,9564,9664,9651,9933,9604,9735,8687,9959,9921,9577,9662,9947,9625,9562,9650,9932,9600,9734,7493,9958,9638,9576,9677,9945,9623,9746,8999,9931,9599,8463,6883,9956,9758,9575,9676,9660,9944,9621,9745,8983,9648,9930,9732,8462,9957,9635,9757,9574,9673,9658,9943,9744,9647,9928,9589,9730,8461,9955,9756,9573,9672,9657,9942,9616,9743,8945,9927,9587,9686,8459,9954,9633,9754,9671,9656,9612,8941,10202,9645,9926,9585,9685,8458,9670,9939,9740,8940,9752,9642,9951,9566,9653,9935,9607,9962,9749,9736,8235,9680,8213,9639,9678,9747,8686,9920,9661,9649,9733,9636,9591,9619,8982,9634,9646,9572,9940,9741)",
              "using_index": true
            }
          }
        ]
      }
    }
  }
}