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 (
    91921, 91922, 91923, 91924, 91925, 91926, 
    91927, 89440, 82416, 91913, 91914, 
    91915, 91916, 91917, 91918, 91919, 
    82415, 86854, 89438, 82398, 84262, 
    84266, 82397, 86850, 89728, 89733, 
    89158, 89549, 82334, 82339, 82349, 
    84913, 84916, 84919, 84928, 84931, 
    86390, 89436, 83086, 89122, 89161, 
    89552, 82347, 86808, 89736, 89989, 
    89990, 89991, 89992, 86811, 89098, 
    89126, 82330, 82348, 86807, 86846, 
    89732, 91292, 90094, 90104, 90113, 
    91288, 83116, 83126, 83135, 90121, 
    89200, 83143, 84122, 82447, 91284, 
    91920, 91912, 90118, 89197, 83140, 
    84119, 82364, 82363, 90117, 89196, 
    90115, 89194, 84116, 84118, 83137, 
    82390, 92842, 94078, 94686, 94690, 
    94692, 94693, 94710, 94714, 94716
  ) 
  AND cscart_discussion.object_type = "P" 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00172

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 (91921,91922,91923,91924,91925,91926,91927,89440,82416,91913,91914,91915,91916,91917,91918,91919,82415,86854,89438,82398,84262,84266,82397,86850,89728,89733,89158,89549,82334,82339,82349,84913,84916,84919,84928,84931,86390,89436,83086,89122,89161,89552,82347,86808,89736,89989,89990,89991,89992,86811,89098,89126,82330,82348,86807,86846,89732,91292,90094,90104,90113,91288,83116,83126,83135,90121,89200,83143,84122,82447,91284,91920,91912,90118,89197,83140,84119,82364,82363,90117,89196,90115,89194,84116,84118,83137,82390,92842,94078,94686,94690,94692,94693,94710,94714,94716)) 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)"
          }
        }
      ]
    }
  }
}