How does this skill help with slow database queries?

It provides systematic patterns for analyzing execution plans with the EXPLAIN command and suggests specific indexing or query refactoring techniques to reduce execution time and resource consumption.

Can this skill help solve the N+1 query problem?

Absolutely. It includes specific patterns for identifying N+1 issues in application code and replacing them with efficient JOINs or batch loading strategies to minimize database round-trips.

Does it support multiple database engines?

Yes, while it provides specific examples for PostgreSQL and MySQL, the core principles of indexing, join optimization, and query planning are applicable to most relational database systems.

What types of indexing strategies are covered?

It covers a wide range of strategies including B-Tree, GIN for JSONB/Full-text search, composite indexes, partial indexes for specific row subsets, and covering indexes that include extra columns.

Does it provide guidance on large-scale data management?

Yes, it includes advanced techniques such as table partitioning, materialized views for expensive aggregates, and batch processing for large-scale INSERT and UPDATE operations.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: Activer007

byActiver007

データベース管理

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

概要

This skill empowers developers to transform slow database queries into high-performance operations by providing systematic guidance on SQL optimization. It covers deep analysis of execution plans using EXPLAIN, implementation of advanced indexing strategies like B-Tree and GIN, and the resolution of common performance bottlenecks like N+1 queries and inefficient offset-based pagination. Whether you are debugging a lagging production query or designing a scalable schema from scratch, this skill offers the patterns and best practices needed to minimize database load, reduce costs, and dramatically improve application response times.

主な機能

Comprehensive indexing strategies including composite, partial, and GIN indexes
High-performance pagination techniques using cursor-based navigation
0 GitHub stars
Advanced EXPLAIN and execution plan analysis for PostgreSQL and MySQL
Standardized patterns for resolving N+1 query performance anti-patterns
Optimization patterns for complex aggregates, JOINs, and batch operations

ユースケース

Refactoring backend application code to eliminate database performance bottlenecks
Debugging and fixing slow-running production database queries
Designing high-performance schemas for applications with rapidly growing datasets

概要

主な機能

Comprehensive indexing strategies including composite, partial, and GIN indexes
High-performance pagination techniques using cursor-based navigation
0 GitHub stars
Advanced EXPLAIN and execution plan analysis for PostgreSQL and MySQL
Standardized patterns for resolving N+1 query performance anti-patterns
Optimization patterns for complex aggregates, JOINs, and batch operations

ユースケース

Refactoring backend application code to eliminate database performance bottlenecks
Debugging and fixing slow-running production database queries
Designing high-performance schemas for applications with rapidly growing datasets