Route popularity updating is really slow

[Wotuu]

This causes a 40s query to run every hour.

[Wotuu]

Something like this:

EXPLAIN SELECT dungeon_routes.*, COUNT(page_views.model_id) as page_view_count
FROM `dungeon_routes` 
INNER JOIN page_views ON page_views.model_id = dungeon_routes.id AND page_views.model_class = "App\\Models\\DungeonRoute\\DungeonRoute"
WHERE dungeon_routes.published_state_id = 4
AND page_views.created_at > "2024-09-25 12:10:00"
GROUP BY page_views.model_id

[Wotuu]

Current query

UPDATE dungeon_routes, (
                SELECT model_id, count(0) as views
                FROM page_views
                WHERE page_views.model_class = "App\\Models\\DungeonRoute\\DungeonRoute"
                AND page_views.created_at > "2024-09-25 12:10:00"
                GROUP BY page_views.model_id
            ) as page_views,
            (
                SELECT MAX(id) as ids
                FROM mapping_versions
                GROUP BY mapping_versions.dungeon_id
            ) as latest_mapping_version_ids
            SET dungeon_routes.popularity = page_views.views
            /*
                This will calculate a number between 1 and 0 depending on the age of the route. A new route will generate 1.
                A route at popularityFalloffDays days will produce 0. This will ensure that old routes fall off the
                popularity board over time and the overview stays fresh
            */
                * GREATEST(0, (1 - DATEDIFF(NOW(), dungeon_routes.updated_at) / 30))
            /*
                If your route is cloned, it cannot show up in any popularity pages
             */
                * IF(dungeon_routes.clone_of IS NOT NULL, 1, 0)
            /*
                Adds a penalty if your route does not use the latest mapping version for your dungeon
             */
                * IF(FIND_IN_SET(dungeon_routes.mapping_version_id, latest_mapping_version_ids.ids) > 1, 1, 0.25)
            WHERE dungeon_routes.published_state_id IN (4)
            AND dungeon_routes.id = page_views.model_id

[Wotuu]

Long term - have to check if the optimized indices have worked or not.