majiayu000 Key capabilities: Strategic index placement (@@index, @@unique) · Efficient batch operations (createMany, transactions) · Query analysis & N+1 prevention · Field selection optimization & cursor pagination
Phase 2 — Optimize: Add indexes for filtered/sorted fields; replace loops with batch operations; select only needed fields; use cursor pagination for large datasets
Phase 3 — Validate: Measure execution time before/after; verify index usage with EXPLAIN ANALYZE; monitor connection pool under load
Quick Reference
Index Strategy:
| Scenario | Index Type | Example |
|---|---|---|
| Single field filter | @@index([field]) |
@@index([status]) |
| Multiple field filter | @@index([field1, field2]) |
@@index([userId, status]) |
| Sort + filter | @@index([filterField, sortField]) |
@@index([status, createdAt]) |
Batch Operations:
| Operation | Slow (Loop) | Fast (Batch) |
|---|---|---|
| Insert | for...await create() |
createMany() |
| Update | for...await update() |
updateMany() |
| Delete | for...await delete() |
deleteMany() |
Performance Gains: Indexes (10-100x) · Batch ops (50-100x for 1000+ records) · Cursor pagination (constant vs O(n))
SHOULD: Test indexes with production data; chunk 100k+ batches into smaller sizes; use @@index([field1, field2]) for multi-field filters; remove unused indexes
NEVER: Add indexes without performance measurement; offset pagination beyond page 100 on large tables; fetch all
fields when only needing few; loop with individual creates/updates; ignore slow query warnings
Verify Index Usage: Run EXPLAIN ANALYZE; confirm "Index Scan" vs "Seq Scan"
Monitor Production: Track P95/P99 latency; expect reduced slow query frequency
Check Write Performance: Writes may increase 10-30% per index if rarely-used; consider removal
References
- Index Strategy:
references/index-strategy.md— indexing patterns and trade-offs - Batch Operations:
references/batch-operations.md— bulk operations and chunking - Query Monitoring:
references/query-monitoring.md— logging setup and slow query analysis - Field Selection:
references/field-selection.md— select vs include patterns and N+1 prevention - Optimization Examples:
references/optimization-examples.md— real-world improvements - Next.js Integration: Next.js plugin for App Router-specific patterns
- Serverless: CLIENT-serverless-config skill for connection pooling