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 (
    9845, 9853, 9843, 478, 3321, 514, 8295, 
    480, 9851, 9842, 9849, 479, 9848, 9838, 
    9850, 9839, 9855, 9854, 9844, 9840, 
    9841, 9852
  ) 
  AND cscart_discussion.object_type = "P" 
  AND cscart_discussion.company_id = 3 
GROUP BY 
  cscart_discussion.object_id

Query time 0.00114

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 22 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
478 B 1702
479 5.0000 B 1701
480 B 1703
514 5.0000 B 2092
3321 5.0000 B 2219
8295 B 1908
9838 5.0000 B 3241
9839 B 3240
9840 B 3239
9841 B 3238
9842 B 5805
9843 5.0000 B 5803
9844 B 5804
9845 5.0000 B 5801
9848 B 4110
9849 5.0000 B 4111
9850 B 4107
9851 B 4108
9852 B 4109
9853 B 5494
9854 B 5802
9855 B 5496