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 (
    91905, 91906, 91907, 91908, 91909, 91910, 
    91911, 84268, 89735, 91904, 89435, 
    82417, 86852, 89433, 82399, 86848, 
    84265, 89731, 89431, 83088, 89124, 
    89128, 89160, 89551, 91290, 82332, 
    86814, 86844, 89101, 91286, 90093, 
    90103, 90112, 83115, 83125, 83134, 
    90120, 89199, 83142, 91282, 84121, 
    89202, 83145, 84124, 92841, 94077, 
    94689, 94713, 96714, 96755, 96770, 
    97014, 97019, 97035, 97042, 97048, 
    97054, 97813, 97837, 97991, 98033, 
    98157, 98161, 98165, 98700, 98738, 
    98742, 98746, 99573, 99583, 99587, 
    100070, 100154, 100170, 100172, 100174, 
    100324, 100607, 100885, 100901, 100908, 
    100914, 100920, 101113, 101122, 101131, 
    101515, 101524, 101533, 101553, 101562, 
    101571
  ) 
  AND cscart_discussion.object_type = "P" 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00148

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "106.06"
    },
    "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": 92,
            "rows_produced_per_join": 92,
            "filtered": "100.00",
            "index_condition": "((`nuie_scalesta_net`.`cscart_discussion`.`object_id` in (91905,91906,91907,91908,91909,91910,91911,84268,89735,91904,89435,82417,86852,89433,82399,86848,84265,89731,89431,83088,89124,89128,89160,89551,91290,82332,86814,86844,89101,91286,90093,90103,90112,83115,83125,83134,90120,89199,83142,91282,84121,89202,83145,84124,92841,94077,94689,94713,96714,96755,96770,97014,97019,97035,97042,97048,97054,97813,97837,97991,98033,98157,98161,98165,98700,98738,98742,98746,99573,99583,99587,100070,100154,100170,100172,100174,100324,100607,100885,100901,100908,100914,100920,101113,101122,101131,101515,101524,101533,101553,101562,101571)) and (`nuie_scalesta_net`.`cscart_discussion`.`object_type` = 'P'))",
            "cost_info": {
              "read_cost": "55.21",
              "eval_cost": "9.20",
              "prefix_cost": "64.41",
              "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": 92,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "23.00",
              "eval_cost": "9.20",
              "prefix_cost": "96.61",
              "data_read_per_join": "40K"
            },
            "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": 92,
            "filtered": "100.00",
            "using_join_buffer": "hash join",
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "9.20",
              "prefix_cost": "106.06",
              "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)"
          }
        }
      ]
    }
  }
}