{"name":"optimise-slow-sql-queries","description":"Speeds up slow SQL queries in Postgres or MySQL - find the slow ones, read the EXPLAIN ANALYZE plan, add the right index, rewrite N+1 loops and wide selects, and page with cursors instead of big offsets. Use when a database query is slow, a page waits on the database, or the user mentions indexes, EXPLAIN or N+1 queries.","category":"backend","tags":["sql","database","performance","postgres"],"models":["any"],"proven":false,"passing_families":0,"model_testing":"Not yet model-tested; testing Jan 2027","url":"https://theskillvault.ai/skills/optimise-slow-sql-queries","api_url":"https://theskillvault.ai/api/skills/optimise-slow-sql-queries","raw_url":"https://theskillvault.ai/api/skills/optimise-slow-sql-queries/raw","body":"# Optimise slow SQL queries\n\nMeasure first. Most slow queries are fixed by one missing index or one query run inside a loop.\n\n## When to use\n- A page or API call is slow and the database is the bottleneck.\n- You see the same query run hundreds of times per request.\n- Someone suggested \"add an index\" and you are not sure where.\n\n## When not to use\n- You are designing tables from scratch. Use design-supabase-postgres-schema first.\n\n## Steps\n1. Find the slow queries: `pg_stat_statements` in Postgres, the slow query log in MySQL, or your ORM's query log.\n2. Copy one slow query and run `EXPLAIN ANALYZE` on it against a copy of real data.\n3. Look for \"Seq Scan\" on a big table, a large gap between estimated and actual rows, or a costly sort.\n4. Add an index on the columns in `WHERE`, `JOIN` and `ORDER BY`. Put the most selective column first in a composite index.\n5. In production Postgres, use `CREATE INDEX CONCURRENTLY` so writes are not blocked.\n6. Fix N+1 loops: load related rows in one query with a join or `WHERE id = ANY(...)`, or use your ORM's include option.\n7. Select only the columns you need instead of `SELECT *`.\n8. Replace large `OFFSET` paging with cursor paging: `WHERE id > :last ORDER BY id LIMIT 20`.\n9. Run `EXPLAIN ANALYZE` again and compare the timing. Keep the change only if it helped.\n\n```sql\nEXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;\nCREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at DESC);\n```\n\n## Gotchas\n- `EXPLAIN ANALYZE` runs the query. Wrap writes in a transaction you roll back.\n- Every index slows writes and uses space. Remove ones nothing uses.\n- Functions on a column (`WHERE lower(email) = ...`) skip a plain index. Index the expression instead.\n- Test on a copy of production data, not on production, and back up before big schema changes.\n\n## Portability\nWorks with Postgres, MySQL, SQLite and hosted options such as Supabase or Neon. If you use an ORM like Prisma or Drizzle, log the SQL it generates and tune that; the steps are the same.\n\n## Example prompt\n\"My orders page takes 4 seconds. Find the slow query, explain the plan and add the right index.\"\n\n## Done when\n- The slow query has a before-and-after `EXPLAIN ANALYZE` time.\n- New indexes are in a migration file, not added by hand.\n- No N+1 loop remains on the fixed page.\n\n---\nAdapted from \"sql-optimization-patterns\" in wshobson/agents (MIT, Copyright (c) 2024 Seth Hobson). See LICENSE.txt.","files":["LICENSE.txt"],"license":"MIT","license_name":"MIT License","license_url":"https://opensource.org/license/mit","attribution":"Based on work by Seth Hobson (wshobson/agents, sql-optimization-patterns) (Copyright (c) 2024 Seth Hobson, MIT License), https://github.com/wshobson/agents/tree/7e2fb559cf4b824b27f1fec49fe88f7511066d26/plugins/developer-essentials/skills/sql-optimization-patterns; adapted by TheSkillVault, https://theskillvault.ai","install":{"claude_code":"curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/optimise-slow-sql-queries/raw' -o ~/'.claude/skills/optimise-slow-sql-queries/SKILL.md' && curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/optimise-slow-sql-queries/files/LICENSE.txt' -o ~/'.claude/skills/optimise-slow-sql-queries/LICENSE.txt'","cursor":"curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/optimise-slow-sql-queries/raw' -o '.cursor/skills/optimise-slow-sql-queries/SKILL.md' && curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/optimise-slow-sql-queries/files/LICENSE.txt' -o '.cursor/skills/optimise-slow-sql-queries/LICENSE.txt'","agents":"curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/optimise-slow-sql-queries/raw' -o '.agents/skills/optimise-slow-sql-queries/SKILL.md' && curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/optimise-slow-sql-queries/files/LICENSE.txt' -o '.agents/skills/optimise-slow-sql-queries/LICENSE.txt'","mcp_url":"https://theskillvault.ai/mcp","cursor_mcp_deeplink":"cursor://anysphere.cursor-deeplink/mcp/install?name=theskillvault&config=eyJ1cmwiOiJodHRwczovL3RoZXNraWxsdmF1bHQuYWkvbWNwIn0%3D","mcp_config":{"mcpServers":{"theskillvault":{"url":"https://theskillvault.ai/mcp"}}}},"bundles":[],"tested_on":[{"family":"chatgpt","name":"ChatGPT","outcome":null,"date":null,"source":null,"counts":false,"stale":false},{"family":"claude","name":"Claude","outcome":null,"date":null,"source":null,"counts":false,"stale":false},{"family":"gemini","name":"Gemini","outcome":null,"date":null,"source":null,"counts":false,"stale":false},{"family":"grok","name":"Grok","outcome":null,"date":null,"source":null,"counts":false,"stale":false},{"family":"llama","name":"Llama","outcome":null,"date":null,"source":null,"counts":false,"stale":false},{"family":"deepseek","name":"DeepSeek","outcome":null,"date":null,"source":null,"counts":false,"stale":false},{"family":"mistral","name":"Mistral","outcome":null,"date":null,"source":null,"counts":false,"stale":false},{"family":"qwen","name":"Qwen","outcome":null,"date":null,"source":null,"counts":false,"stale":false},{"family":"kimi","name":"Kimi K","outcome":null,"date":null,"source":null,"counts":false,"stale":false}],"proven_rule":{"threshold":5,"window_days":365,"computed_at":"2026-11-02T03:20:33.817Z"}}