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 (
    21626, 18985, 18986, 19418, 18988, 21627, 
    18987, 18908, 18989, 18990, 18991, 
    18992, 21628, 18994, 21630, 21629, 
    21631, 21632, 18961, 21634, 18995, 
    19007, 9348, 9347, 18960, 18959, 20565, 
    18970, 18971, 18973, 21641, 18975, 
    18976, 18977, 21640, 22461, 21639, 
    18958, 18981, 18982, 21637, 18984, 
    22048, 22055, 22052, 22054, 22064, 
    22063, 22062, 22056, 22061, 22058, 
    22044, 22046, 22060, 18998, 18996, 
    18997, 18999, 19000, 19001, 19002, 
    19003, 19004, 19006, 19005, 22051, 
    22053, 22047, 22050, 22045, 22479, 
    22049, 22057, 22059, 21638, 21635, 
    18911, 18912, 19011, 18913, 19066, 
    21636, 19013, 18915, 19014, 20602, 
    18917, 18918, 21642, 19016, 18920, 
    19017, 20603, 20601, 18924, 18914, 
    18925, 18926
  ) 
  AND cscart_discussion.object_type = "P" 
  AND cscart_discussion.company_id = 1 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00137

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": 99,
          "filtered": 100,
          "index_condition": "cscart_discussion.company_id = 1 and cscart_discussion.object_id in (21626,18985,18986,19418,18988,21627,18987,18908,18989,18990,18991,18992,21628,18994,21630,21629,21631,21632,18961,21634,18995,19007,9348,9347,18960,18959,20565,18970,18971,18973,21641,18975,18976,18977,21640,22461,21639,18958,18981,18982,21637,18984,22048,22055,22052,22054,22064,22063,22062,22056,22061,22058,22044,22046,22060,18998,18996,18997,18999,19000,19001,19002,19003,19004,19006,19005,22051,22053,22047,22050,22045,22479,22049,22057,22059,21638,21635,18911,18912,19011,18913,19066,21636,19013,18915,19014,20602,18917,18918,21642,19016,18920,19017,20603,20601,18924,18914,18925,18926) 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))"
        }
      }
    ]
  }
}