Why "Popular Articles" Stay Stale — and How to Fix It with a 7-Day Rolling Score

The Problem: Total Score, Not Current Score

I opened the public KotaPortal page last week and the "Popular Articles" section showed the exact same items from two weeks ago. I thought it was just a cache issue. It wasn't — the sort=popular query used total view_count since day one, so old articles with high views always won.

The Fix: 7-Day Rolling Score + Deterministic Tiebreak

Commit 40ade74 fixes this with two things: a 7-day rolling score (entity_view_daily for today + 6 previous days) + deterministic tiebreak (view_score → view_count → id). For filler so the section doesn't feel frozen, add sort=random using ORDER BY RAND() limited to 9 rows.

Why ORDER BY RAND() Is Safe Here

Because the limit is tiny (9 rows). Vlad Mihalcea says ORDER BY RANDOM() is slow on large result sets but fine on small ones [3]. StackOverflow has the JOIN (SELECT id ORDER BY RAND() LIMIT N) pattern that's fast because sorting only hits the indexed ID column [2].

Implementation & Performance

func publicOrderClause(sort string) (string, []any) {
    switch sort {
    case "popular":
        return `ORDER BY view_score DESC, view_count DESC, id ASC`, nil
    case "random":
        return `ORDER BY RAND()`, nil
    default:
        return `ORDER BY created_at DESC`, nil
    }
}

view_score is computed from entity_view_daily over 7 days — this makes articles being read now rise, not ones read before. Tiebreak view_count → id makes ordering deterministic, not random on every refresh.

MySQL can use an index for ORDER BY when columns match, avoiding filesort [1]. If entity_view_daily has an index on (entity_id, date), the 7-day query is fast.

Test coverage: sort_clause_test.go (unit), popular_sort_test.go (integration), sort_passthrough_test.go (passthrough). All green.

Sources

1. MySQL ORDER BY Optimization — index usage avoids filesort
2. StackOverflow: JOIN + ORDER BY RAND() on ID column only = fast on 200K rows
3. Vlad Mihalcea: ORDER BY RANDOM() slow on large result sets, fine on small ones