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 (
    81655, 
    81657, 
    81702, 
    81704, 
    81737, 
    81739, 
    81826, 
    81860, 
    81862, 
    81863, 
    82052, 
    82061, 
    82069, 
    82071, 
    82072, 
    82073, 
    82074, 
    82101, 
    82103, 
    82038, 
    148882, 
    148883, 
    148884, 
    148885, 
    148886, 
    148887, 
    148888, 
    148889, 
    148890, 
    148891, 
    148892, 
    148893, 
    148894, 
    148895, 
    148896, 
    148897, 
    148898, 
    148899, 
    148900, 
    148901, 
    148902, 
    148903, 
    148904, 
    148905, 
    148906, 
    148907, 
    148908, 
    148909, 
    148910, 
    148911, 
    148912, 
    148913, 
    148914, 
    148915, 
    148916, 
    148917, 
    148918, 
    148919, 
    148920, 
    148921, 
    148922, 
    148923, 
    148924, 
    148925, 
    148926, 
    148927, 
    148928, 
    148929, 
    148930, 
    148931, 
    81948, 
    149025, 
    149026, 
    149027, 
    149028, 
    149029, 
    149030, 
    149031, 
    149032, 
    149033, 
    149034, 
    149035, 
    149036, 
    149037, 
    149038, 
    149039, 
    149040, 
    149041, 
    82118, 
    149043, 
    149044, 
    149045, 
    149046, 
    149047, 
    149048, 
    149049, 
    149042, 
    149050, 
    149051, 
    149052, 
    149053, 
    149054, 
    149055, 
    149056, 
    149057, 
    149058, 
    149059, 
    149060, 
    149061, 
    149062, 
    149063, 
    149064, 
    149065, 
    149066, 
    149067, 
    149068, 
    149069, 
    149070, 
    149071, 
    149072, 
    149073, 
    149074, 
    149075, 
    149076, 
    149077, 
    149078, 
    149079, 
    149080, 
    149081, 
    149082, 
    149083, 
    149084, 
    149085, 
    149086, 
    149087, 
    149088, 
    149089, 
    149090, 
    149091, 
    149092, 
    149093, 
    149094, 
    149095, 
    149096, 
    149097, 
    149098, 
    149099, 
    82009, 
    149101, 
    149102, 
    149103, 
    149104, 
    149105, 
    149106, 
    149107, 
    149108, 
    149109, 
    149110, 
    149111, 
    149112, 
    149113, 
    149114, 
    81647, 
    149123, 
    149124, 
    149125, 
    149126, 
    149127, 
    149128, 
    149129, 
    149130
  ) 
GROUP BY 
  p.product_id

Query time 0.00185

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 (81655,81657,81702,81704,81737,81739,81826,81860,81862,81863,82052,82061,82069,82071,82072,82073,82074,82101,82103,82038,148882,148883,148884,148885,148886,148887,148888,148889,148890,148891,148892,148893,148894,148895,148896,148897,148898,148899,148900,148901,148902,148903,148904,148905,148906,148907,148908,148909,148910,148911,148912,148913,148914,148915,148916,148917,148918,148919,148920,148921,148922,148923,148924,148925,148926,148927,148928,148929,148930,148931,81948,149025,149026,149027,149028,149029,149030,149031,149032,149033,149034,149035,149036,149037,149038,149039,149040,149041,82118,149043,149044,149045,149046,149047,149048,149049,149042,149050,149051,149052,149053,149054,149055,149056,149057,149058,149059,149060,149061,149062,149063,149064,149065,149066,149067,149068,149069,149070,149071,149072,149073,149074,149075,149076,149077,149078,149079,149080,149081,149082,149083,149084,149085,149086,149087,149088,149089,149090,149091,149092,149093,149094,149095,149096,149097,149098,149099,82009,149101,149102,149103,149104,149105,149106,149107,149108,149109,149110,149111,149112,149113,149114,81647,149123,149124,149125,149126,149127,149128,149129,149130)) 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"
            ]
          }
        }
      ]
    }
  }
}