-- @database: hxa
--
-- Indexes supporting the session-stats rollup (`bin/session-stats-rollup.php`).
--
-- `idx_session_stats` is COVERING for the country half of the rollup:
--   SELECT country, COUNT(*), SUM(last_used_at >= …) … WHERE expires_at > NOW()
-- is answered from the index alone, without touching a single table row.
--
-- The THIRD column is what makes that true, and it is easy to get wrong: the
-- rollup counts "active" and "recent" in one pass, so it reads `last_used_at`
-- too. An index on `(expires_at, country)` alone is therefore NOT covering —
-- MySQL falls back to reading each row, and EXPLAIN drops "Using index".
-- Verified with EXPLAIN, not assumed.
--
-- Column order follows access: `expires_at` first because the query is a range
-- on it; the other two only need to be present, not sorted.
--
-- Deliberately ONE index, not two. A standalone key on `last_used_at` would
-- never be used here — the recency test is evaluated inside the `expires_at`
-- range, never as its own lookup — and every unused index is paid for on each
-- session INSERT.
--
-- The user-agent half cannot be covered (VARCHAR(255) is too wide to index
-- usefully) and stays a range scan over live sessions. Acceptable BECAUSE it
-- runs in a worker, off the request path — which is the whole reason the
-- aggregation is pre-computed rather than done on demand.

ALTER TABLE `user_session`
    ADD KEY `idx_session_stats` (`expires_at`, `country`, `last_used_at`);
