optimizing-query-performance
Optimize queries with indexes, batching, and efficient Prisma operations for production performance.
$ インストール
git clone https://github.com/djankies/claude-configs /tmp/claude-configs && cp -r /tmp/claude-configs/prisma-6/skills/optimizing-query-performance ~/.claude/skills/claude-configs// tip: Run this command in your terminal to install the skill
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:
| 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
Repository
