-- @database: main
--
-- Covering index for the demographics rollup (`bin/demographics-rollup.php`).
--
-- The rollup runs ONE scan and derives every dimension from it:
--   SELECT sex, TIMESTAMPDIFF(YEAR, birthdate, :today), YEAR(joined_at), COUNT(*)
--     FROM `user`
--    WHERE deleted_at IS NULL AND confirmed_at IS NOT NULL
--    GROUP BY 1, 2, 3
--
-- Measured before this index on ~987k accounts: 5.3 s, `EXPLAIN` picking
-- `idx_user_purge` and reading every matching ROW off the clustered index.
--
-- Age is a DERIVED value, so no index can ever turn this into a seek — the
-- expression has to be evaluated once per account no matter what. What the
-- index buys is WHAT gets read: all five referenced columns are present, so the
-- scan stays inside a ~40 MB index instead of walking the full user rows
-- (email, bio, password hash and the rest). That is the only available win, and
-- it is the reason all five columns are listed even though the last three are
-- never used to filter or sort.
--
-- Column order follows the WHERE clause: `deleted_at` first (IS NULL is usable
-- as a `ref` lookup), then `confirmed_at`. The trailing three only need to be
-- PRESENT for `EXPLAIN` to report "Using index" — sorting them would buy
-- nothing, since the GROUP BY is on expressions, not on the raw columns.
--
-- ALGORITHM/LOCK are explicit ON PURPOSE: on a million-row production table an
-- accidental table-copy would lock writes for the duration. Stated this way the
-- statement FAILS LOUDLY if the server cannot build the index online, instead
-- of silently taking the site down.

ALTER TABLE `user`
    ADD KEY `idx_user_demographics` (`deleted_at`, `confirmed_at`, `sex`, `birthdate`, `joined_at`),
    ALGORITHM=INPLACE, LOCK=NONE;
