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 (
    11173, 
    11178, 
    11184, 
    11177, 
    11183, 
    11175, 
    11182, 
    11174, 
    11181, 
    11176, 
    11180, 
    11179, 
    11229, 
    11242, 
    11241, 
    11240, 
    11239, 
    11238, 
    11237, 
    11236, 
    11235, 
    11234, 
    11233, 
    11232, 
    11231, 
    11230, 
    11256, 
    11255, 
    11254, 
    11253, 
    11252, 
    11251, 
    11250, 
    11249, 
    11248, 
    11247, 
    11246, 
    11245, 
    11244, 
    11243, 
    11270, 
    11269, 
    11268, 
    11267, 
    11266, 
    11265, 
    11264, 
    11263, 
    11262, 
    11261, 
    11260, 
    11259, 
    11258, 
    11257, 
    11280, 
    11279, 
    11278, 
    11277, 
    11276, 
    11275, 
    11274, 
    11273, 
    11272, 
    11271, 
    11290, 
    11289, 
    11288, 
    11287, 
    11286, 
    11285, 
    11284, 
    11283, 
    11282, 
    11281, 
    11304, 
    11303, 
    11302, 
    11301, 
    11300, 
    11299, 
    11298, 
    11297, 
    11296, 
    11295, 
    11294, 
    11293, 
    11292, 
    11291, 
    11318, 
    11317, 
    11316, 
    11315, 
    11314, 
    11313, 
    11312, 
    11311, 
    11310, 
    11309, 
    11308, 
    11307, 
    11306, 
    11305, 
    11328, 
    11327, 
    11326, 
    11325, 
    11324, 
    11323, 
    11322, 
    11321, 
    11320, 
    11319, 
    11338, 
    11337, 
    11336, 
    11335, 
    11334, 
    11333, 
    11332, 
    11331, 
    11330, 
    11329, 
    11344, 
    11343, 
    11342, 
    11341, 
    11340, 
    11339, 
    11217, 
    11218, 
    11219, 
    11220, 
    11221, 
    11222, 
    11223, 
    11224, 
    11225, 
    11226, 
    11227, 
    11228
  ) 
GROUP BY 
  p.product_id

Query time 0.00090

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.69"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "dp",
            "access_type": "ALL",
            "possible_keys": [
              "thread_id",
              "thread_id_2"
            ],
            "rows_examined_per_scan": 8,
            "rows_produced_per_join": 1,
            "filtered": "12.50",
            "cost_info": {
              "read_cost": "1.22",
              "eval_cost": "0.10",
              "prefix_cost": "1.32",
              "data_read_per_join": "448"
            },
            "used_columns": [
              "post_id",
              "thread_id",
              "status"
            ],
            "attached_condition": "(`dev_yourmart_co_bw`.`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": [
              "dev_yourmart_co_bw.dp.thread_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "5.00",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "0.01",
              "prefix_cost": "1.67",
              "data_read_per_join": "1"
            },
            "used_columns": [
              "thread_id",
              "object_id",
              "object_type"
            ],
            "attached_condition": "((`dev_yourmart_co_bw`.`cscart_discussion`.`object_id` in (11173,11178,11184,11177,11183,11175,11182,11174,11181,11176,11180,11179,11229,11242,11241,11240,11239,11238,11237,11236,11235,11234,11233,11232,11231,11230,11256,11255,11254,11253,11252,11251,11250,11249,11248,11247,11246,11245,11244,11243,11270,11269,11268,11267,11266,11265,11264,11263,11262,11261,11260,11259,11258,11257,11280,11279,11278,11277,11276,11275,11274,11273,11272,11271,11290,11289,11288,11287,11286,11285,11284,11283,11282,11281,11304,11303,11302,11301,11300,11299,11298,11297,11296,11295,11294,11293,11292,11291,11318,11317,11316,11315,11314,11313,11312,11311,11310,11309,11308,11307,11306,11305,11328,11327,11326,11325,11324,11323,11322,11321,11320,11319,11338,11337,11336,11335,11334,11333,11332,11331,11330,11329,11344,11343,11342,11341,11340,11339,11217,11218,11219,11220,11221,11222,11223,11224,11225,11226,11227,11228)) and (`dev_yourmart_co_bw`.`cscart_discussion`.`object_type` = 'P'))"
          }
        },
        {
          "table": {
            "table_name": "p",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "ref": [
              "dev_yourmart_co_bw.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.01",
              "eval_cost": "0.01",
              "prefix_cost": "1.69",
              "data_read_per_join": "266"
            },
            "used_columns": [
              "product_id"
            ]
          }
        }
      ]
    }
  }
}