Sandbox

SELECT 
  AVG (dr.rating_value) 
FROM 
  cscart_discussion_rating AS dr 
  LEFT JOIN cscart_discussion_posts AS dp ON dp.post_id = dr.post_id 
  LEFT JOIN cscart_discussion AS d ON d.thread_id = dp.thread_id 
WHERE 
  d.object_id = 0 
  AND d.object_type = 'E' 
  AND type IN ('B', 'R') 
  AND dp.status = 'A' 
  AND dr.rating_value > 0

Query time 0.00049

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	d	const	PRIMARY,object_id	object_id	6	const,const	1
1	SIMPLE	dp	ref	PRIMARY,thread_id,thread_id_2	thread_id	3	const	2	Using where
1	SIMPLE	dr	eq_ref	PRIMARY	PRIMARY	3	hobilifedt.dp.post_id	1	Using where

Result

AVG (dr.rating_value)
4.5000