-- Index backing the global "recent comments" moderation firehose
-- (`GET /admin/comments/recent`, consumed by the Hyperion back-office).
--
-- That listing keyset-paginates every comment across ALL media and ALL users,
-- newest-first, on `(created_at DESC, id DESC)` with no scoping column. None of
-- the existing `media_comment` indexes help: they are all prefixed by
-- `media_id` / `root_id` / `parent_id` / `user_id`, so `created_at` is never a
-- usable leading column for a global ordering. Without this index MySQL falls
-- back to a full scan + filesort on every page.
--
-- `idx_mc_created (created_at, id)` lets the ORDER BY + keyset predicate
-- (`created_at < :cur OR (created_at = :cur AND id < :cur)`) walk the B-tree in
-- reverse and stop at LIMIT — no filesort, no scan. `id` is included as the
-- tiebreaker second column so the composite fully covers the keyset order.
--
-- Pure read-path optimisation; the write cost is one extra B-tree entry per
-- insert on an append-mostly table (negligible).

ALTER TABLE `media_comment`
    ADD INDEX `idx_mc_created` (`created_at`, `id`);
