My Diagnosis Was Wrong: What Profiling a 60-Million-Row Leaderboard Actually Taught Me

An admin dashboard endpoint felt broken. It was a simple-looking ranking query — "show me the members who created the most notes, bookmarks, and highlights" — paginated at 20 rows per page, over a table that had grown past 60 million rows.

I was confident I knew the culprit. A GROUP BY with ORDER BY COUNT(*) over 60M rows, no index possible on an aggregate. I was already drafting the story in my head: how I made a catastrophically slow query fast.

Then I profiled it.

SET profiling = 1;

SELECT member_seq, COUNT(*) AS cnt FROM bible_sync_note