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 (
    6730, 7172, 6924, 7084, 6814, 6683, 7411, 
    6684, 6685, 6686, 6687, 6688, 6689, 
    6690, 6693, 11052, 6694, 6912, 6691, 
    6913, 6682, 6695, 6696, 11104, 6692, 
    8688, 6697, 8000, 6698, 6699, 6700, 
    6701, 6702, 6703, 6704, 6705, 6707, 
    6708, 6709, 6710, 6714, 6706, 6716, 
    6717, 6718, 6715, 6719, 6720, 6721, 
    6711, 6723, 6722, 6724, 6712, 6725, 
    6726, 6727, 6728, 6729, 6731, 6732, 
    8742, 6733, 6734, 6735, 6736, 6713, 
    6737, 6738, 6739, 6740, 6741, 6742, 
    6748, 8801, 8802, 11103, 6744, 6745, 
    6746, 6747, 10992, 6749, 6750, 8743, 
    6751, 6752, 6753, 6754, 6755, 6756, 
    6757, 6758, 6759, 6760, 6915, 6916, 
    6761, 6762, 11378
  ) 
  AND cscart_discussion.object_type = "P" 
  AND cscart_discussion.company_id = 1 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00110

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "cscart_discussion",
          "access_type": "range",
          "possible_keys": ["object_id", "idx_company_id"],
          "key": "object_id",
          "key_length": "10",
          "used_key_parts": ["object_id", "object_type", "company_id"],
          "rows": 100,
          "filtered": 100,
          "index_condition": "cscart_discussion.company_id = 1 and cscart_discussion.object_id in (6730,7172,6924,7084,6814,6683,7411,6684,6685,6686,6687,6688,6689,6690,6693,11052,6694,6912,6691,6913,6682,6695,6696,11104,6692,8688,6697,8000,6698,6699,6700,6701,6702,6703,6704,6705,6707,6708,6709,6710,6714,6706,6716,6717,6718,6715,6719,6720,6721,6711,6723,6722,6724,6712,6725,6726,6727,6728,6729,6731,6732,8742,6733,6734,6735,6736,6713,6737,6738,6739,6740,6741,6742,6748,8801,8802,11103,6744,6745,6746,6747,10992,6749,6750,8743,6751,6752,6753,6754,6755,6756,6757,6758,6759,6760,6915,6916,6761,6762,11378) and cscart_discussion.object_type = 'P'"
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion_posts",
          "access_type": "ref",
          "possible_keys": ["thread_id", "thread_id_2"],
          "key": "thread_id_2",
          "key_length": "6",
          "used_key_parts": ["thread_id", "status"],
          "ref": ["lillyshop_lillyshop.cscart_discussion.thread_id", "const"],
          "rows": 2,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_discussion_posts.`status` = 'A')"
        }
      },
      {
        "table": {
          "table_name": "cscart_discussion_rating",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "thread_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["post_id"],
          "ref": ["lillyshop_lillyshop.cscart_discussion_posts.post_id"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_discussion_rating.thread_id = cscart_discussion.thread_id and cscart_discussion_rating.rating_value <> 0 and trigcond(cscart_discussion_posts.post_id is not null))"
        }
      }
    ]
  }
}