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 (
    7172, 
    6924, 
    6730, 
    6784, 
    6764, 
    6737, 
    6690, 
    8742, 
    6771, 
    6724, 
    6723, 
    6813, 
    6797, 
    6801, 
    6750, 
    6694, 
    10195, 
    6706, 
    6689, 
    6782, 
    6763, 
    6736, 
    7084, 
    8743, 
    6768, 
    6720, 
    6722, 
    6812, 
    6798, 
    6793, 
    6749, 
    6691, 
    9495, 
    6702, 
    6688, 
    6781, 
    6759, 
    6734, 
    6921, 
    8680, 
    6765, 
    6718, 
    11375, 
    6721, 
    6811, 
    6796, 
    6791, 
    6748, 
    6682, 
    8802, 
    6700, 
    6687, 
    6779, 
    6755, 
    6732, 
    6923, 
    6810, 
    6920, 
    6762, 
    6717, 
    11104, 
    6719, 
    6795, 
    6789, 
    6747, 
    8801, 
    6701, 
    6686, 
    6778, 
    6754, 
    6731, 
    6808, 
    6922, 
    6761, 
    6715, 
    11103, 
    6716, 
    6794, 
    6783, 
    6746, 
    8688, 
    6699, 
    6685, 
    6776, 
    6751, 
    6915, 
    6806, 
    6918, 
    6760, 
    6712, 
    11059, 
    6714, 
    6790, 
    6780, 
    6745, 
    8241, 
    6698, 
    6684, 
    6777, 
    6744, 
    6729, 
    6916, 
    6805, 
    6919, 
    6758, 
    6713, 
    11052, 
    6711, 
    6788, 
    6769, 
    6743, 
    8008, 
    6697, 
    6683, 
    6775, 
    6740, 
    6728, 
    6917, 
    6804, 
    6914, 
    6757, 
    6705, 
    10992, 
    6710, 
    6787, 
    6770, 
    6742, 
    8000, 
    6696, 
    11378, 
    6773, 
    6739, 
    6727, 
    6912, 
    6803, 
    6809, 
    6756, 
    6704, 
    10778, 
    6708, 
    6786, 
    6767, 
    6741, 
    7914, 
    6693, 
    11211, 
    6774, 
    6735, 
    6726, 
    6913, 
    6800, 
    6807, 
    6753, 
    6703, 
    10640, 
    6709, 
    6785, 
    6766, 
    6738, 
    7411, 
    6692, 
    8865, 
    6772, 
    6733, 
    6725, 
    6814, 
    6799, 
    6802, 
    6752, 
    6695, 
    10246, 
    6707
  ) 
GROUP BY 
  p.product_id

Query time 0.00152

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 (7172,6924,6730,6784,6764,6737,6690,8742,6771,6724,6723,6813,6797,6801,6750,6694,10195,6706,6689,6782,6763,6736,7084,8743,6768,6720,6722,6812,6798,6793,6749,6691,9495,6702,6688,6781,6759,6734,6921,8680,6765,6718,11375,6721,6811,6796,6791,6748,6682,8802,6700,6687,6779,6755,6732,6923,6810,6920,6762,6717,11104,6719,6795,6789,6747,8801,6701,6686,6778,6754,6731,6808,6922,6761,6715,11103,6716,6794,6783,6746,8688,6699,6685,6776,6751,6915,6806,6918,6760,6712,11059,6714,6790,6780,6745,8241,6698,6684,6777,6744,6729,6916,6805,6919,6758,6713,11052,6711,6788,6769,6743,8008,6697,6683,6775,6740,6728,6917,6804,6914,6757,6705,10992,6710,6787,6770,6742,8000,6696,11378,6773,6739,6727,6912,6803,6809,6756,6704,10778,6708,6786,6767,6741,7914,6693,11211,6774,6735,6726,6913,6800,6807,6753,6703,10640,6709,6785,6766,6738,7411,6692,8865,6772,6733,6725,6814,6799,6802,6752,6695,10246,6707)",
              "using_index": true
            }
          }
        ]
      }
    }
  }
}