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 (
    86789, 82713, 83302, 85278, 85279, 89750, 
    83412, 83413, 83414, 83415, 89754, 
    85282, 85379, 85430, 85481, 84812, 
    84813, 84830, 84831, 84842, 84843, 
    85337, 85338, 91124, 91210, 91265, 
    85276, 82583, 89827, 89879, 89951, 
    91517, 83018, 83019, 83570, 83572, 
    84515, 84581, 85050, 85136, 85182, 
    85228, 90852, 90854, 90931, 90933, 
    83566, 85108
  ) 
  AND cscart_discussion.object_type = "P" 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00061

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "55.46"
    },
    "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": 48,
            "rows_produced_per_join": 48,
            "filtered": "100.00",
            "index_condition": "((`nuie_scalesta_net`.`cscart_discussion`.`object_id` in (86789,82713,83302,85278,85279,89750,83412,83413,83414,83415,89754,85282,85379,85430,85481,84812,84813,84830,84831,84842,84843,85337,85338,91124,91210,91265,85276,82583,89827,89879,89951,91517,83018,83019,83570,83572,84515,84581,85050,85136,85182,85228,90852,90854,90931,90933,83566,85108)) and (`nuie_scalesta_net`.`cscart_discussion`.`object_type` = 'P'))",
            "cost_info": {
              "read_cost": "28.81",
              "eval_cost": "4.80",
              "prefix_cost": "33.61",
              "data_read_per_join": "1K"
            },
            "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_2",
            "used_key_parts": [
              "thread_id",
              "status"
            ],
            "key_length": "6",
            "ref": [
              "nuie_scalesta_net.cscart_discussion.thread_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 48,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "12.00",
              "eval_cost": "4.80",
              "prefix_cost": "50.41",
              "data_read_per_join": "21K"
            },
            "used_columns": [
              "post_id",
              "thread_id",
              "status"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_discussion_rating",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY",
              "thread_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 48,
            "filtered": "100.00",
            "using_join_buffer": "hash join",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "4.80",
              "prefix_cost": "55.46",
              "data_read_per_join": "768"
            },
            "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)"
          }
        }
      ]
    }
  }
}

Result

product_id average_rating discussion_type discussion_thread_id
82583 D 90621
82713 D 90751
83018 D 91056
83019 D 91057
83302 D 91340
83412 D 91450
83413 D 91451
83414 D 91452
83415 D 91453
83566 D 91604
83570 D 91608
83572 D 91610
84515 D 92553
84581 D 92619
84812 D 92850
84813 D 92851
84830 D 92868
84831 D 92869
84842 D 92880
84843 D 92881
85050 D 93088
85108 D 93146
85136 D 93174
85182 D 93220
85228 D 93266
85276 D 93314
85278 D 93316
85279 D 93317
85282 D 93320
85337 D 93375
85338 D 93376
85379 D 93417
85430 D 93468
85481 D 93519
86789 D 94827
89750 D 97788
89754 D 97792
89827 D 97865
89879 D 97917
89951 D 97989
90852 D 98925
90854 D 98927
90931 D 99004
90933 D 99006
91124 D 99197
91210 D 99283
91265 D 99338
91517 D 99590