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 = 3170 
  AND d.object_type = 'C' 
  AND type IN ('B', 'R') 
  AND dp.status = 'A' 
  AND dr.rating_value > 0

Query time 0.00068

JSON explain

{
  "query_block": {
    "select_id": 1,
    "message": "Impossible WHERE noticed after reading const tables"
  }
}

Result

AVG (dr.rating_value)