optimizing-query-performance

---

name: optimizing-query-performance description: Optimize queries with indexes, batching, and efficient Prisma operations for production performance. allowed-tools: Read, Write, Edit, Bash version: 1.0.0

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:

Batch Operations:

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