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 (
    93782, 93783, 93784, 93785, 93786, 94026, 
    94027, 94028, 94029, 94030, 94031, 
    94032, 94033, 94074, 94075, 94428, 
    94452, 94453, 94454, 94455, 94456, 
    94458, 94459, 94460, 94461, 94462, 
    94709, 94717, 94718, 94719, 94720, 
    94721, 94722, 94723, 94724, 94725, 
    94726, 94727, 94728, 94729, 94730, 
    94731, 94732, 94733, 94734, 94735, 
    94736, 94737, 94738, 94739, 94740, 
    94741, 94742, 94743, 94744, 94745, 
    94746, 94747, 94748, 94749, 94750, 
    94751, 94752, 94753, 94754, 94755, 
    94756, 94757, 94758, 94759, 94760, 
    94761, 94762, 94763, 94764, 94765, 
    94816, 94950, 94951, 95565, 95566, 
    95567, 95568, 95569, 95570, 95571, 
    95572, 95573, 95574, 95575, 95576, 
    95577, 95578, 95579, 95580, 95581
  ) 
  AND cscart_discussion.object_type = "P" 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00073

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 (93782,93783,93784,93785,93786,94026,94027,94028,94029,94030,94031,94032,94033,94074,94075,94428,94452,94453,94454,94455,94456,94458,94459,94460,94461,94462,94709,94717,94718,94719,94720,94721,94722,94723,94724,94725,94726,94727,94728,94729,94730,94731,94732,94733,94734,94735,94736,94737,94738,94739,94740,94741,94742,94743,94744,94745,94746,94747,94748,94749,94750,94751,94752,94753,94754,94755,94756,94757,94758,94759,94760,94761,94762,94763,94764,94765,94816,94950,94951,95565,95566,95567,95568,95569,95570,95571,95572,95573,95574,95575,95576,95577,95578,95579,95580,95581)) 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)"
          }
        }
      ]
    }
  }
}