-- Indexes backing the public leaderboard (`GET /api/leaderboard`).
--
-- Each metric ranks users by a single pre-aggregated counter:
--   ORDER BY <col> DESC, <id> DESC LIMIT n OFFSET m
-- The endpoint is cached and low-frequency, but without these indexes every
-- request is a full scan + filesort over `user_stats` (or `user` for XP).
--
-- Single-column indexes are sufficient for the tiebreak: an InnoDB secondary
-- index implicitly appends the table's PRIMARY KEY, so
--   - KEY (`num_likes_received`) on `user_stats` already orders by
--     (`num_likes_received`, `user_id`) — exactly the ranking tuple;
--   - KEY (`experience`) on `user` already orders by (`experience`, `id`).
-- A backward index scan then yields the DESC/DESC order with no filesort.
--
-- Eligibility (`confirmed_at IS NOT NULL AND deleted_at IS NULL AND not banned`)
-- lives on `user`; for `user_stats` metrics the optimiser walks the counter
-- index in order and probes `user` by PK per row, stopping after limit+offset
-- eligible rows — cheap as long as ineligible accounts are a minority.
--
-- Write cost: one extra B-tree entry per counter mutation on `user_stats`
-- (trigger-driven, low churn) — negligible.

ALTER TABLE `user_stats`
    ADD INDEX `idx_us_num_media`              (`num_media`),
    ADD INDEX `idx_us_num_likes_received`     (`num_likes_received`),
    ADD INDEX `idx_us_num_user_follower`      (`num_user_follower`),
    ADD INDEX `idx_us_num_comments`           (`num_comments`),
    ADD INDEX `idx_us_num_views`              (`num_views`),
    ADD INDEX `idx_us_num_external_comments`  (`num_external_comments`);

-- XP leaderboard reads `user.experience` directly (no `user_stats` join).
ALTER TABLE `user`
    ADD INDEX `idx_user_experience` (`experience`);
