Backend · Skill

Optimise slow SQL queries

optimise-slow-sql-queries

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.

Works with: Any AI Not yet model-tested; testing Jan 2027

View raw SKILL.mdInstall

Paste it into ChatGPT, Claude, Gemini or any AI chat, then tell it about your project. How it works

Install in Claude Code, Cursor or Codex (optional)

Only for AI coding tools that read skill folders. Run one line in a terminal, in your project folder. It downloads this skill's text files from TheSkillVault and nothing else; you can read them first with “View raw SKILL.md”.

Claude Code (all your projects)

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 (this project)

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'

Codex and other Agent Skills tools (this project)

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'

Or connect the whole library over MCP

Add TheSkillVault to Cursor or add this to your app's MCP settings (read-only; help):

{
  "mcpServers": {
    "theskillvault": {
      "url": "https://theskillvault.ai/mcp"
    }
  }
}

Measure first. Most slow queries are fixed by one missing index or one query run inside a loop.

When to use

  • A page or API call is slow and the database is the bottleneck.
  • You see the same query run hundreds of times per request.
  • Someone suggested "add an index" and you are not sure where.

When not to use

  • You are designing tables from scratch. Use design-supabase-postgres-schema first.

Steps

  1. Find the slow queries: pg_stat_statements in Postgres, the slow query log in MySQL, or your ORM's query log.
  2. Copy one slow query and run EXPLAIN ANALYZE on it against a copy of real data.
  3. Look for "Seq Scan" on a big table, a large gap between estimated and actual rows, or a costly sort.
  4. Add an index on the columns in WHERE, JOIN and ORDER BY. Put the most selective column first in a composite index.
  5. In production Postgres, use CREATE INDEX CONCURRENTLY so writes are not blocked.
  6. Fix N+1 loops: load related rows in one query with a join or WHERE id = ANY(...), or use your ORM's include option.
  7. Select only the columns you need instead of SELECT *.
  8. Replace large OFFSET paging with cursor paging: WHERE id > :last ORDER BY id LIMIT 20.
  9. Run EXPLAIN ANALYZE again and compare the timing. Keep the change only if it helped.
EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
CREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at DESC);

Gotchas

  • EXPLAIN ANALYZE runs the query. Wrap writes in a transaction you roll back.
  • Every index slows writes and uses space. Remove ones nothing uses.
  • Functions on a column (WHERE lower(email) = ...) skip a plain index. Index the expression instead.
  • Test on a copy of production data, not on production, and back up before big schema changes.

Portability

Works 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.

Example prompt

"My orders page takes 4 seconds. Find the slow query, explain the plan and add the right index."

Done when

  • The slow query has a before-and-after EXPLAIN ANALYZE time.
  • New indexes are in a migration file, not added by hand.
  • No N+1 loop remains on the fixed page.

---

Adapted from "sql-optimization-patterns" in wshobson/agents (MIT, Copyright (c) 2024 Seth Hobson). See LICENSE.txt.

Bundled files

Licence

Adapted from Seth Hobson (wshobson/agents, sql-optimization-patterns) (Copyright (c) 2024 Seth Hobson), MIT License. Adapted by TheSkillVault: condensed into a measure-fix-measure loop for app builders with original examples; long pattern sections dropped; added production safety notes, gotchas and done-when.

Licence: MIT License. Attribution when you reuse it: 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