data-sql-optimization
SQL Optimization — Comprehensive Reference
This skill provides actionable checklists, patterns, and templates for transactional (OLTP) SQL optimization: measurement-first triage, EXPLAIN/plan interpretation, balanced indexing (avoiding over-indexing), performance monitoring, schema evolution, migrations, backup/recovery, high availability, and security.
Supported Platforms: PostgreSQL, MySQL, SQL Server, Oracle, SQLite
For OLAP/Analytics: See data-lake-platform (ClickHouse, DuckDB, Doris, StarRocks)
Quick Reference
| Task | Tool/Framework | Command | When to Use |
|---|---|---|---|
| Query Performance Analysis | EXPLAIN ANALYZE | EXPLAIN (ANALYZE, BUFFERS) SELECT ... (PG) / EXPLAIN ANALYZE SELECT ... (MySQL) |
Diagnose slow queries, identify missing indexes |
| Find Slow Queries | pg_stat_statements / slow query log | SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; |
Identify performance bottlenecks in production |
| Index Analysis | pg_stat_user_indexes / SHOW INDEX | SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0; |
Find unused indexes, validate index coverage |
| Schema Migration | Flyway / Liquibase | flyway migrate / liquibase update |
Version-controlled database changes |
| Backup & Recovery | pg_dump / mysqldump | pg_dump -Fc dbname > backup.dump |
Point-in-time recovery, disaster recovery |
More from vasilyu1983/ai-agents-public
product-management
Founder-PM toolkit for discovery, roadmaps, prioritization, and PMF measurement. Use when planning product strategy, metrics, or roadmaps.
684software-architecture-design
Designs system structure across monolith/microservices/serverless. Use when structuring systems, scaling, decomposing monoliths, or choosing patterns.
519software-ui-ux-design
Designs and audits UI/UX with WCAG 2.2 accessibility. Use when designing flows, running heuristic reviews, or defining design systems.
383qa-testing-playwright
E2E web testing with Playwright. Use when writing tests, debugging flakes, or setting up CI with selectors, sharding, and network mocking.
372document-pdf
Extract text/tables from PDFs, create formatted PDFs, merge/split/rotate, and handle forms. Use for any PDF generation or parsing task.
328qa-testing-strategy
Risk-based test strategy for software delivery. Use when defining coverage, setting CI gates, managing flaky tests, or establishing release criteria.
317