skills.rest
Hieubkav
HieubkavCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

sql-optimization-patterns

Analyze SQL query plans with EXPLAIN and implement indexing strategies.

Updated Oct 26, 2025
Search Tags:#indexing#SQL#EXPLAIN#N+1
One-click install
npx skills add https://github.com/Hieubkav/wincellarCloneBackend --skill sql-optimization-patterns-hieubkav
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/Hieubkav/wincellarCloneBackend/tree/main/.claude/skills/database/sql-optimization-patterns
Command: npx skills add https://github.com/Hieubkav/wincellarCloneBackend --skill sql-optimization-patterns-hieubkav

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

Systematic SQL optimization with indexing, EXPLAIN analysis, and pattern-based improvements.

Core Features & Use Cases

  • EXPLAIN analysis and slow query detection
  • Indexing strategies (composite, partial, covering)
  • Pattern-based optimizations (N+1, pagination, joins)

Quick Start

Analyze a slow query and propose indexes or changes.

Frequently Asked Questions about sql-optimization-patterns

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I identify and fix slow SQL queries using EXPLAIN?▼

EXPLAIN ANALYZE shows query execution plans revealing performance bottlenecks. Run EXPLAIN ANALYZE on slow queries to see sequential scans, join strategies, and row estimates; use this data to add indexes or restructure queries for faster execution.

What indexing strategies eliminate N+1 query problems?▼

N+1 occurs when queries repeat for each row instead of batching. Composite indexes covering all query columns, joining techniques, and query restructuring reduce round trips. Covering indexes return results without table lookups, eliminating the extra fetches.

When should I use partial indexes versus composite indexes?▼

Partial indexes filter to specific rows (e.g., active records only), reducing size and maintenance cost for common queries. Composite indexes cover multiple columns for joins and filters. Use partial indexes for subset queries; composite indexes for multi-column predicates.

How do materialized views and partitioning improve query performance?▼

Materialized views pre-compute and cache expensive query results, eliminating recalculation. Partitioning splits large tables by key ranges, enabling parallel queries and faster scans. Both reduce load and cost for high-volume workloads.

Can I optimize queries without modifying application code?▼

Yes. Query hints and index tuning optimize execution without code changes. EXPLAIN analysis identifies missing indexes; adding B-Tree, Hash, GIN, or GiST indexes accelerates queries. Parallel query execution and monitoring catch future performance issues.

What's the difference between B-Tree and GIN indexes for my queries?▼

B-Tree indexes handle range and equality queries efficiently on scalar data. GIN indexes excel with array and full-text search. Choose B-Tree for standard columns; GIN for arrays, JSON, or text fields requiring complex matching.

Related Skills

Explore More Software Engineering Skills→
Impertio-StudioImpertio-Studio
2

mariadb-errors-slow-queries

Diagnose and optimize slow MariaDB queries using logs, EXPLAIN, and performance schema.

Official
Advanced
DexploarerDexploarer
6

database-query-optimizer

Analyze EXPLAIN plans and recommend indexes for PostgreSQL, MySQL, and MongoDB.

Community
Advanced
bbara04bbara04

optimizing-performance

Profile applications to identify and resolve performance bottlenecks.

Community
Advanced
rnavarychrnavarych
14

caching-strategies

Explain caching patterns like cache-aside, write-through, and multi-level architectures.

Community
Advanced