SELECT 
  cscart_discussion.object_id AS product_id, 
  AVG(
    cscart_discussion_rating.rating_value
  ) AS average_rating, 
  cscart_discussion.type AS discussion_type, 
  cscart_discussion.thread_id AS discussion_thread_id 
FROM 
  cscart_discussion 
  LEFT JOIN cscart_discussion_posts ON cscart_discussion_posts.thread_id = cscart_discussion.thread_id 
  AND cscart_discussion_posts.status = "A" 
  LEFT JOIN cscart_discussion_rating ON cscart_discussion.thread_id = cscart_discussion_rating.thread_id 
  AND cscart_discussion_rating.post_id = cscart_discussion_posts.post_id 
  AND cscart_discussion_rating.rating_value != 0 
WHERE 
  cscart_discussion.object_id IN (
    86936, 86937, 86938, 86939, 86940, 86941, 
    86942, 86997, 86998, 86999, 87000, 
    87001, 87002, 87003, 87058, 87059, 
    87060, 87061, 87062, 87063, 87064, 
    87116, 87117, 87118, 87119, 87120, 
    87121, 87122, 86930, 86931, 86932, 
    86933, 86934, 86935, 86991, 86992, 
    86993, 86994, 86995, 86996, 87052, 
    87053, 87054, 87055, 87056, 87057, 
    87110, 87111, 87112, 87113, 87114, 
    87115, 86957, 86958, 86959, 86960, 
    86961, 86962, 87018, 87019, 87020, 
    87021, 87022, 87023, 87079, 87080, 
    87081, 87082, 87083, 87084, 87130, 
    87131, 87132, 87133, 87134, 87135, 
    86950, 86951, 86952, 86953, 86954, 
    86955, 86956, 87011, 87012, 87013, 
    87014, 87015, 87016, 87017, 87072, 
    87073, 87074, 87075, 87076, 87077
  ) 
  AND cscart_discussion.object_type = "P" 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00133

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "110.66"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_discussion",
            "access_type": "range",
            "possible_keys": [
              "object_id"
            ],
            "key": "object_id",
            "used_key_parts": [
              "object_id",
              "object_type"
            ],
            "key_length": "6",
            "rows_examined_per_scan": 96,
            "rows_produced_per_join": 96,
            "filtered": "100.00",
            "index_condition": "((`nuie_scalesta_net`.`cscart_discussion`.`object_id` in (86936,86937,86938,86939,86940,86941,86942,86997,86998,86999,87000,87001,87002,87003,87058,87059,87060,87061,87062,87063,87064,87116,87117,87118,87119,87120,87121,87122,86930,86931,86932,86933,86934,86935,86991,86992,86993,86994,86995,86996,87052,87053,87054,87055,87056,87057,87110,87111,87112,87113,87114,87115,86957,86958,86959,86960,86961,86962,87018,87019,87020,87021,87022,87023,87079,87080,87081,87082,87083,87084,87130,87131,87132,87133,87134,87135,86950,86951,86952,86953,86954,86955,86956,87011,87012,87013,87014,87015,87016,87017,87072,87073,87074,87075,87076,87077)) and (`nuie_scalesta_net`.`cscart_discussion`.`object_type` = 'P'))",
            "cost_info": {
              "read_cost": "57.61",
              "eval_cost": "9.60",
              "prefix_cost": "67.21",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "thread_id",
              "object_id",
              "object_type",
              "type"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_discussion_posts",
            "access_type": "ref",
            "possible_keys": [
              "thread_id",
              "thread_id_2"
            ],
            "key": "thread_id",
            "used_key_parts": [
              "thread_id"
            ],
            "key_length": "3",
            "ref": [
              "nuie_scalesta_net.cscart_discussion.thread_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 96,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "24.00",
              "eval_cost": "9.60",
              "prefix_cost": "100.81",
              "data_read_per_join": "42K"
            },
            "used_columns": [
              "post_id",
              "thread_id",
              "status"
            ],
            "attached_condition": "<if>(is_not_null_compl(cscart_discussion_posts), (`nuie_scalesta_net`.`cscart_discussion_posts`.`status` = 'A'), true)"
          }
        },
        {
          "table": {
            "table_name": "cscart_discussion_rating",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "thread_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 96,
            "filtered": "100.00",
            "using_join_buffer": "hash join",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "9.60",
              "prefix_cost": "110.66",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "rating_value",
              "post_id",
              "thread_id"
            ],
            "attached_condition": "<if>(is_not_null_compl(cscart_discussion_rating), ((`nuie_scalesta_net`.`cscart_discussion_rating`.`post_id` = `nuie_scalesta_net`.`cscart_discussion_posts`.`post_id`) and (`nuie_scalesta_net`.`cscart_discussion_rating`.`thread_id` = `nuie_scalesta_net`.`cscart_discussion`.`thread_id`) and (`nuie_scalesta_net`.`cscart_discussion_rating`.`rating_value` <> 0)), true)"
          }
        }
      ]
    }
  }
}