database-optimization

npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization --agent claude-code

Same command for any agent — swap --agent for codex, cursor, copilot.

# Database Optimization ## EXPLAIN Analysis Always run `EXPLAIN ANALYZE` before optimizing. Read the output bottom-up. ```sql -- PostgreSQL EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...; -- MySQL EXPLAIN ANALYZE SELECT ...; ``` Key metrics to watch: - **Seq Scan** on large tables = missing index - **Nested Loop** with high row count = consider hash/merge join - **Sort** without index = add index on sort column - **Rows estimated vs actual** divergence = stale statistics, run `ANALYZE` ## Index Strategies ### B-tree (default, most cases) ```sql CREATE INDEX idx_users_email ON users (email); CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC); ``` Use for: equality, range queries, sorting. Column order matters in composite indexes: put equality columns first, then range/sort columns. ### Partial Index (PostgreSQL) ```sql CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending'; ``` Use when queries always filter on a specific condition. Dramatically smaller than full indexes. ### GIN (PostgreSQL - arrays, JSONB, full-text) ```sql CREATE INDEX idx_products_tags ON products USING GIN (tags); CREATE INDEX idx_docs_search ON documents USING