Does it support both PostgreSQL and MySQL?

Yes, the skill includes optimization patterns, indexing strategies, and EXPLAIN syntax applicable to both PostgreSQL and MySQL environments.

What is cursor-based pagination?

It is a performance-focused approach to pagination that uses unique keys (like IDs or timestamps) as anchors rather than using OFFSET, which becomes increasingly slow on large datasets.

How does this skill help with slow SQL queries?

It provides structured patterns for analyzing execution plans with EXPLAIN and suggests specific indexing or query refactoring strategies to eliminate performance bottlenecks.

Can this skill help reduce my database hosting costs?

Yes, by optimizing queries to use fewer resources (CPU, Memory, and IOPS) and implementing better indexing, you can often run on smaller, more cost-effective database instances.

Can it help resolve N+1 query problems?

Absolutely. It offers specific code patterns for identifying and transforming N+1 loops into efficient JOINs or batch loading operations to minimize database round-trips.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: duanbiao2000

byduanbiao2000

0•

Database Management

Optimizes database performance through systematic query analysis, advanced indexing strategies, and execution plan interpretation.

This skill empowers developers to transform sluggish database operations into high-performance queries by implementing industry-standard optimization patterns. It provides expert guidance on interpreting EXPLAIN plans, selecting the correct index types (B-Tree, GIN, BRIN, etc.), and resolving common performance bottlenecks like the N+1 query problem or inefficient OFFSET pagination. Whether you are designing a new schema or debugging a production slowdown, this skill helps reduce database load, improve application responsiveness, and ensure scalability for growing datasets across PostgreSQL and MySQL environments.

Key Features

01Implementation of advanced indexing strategies including Composite, Partial, and Covering indexes

02Advanced optimization techniques including Materialized Views and Table Partitioning

03Performance-driven pagination patterns using cursor-based methods instead of OFFSET

04Systematic elimination of N+1 query anti-patterns through JOINs and batch loading

050 GitHub stars

06Deep analysis of EXPLAIN and EXPLAIN ANALYZE execution plans to identify bottlenecks

Use Cases

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

02Debugging and fixing slow-running production database queries and high CPU usage

03Designing performant and scalable schemas for new high-traffic applications

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add duanbiao2000/obsidiandoc26 sql-optimization-patterns

For use in Claude.ai and ChatGPT

Download Skill