One Composite Index on org_id and season_id Changed the Page

You’d open the leaderboard for a customer’s page in the page editor and wait past the point where you’d normally assume something had crashed.

Chasing the wrong layer

We started with the network tab. Nothing unusual, the request just sat there. Our next guess was the query itself: maybe an aggregate over a season’s worth of events was doing something expensive in application code before it ever touched the database. We went through the leaderboard assembly path looking for an N+1, a loop that fetched detail rows one event at a time and summed them in Python instead of SQL.

We didn’t find one. The aggregation was already a single query against the events table, filtered by org_id and season_id.

So we ran that query directly against the database instead of through the app. Same stall. That ruled out the application layer entirely, which meant we had spent time chasing a bug in a layer where we had no evidence it lived. The query was the problem, and the code around it was fine.

What the plan showed

With the app out of the picture, we pulled the plan for the query. It was a full table scan on events, filtered on org_id and season_id, with no index covering either column. Every leaderboard load walked every row in the table and checked each one against the filter. That table had been accumulating events across every org_id and every season_id for years. The bigger it got, the worse the page got, and nobody had connected the two because the growth was gradual and the page had probably never been fast to begin with.

The fix was one index:

CREATE INDEX idx_events_org_season ON events(org_id, season_id);

It’s composite, with org_id first since that’s the higher-selectivity filter for this workload and season_id second. After the index, the same query against the same table went from a stall to under a second. The page editor round-trip landed at about 4 seconds end to end, and most of that is render time.

Why it hid

A stalling page editor looks like a frontend problem or a network problem, and those are the usual suspects, so we checked both first. A full table scan behaves differently from slow application code. It degrades silently as data grows, with no error, no timeout, and no signal except “this used to be fine and now it isn’t.” Nobody filed a ticket saying “the events table lacks an index.” From the outside all you can observe is a page that takes forever, and that symptom maps to a dozen different causes before it maps to the right one.

Closing it out

We closed the follow-up ticket and cleaned up the customer’s open ticket with a note for the support lead to send back. We also filed the transaction-log backup gap we’d noticed while we were in there as its own separate ticket instead of fixing it in the same pass. It’s a different problem with a different blast radius, and bundling it in would have meant re-testing a change that had nothing to do with the one we’d just verified.

The order we’d use next time

Reproduce against the database directly before touching application code. It’s the cheapest way to rule out an entire layer, and it would have saved the detour through the leaderboard assembly path. Full table scans don’t announce themselves. They make a page a little worse every month until “a little worse” turns into “doesn’t finish loading,” and by then the way back is EXPLAIN on the actual query, since guessing where the time goes doesn’t get you there.

When a page gets slower as data accumulates, with no error and no timeout, run the underlying query directly against the database and check its plan before anyone investigates the application or the network. A missing index on the columns a query filters by can sit unnoticed for years because the decline is gradual. It’s also worth checking whether your largest tables have indexes that match how they are actually queried.