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 (
    86524, 86800, 85124, 85125, 89075, 89076, 
    85360, 86500, 86776, 86147, 86148, 
    86170, 86171, 86663, 86664, 86732, 
    86733, 82819, 82892, 82965, 83073, 
    83626, 90909, 90988, 85083, 85169, 
    85215, 85261, 89074, 85123, 86195, 
    86218, 86527, 86803, 89079, 89080, 
    85320, 85321, 85417, 85418, 85468, 
    85469, 85519, 85520, 86184, 86185, 
    86207, 86208, 86508, 86509, 86784, 
    86785, 82855, 82856, 82928, 82929, 
    83001, 83002, 83517, 83518, 83525, 
    83526, 85366, 85367, 86146, 86169, 
    86662, 86731, 89071, 89072, 82600, 
    83076, 83629, 90912, 90991, 86638, 
    86707, 85086, 85172, 85218, 85264, 
    83057, 83058, 83606, 83607, 89825, 
    89826, 89877, 89878, 89949, 89950, 
    90893, 90894, 90972, 90973, 91515
  ) 
  AND cscart_discussion.object_type = "P" 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00156

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 (86524,86800,85124,85125,89075,89076,85360,86500,86776,86147,86148,86170,86171,86663,86664,86732,86733,82819,82892,82965,83073,83626,90909,90988,85083,85169,85215,85261,89074,85123,86195,86218,86527,86803,89079,89080,85320,85321,85417,85418,85468,85469,85519,85520,86184,86185,86207,86208,86508,86509,86784,86785,82855,82856,82928,82929,83001,83002,83517,83518,83525,83526,85366,85367,86146,86169,86662,86731,89071,89072,82600,83076,83629,90912,90991,86638,86707,85086,85172,85218,85264,83057,83058,83606,83607,89825,89826,89877,89878,89949,89950,90893,90894,90972,90973,91515)) 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)"
          }
        }
      ]
    }
  }
}