optimizing-sql-queries

Execute use when you need to work with query optimization. This skill provides query performance analysis with comprehensive guidance and automation. Trigger with phrases like "optimize queries", "analyze performance", or "improve query speed".

Aller à l'installation

Dépôt: jeremylongshore/claude-code-plugins-plus-skills
Dernière activité de la source: 17 juillet 2026 à 22:18
Langue détectée de SKILL.md: anglais
Étoiles: 2 604
Forks: 378

Options d'installation

Le prompt qui vérifie d'abord la source est sélectionné par défaut. Vous pouvez passer à une commande directe ou télécharger une copie locale.

Installer avec Codex ou Claude Copiez ce prompt, collez-le dans Codex, Claude ou un autre assistant, puis laissez-le vérifier la page du skill et l'installer pour vous.

Une commande directe contourne le prompt de vérification. Examinez la source avant de l'exécuter.

npx skills add https://github.com/jeremylongshore/claude-code-plugins-plus-skills --skill optimizing-sql-queries

La commande reste sur une seule ligne. Faites défiler horizontalement pour la vérifier avant de la copier.

Vous préférez une copie locale ? Téléchargez les fichiers actuellement disponibles dans SkillsMP.

Vérifiez les fichiers source

Lisez SKILL.md et les fichiers associés affichés par SkillsMP avant de décider de l'installer.

Explorateur de fichiers

6 fichiers

SKILL.md

Instructions source · Aperçu en lecture seule

Retour à la recherche Retour à l’installation

clerk-core-workflow-a

Implement user sign-up and sign-in flows with Clerk. Use when building authentication UI, customizing sign-in experience, or implementing OAuth social login. Trigger with phrases like "clerk sign-in", "clerk sign-up", "clerk login flow", "clerk OAuth", "clerk social login".

2026-07-23

clerk-core-workflow-b

Implement session management and middleware with Clerk. Use when managing user sessions, configuring route protection, or implementing token refresh and custom JWT templates. Trigger with phrases like "clerk session", "clerk middleware", "clerk route protection", "clerk token", "clerk JWT".

2026-07-23

clerk-enterprise-rbac

Configure enterprise SSO, role-based access control, and organization management. Use when implementing SSO integration, configuring role-based permissions, or setting up organization-level controls. Trigger with phrases like "clerk SSO", "clerk RBAC", "clerk enterprise", "clerk roles", "clerk permissions", "clerk organizations".

2026-07-23

Tout voir dans ce dépôt

SQL Query Optimizer

Overview

Rewrite SQL queries for maximum performance by eliminating anti-patterns, restructuring JOINs, leveraging window functions, and applying database-specific optimizations for PostgreSQL and MySQL. This skill takes a slow query and its execution plan as input and produces an optimized version with measurable improvement, along with any supporting index changes needed.

Prerequisites

The slow SQL query text and its current execution time

EXPLAIN ANALYZE output (PostgreSQL) or EXPLAIN FORMAT=JSON output (MySQL) for the query

Table row counts and approximate data distribution for involved tables

psql or mysql CLI for testing rewrites

Knowledge of the application's acceptable result ordering and NULL handling requirements

Instructions

Examine the original query structure and identify common anti-patterns:

SELECT * instead of specific columns (forces unnecessary I/O)
WHERE column IN (SELECT ...) that can be rewritten as JOIN or EXISTS
DISTINCT used to mask duplicate rows from incorrect JOINs
Functions applied to indexed columns in WHERE clauses (WHERE UPPER(name) = 'FOO')
OR conditions that prevent index usage
NOT IN with nullable columns (produces wrong results and poor plans)

Analyze the execution plan to identify the most expensive operation nodes. Focus optimization effort on the node consuming the most time or processing the most rows.

Rewrite subqueries as JOINs where possible. Convert correlated subqueries to lateral joins (PostgreSQL) or derived tables. Replace IN (SELECT ...) with EXISTS (SELECT 1 ...) for existence checks since EXISTS short-circuits after the first match.

Optimize JOIN ordering for the query planner: place the most selective table (fewest matching rows after WHERE filters) as the driving table. Use JOIN hints only as a last resort since the optimizer usually picks the correct order with accurate statistics.

Replace multiple OR conditions on the same column with IN (...): change WHERE status = 'active' OR status = 'pending' to WHERE status IN ('active', 'pending'). For OR across different columns, consider UNION ALL of two simpler queries.

Apply window functions to replace self-joins or correlated subqueries. Use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) for top-N-per-group queries instead of GROUP BY with subqueries.

Leverage CTEs (Common Table Expressions) for readability but be aware that PostgreSQL versions before 12 materialize all CTEs. For performance-critical queries on older PostgreSQL, inline the CTE as a subquery.

Optimize aggregation queries by filtering before grouping (WHERE is more efficient than HAVING for non-aggregate conditions), using partial indexes for filtered aggregates, and considering materialized views for expensive recurring aggregations.

Test the rewritten query with EXPLAIN ANALYZE and compare execution time, row estimates vs. actuals, and buffer usage against the original. The optimized version should show fewer rows processed, index scans replacing sequential scans, and lower total execution time.

Document each change made, the reason for the change, and the measured impact so the development team understands and can apply similar patterns to future queries.

Output

Optimized SQL query with comments explaining each structural change

Before/after execution plans showing performance improvement

Index recommendations (CREATE INDEX statements) needed to support the optimized query

Anti-pattern report listing issues found in the original query with explanations

Performance metrics comparison (execution time, rows scanned, buffer hits)

Error Handling

Error

Cause

Solution

Rewritten query returns different results

JOIN type change (INNER vs LEFT) or NULL handling difference

Verify result sets match with EXCEPT query; preserve original JOIN types; handle NULLs explicitly with COALESCE

Optimized query slower than original

Statistics outdated causing planner to choose wrong plan

Run ANALYZE on involved tables; compare estimated rows vs actual rows in EXPLAIN; consider SET enable_seqscan = off to test alternative plans

CTE materialization hurting performance

PostgreSQL <12 materializes CTEs preventing predicate pushdown

Inline the CTE as a subquery; upgrade PostgreSQL; add AS NOT MATERIALIZED hint in PostgreSQL 12+

Window function query uses excessive memory

Large partition sizes with ORDER BY in window specification

Add LIMIT to outer query; use index matching the PARTITION BY and ORDER BY columns; increase work_mem for the session

UNION ALL produces duplicates

Overlapping conditions in constituent queries

Add mutually exclusive WHERE conditions to each branch; or use UNION (with dedup cost) if overlap is unavoidable

Examples

Converting correlated subquery to JOIN: Original: SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'US') taking 8 seconds with sequential scan on orders. Rewrite: SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.region = 'US' using index on orders.customer_id reduces to 120ms.

Top-N per group with window function: Original uses self-join to find the 3 most recent orders per customer (15 seconds). Rewrite:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders) sub WHERE rn <= 3

with index on (customer_id, created_at DESC) completes in 400ms.

Eliminating DISTINCT from incorrect JOIN: SELECT DISTINCT o.* FROM orders o JOIN line_items li ON o.id = li.order_id WHERE li.amount > 100 scans all line items. Rewrite: SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM line_items li WHERE li.order_id = o.id AND li.amount > 100) eliminates the deduplication step and halves execution time.

Resources

name	optimizing-sql-queries
description	Execute use when you need to work with query optimization. This skill provides query performance analysis with comprehensive guidance and automation. Trigger with phrases like "optimize queries", "analyze performance", or "improve query speed".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:), Bash(mysql:), Bash(mongosh:*)
version	1.27.0
author	Jeremy Longshore <jeremy@intentsolutions.io>
license	MIT
tags	["database","performance","optimizing-sql"]
compatibility	Designed for Claude Code, also compatible with Codex and OpenClaw

name	optimizing-sql-queries
description	Execute use when you need to work with query optimization. This skill provides query performance analysis with comprehensive guidance and automation. Trigger with phrases like "optimize queries", "analyze performance", or "improve query speed".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:), Bash(mysql:), Bash(mongosh:*)
version	1.27.0
author	Jeremy Longshore <jeremy@intentsolutions.io>
license	MIT
tags	["database","performance","optimizing-sql"]
compatibility	Designed for Claude Code, also compatible with Codex and OpenClaw

optimizing-sql-queries

Informations de source

Options d'installation

Vérifiez les fichiers source

Plus depuis ce dépôt

Plus depuis ce dépôt

SQL Query Optimizer

Overview

Prerequisites

Instructions

Output

Error Handling

Examples

Resources