What is the benefit of using EXPLAIN ANALYZE with this skill?

The skill helps you interpret the detailed metrics from EXPLAIN ANALYZE, such as actual execution time and buffer usage, allowing you to verify that your optimizations are actually working.

Is this useful for database schema design?

Absolutely. It offers best practices for choosing efficient data types and planning index strategies during the design phase to ensure the database scales effectively as data grows.

Does it support specific databases like PostgreSQL or MySQL?

While many patterns are universal, the skill includes specialized advice for PostgreSQL (like GIN/GiST indexes and partitioning) and MySQL-specific optimization hints.

How does this skill help with slow database queries?

It provides step-by-step guidance on analyzing query execution plans to identify bottlenecks like full table scans and recommends specific indexing or query refactoring strategies to improve execution speed.

Can this skill help resolve N+1 query problems?

Yes, it provides specific patterns for identifying N+1 issues in application logic and offers SQL-based solutions like batch loading or optimized JOINs to reduce database roundtrips.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: Microck

byMicrock

•

Database Management

Optimizes SQL database performance through advanced indexing strategies, execution plan analysis, and efficient query refactoring.

This skill empowers developers to transform slow database operations into high-performance queries by applying systematic optimization techniques. It provides expert guidance on interpreting execution plans (EXPLAIN), implementing sophisticated indexing strategies such as composite, partial, and GIN indexes, and resolving common performance bottlenecks like N+1 queries and inefficient pagination. Whether you are debugging a production bottleneck or designing a scalable schema from scratch, this skill offers the patterns and best practices needed to reduce database load, minimize latency, and lower infrastructure costs across various RDBMS platforms including PostgreSQL and MySQL.

Key Features

01Techniques for high-performance pagination and large-scale batch data processing

02Patterns for eliminating N+1 query bottlenecks and optimizing complex JOIN operations

03Implementation of advanced indexing strategies including covering, partial, and expression indexes

04Guidance on advanced database features like partitioning, materialized views, and vacuuming

05In-depth analysis and interpretation of EXPLAIN and EXPLAIN ANALYZE query plans

0681 GitHub stars

Use Cases

01Reducing cloud database costs by optimizing resource consumption and query efficiency

02Designing performant database schemas for data-intensive applications

03Refactoring legacy application queries that are causing performance timeouts

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add microck/ordinary-claude-skills sql-optimization-patterns

For use in Claude.ai and ChatGPT

Download Skill