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 (
    87325, 87336, 87337, 87348, 87349, 86482, 
    86484, 86758, 86760, 83582, 83583, 
    83584, 83585, 86479, 86755, 91118, 
    91119, 91204, 91205, 91259, 91260, 
    82615, 82617, 82625, 82626, 83418, 
    83420, 83490, 83491, 86651, 86720, 
    87300, 87301, 82612, 83411, 91149, 
    91150, 86480, 86756, 85073, 85159, 
    85205, 85251, 82613, 83416, 83031, 
    83033, 83588, 83590, 90867, 90869, 
    90946, 90948, 82623, 83488, 85070, 
    85156, 85202, 85248, 85113, 83233, 
    83234, 83028, 83581, 90864, 90943, 
    82843, 82916, 82989, 83473, 83481, 
    85110, 85071, 85157, 85203, 85249, 
    83029, 83227, 83232, 83586, 90865, 
    90944, 85111, 86620, 86622, 86689, 
    86691, 83042, 83044, 90878, 90880, 
    90957, 90959, 84810, 84811, 84828
  ) 
  AND cscart_discussion.object_type = "P" 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00135

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 (87325,87336,87337,87348,87349,86482,86484,86758,86760,83582,83583,83584,83585,86479,86755,91118,91119,91204,91205,91259,91260,82615,82617,82625,82626,83418,83420,83490,83491,86651,86720,87300,87301,82612,83411,91149,91150,86480,86756,85073,85159,85205,85251,82613,83416,83031,83033,83588,83590,90867,90869,90946,90948,82623,83488,85070,85156,85202,85248,85113,83233,83234,83028,83581,90864,90943,82843,82916,82989,83473,83481,85110,85071,85157,85203,85249,83029,83227,83232,83586,90865,90944,85111,86620,86622,86689,86691,83042,83044,90878,90880,90957,90959,84810,84811,84828)) 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)"
          }
        }
      ]
    }
  }
}