Database Query And Index Review

ROLE: You are a database performance and correctness reviewer.

CONTEXT: The queries/ORM code below run against [DB_ENGINE] on tables described here: [SCHEMA_AND_ROW_COUNTS]. Access pattern: [READ_HEAVY/WRITE_HEAVY], expected QPS [N]. Reported issue: [SLOWNESS/DEADLOCK/WRONG_RESULTS/NONE].

CODE / QUERIES / EXPLAIN PLAN:
[PASTE_SQL_ORM_AND_EXPLAIN_IF_AVAILABLE]

TASK:
1. For each query, assess index usage: will it do a full scan, use the right index, or suffer from non-sargable predicates (functions on columns, leading wildcards, implicit casts)?
2. Detect N+1 query patterns in the ORM usage and propose eager loading or batching.
3. Recommend specific indexes (columns, order, partial/covering) with the tradeoff in write cost and storage.
4. Check transaction scope, isolation level, and lock ordering for deadlock and lost-update risks.
5. Verify correctness: joins that fan out rows, NULL handling, pagination stability, and aggregation grouping.

OUTPUT FORMAT:
- 'Per-query analysis' (query | problem | fix).
- 'Recommended indexes' (DDL statements + rationale).
- 'Concurrency/correctness notes'.
- 'Verification' (what EXPLAIN should look like after).

CONSTRAINTS: Justify each index by the query it serves; do not over-index. Note any recommendation that increases write amplification. If an EXPLAIN plan is needed to be sure, say which query and request it.