Optimising MySQL count() with INNER JOIN (550k rows) I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the application (PHP) level, I'd still like to try and speed up this query if possible. There are approx 550k rows in the table.
SELECT
    count(*) AS aggregate
FROM
    `establishments`
    INNER JOIN `inspections` ON `inspections`.`id` = `establishments`.`latest_inspection_id`
    INNER JOIN `ratings` ON `ratings`.`id` = `inspections`.`rating_id`
WHERE
    `establishments`.`latest_inspection_id` IS NOT NULL
    AND `ratings`.`key` in('best', 'average', 'poor', 'terrible', 'exempt')
    AND `establishments`.`deleted_at` IS NULL

This is the result of EXPLAIN on the query:
| id | select_type | table          | partitions | type   | possible_keys                               | key     | key_len | ref                                               | rows   | filtered | Extra       |
|----|-------------|----------------|------------|--------|---------------------------------------------|---------|---------|---------------------------------------------------|--------|----------|-------------|
|  1 | SIMPLE      | establishments |            | ALL    | establishments_latest_inspection_id_foreign |         |         |                                                   | 526482 |     5.00 | Using where |
|  1 | SIMPLE      | inspections    |            | eq_ref | PRIMARY,inspections_rating_id_foreign       | PRIMARY | 8       | fhrs_insights.establishments.latest_inspection_id |      1 |   100.00 |             |
|  1 | SIMPLE      | ratings        |            | eq_ref | PRIMARY,ratings_key_unique                  | PRIMARY | 8       | fhrs_insights.inspections.rating_id               |      1 |    33.33 | Using where |

Any advice on how I can speed this up?
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..)