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 (
    10076, 10945, 8864, 397, 479, 10219, 
    3317, 10188, 9848, 8880, 404, 524
  ) 
  AND cscart_discussion.object_type = "P" 
  AND cscart_discussion.company_id = 2 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00051

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_discussion range object_id,company_id object_id 10 12 Using index condition
1 SIMPLE cscart_discussion_posts ref thread_id,thread_id_2 thread_id 3 mahm3t_cs443.cscart_discussion.thread_id 4 Using where
1 SIMPLE cscart_discussion_rating eq_ref PRIMARY,thread_id PRIMARY 3 mahm3t_cs443.cscart_discussion_posts.post_id 1 Using where

Result

product_id average_rating discussion_type discussion_thread_id
397 B 43
404 B 338
479 B 104
524 B 174
3317 B 3975
8864 B 4021
8880 B 4037
9848 B 4116
10076 B 4662
10188 B 8508
10219 B 5189
10945 B 10865