Sandbox

SELECT 
  AVG(a.rating_value) as val 
FROM 
  cscart_discussion_rating as a 
  LEFT JOIN cscart_discussion_posts as b ON a.post_id = b.post_id 
WHERE 
  a.thread_id = 2898 
  AND b.status = 'A' 
  AND a.rating_value > 0

Query time 0.00025

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ref	PRIMARY,thread_id	thread_id	3	const	1	Using where
1	SIMPLE	b	eq_ref	PRIMARY	PRIMARY	3	mahm3t_cs443.a.post_id	1	Using where

Result

val