Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Route popularity updating is really slow #2534

Open
Wotuu opened this issue Oct 2, 2024 · 3 comments
Open

Route popularity updating is really slow #2534

Wotuu opened this issue Oct 2, 2024 · 3 comments
Assignees
Labels
bug Something isn't working long term Issues that will be picked up over a longer period of time

Comments

@Wotuu
Copy link
Contributor

Wotuu commented Oct 2, 2024

This causes a 40s query to run every hour.

@Wotuu Wotuu added the bug Something isn't working label Oct 2, 2024
@Wotuu
Copy link
Contributor Author

Wotuu commented Oct 2, 2024

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 added a commit that referenced this issue Oct 2, 2024
…aster. But it needs to be written differently.
@Wotuu
Copy link
Contributor Author

Wotuu commented Oct 2, 2024

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 added a commit that referenced this issue Oct 2, 2024
@Wotuu Wotuu self-assigned this Oct 2, 2024
@Wotuu Wotuu added the long term Issues that will be picked up over a longer period of time label Oct 2, 2024
@Wotuu
Copy link
Contributor Author

Wotuu commented Oct 2, 2024

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

Wotuu added a commit that referenced this issue Oct 6, 2024
…pass. Just in case deployment times out because of the creation of this index.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working long term Issues that will be picked up over a longer period of time
Projects
None yet
Development

No branches or pull requests

1 participant