Detect N+1 queries, analyze slow queries with EXPLAIN, identify missing indexes, and ensure safe online index migrations for MySQL/MariaDB. Use when optimizing query performance, preventing performance regressions, or debugging slow endpoints. Complements database-migrations skill which covers index creation syntax.
Detect N+1 queries, analyze slow queries with EXPLAIN, identify missing indexes, and ensure safe online index migrations for MySQL/MariaDB. Use when optimizing query performance, preventing performance regressions, or debugging slow endpoints. Complements database-migrations skill which covers index creation syntax.
Query Performance Analysis & Index Management
Context (Input)
Use this skill when:
New or modified endpoints are slow
Profiler shows many database queries for single operation
Need to detect N+1 query problems
Query execution time is high
Slow query warnings in MySQL logs
Performance regression after code changes
Planning safe index migrations for production
Need to verify index effectiveness
Task (Function)
Analyze query performance, detect N+1 issues, identify missing indexes, and create safe online index migrations with verification steps.
Success Criteria:
N+1 queries detected and fixed
Slow queries identified with EXPLAIN analysis
Missing indexes detected and added
Query performance meets acceptable thresholds (<100ms for reads, <500ms for writes)
Index migrations are safe for production (minimal downtime)
Performance regression tests added
TL;DR - Quick Performance Checklist
Before Merging Code:
Run endpoint with profiler - check query count
No N+1 queries (queries in loops)
Slow queries (<100ms) analyzed with EXPLAIN
Missing indexes identified and added
Eager loading used where appropriate
Query count reasonable for operation (<10 queries ideal)
Performance test added to prevent regression
When Adding Indexes:
Index covers actual query patterns
Composite index field order correct
Index creation uses ALGORITHM=INPLACE when possible
Most index operations are non-blocking with ALGORITHM=INPLACE
InnoDB allows concurrent reads and writes during index builds
Note: Large tables may still cause brief locks at start/end
Recommendation: For production index builds, schedule during low-traffic periods for very large tables.
Production Migration Strategy
Create Doctrine migration with index
Use ALGORITHM=INPLACE for non-blocking creation
Schedule during low traffic for large tables
Run migration: make doctrine-migrations-migrate
Verify index created: SHOW INDEX FROM table_name
Verify index is used: Run EXPLAIN on queries
Measure performance improvement
// Migration example with online DDLpublicfunctionup(Schema $schema): void{
$this->addSql('CREATE INDEX idx_users_email ON users (email) ALGORITHM=INPLACE LOCK=NONE');
}
