Optimising MySQL count() with INNER JOIN (550k rows)
First I would try index on establishments (latest_inspection_id, deleted_at).
Also it may has a sense to eliminate join with ratings table using condition directly on inspections.rating_id instead. I mean AND inspections.rating_id IN (..list of corresponding ids..)