skills.rest
algusaem
algusaemCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

sql-optimization-patterns

Identify and apply SQL optimization patterns for PostgreSQL and MySQL queries.

Updated Jul 15, 2023
Search Tags:#indexing#optimization#sql#mysql#postgres#explain#query-plan
One-click install
npx skills add https://github.com/algusaem/algusaem-portfolio --skill sql-optimization-patterns-algusaem
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/algusaem/algusaem-portfolio/tree/main/.claude/skills/sql-optimization-patterns
Command: npx skills add https://github.com/algusaem/algusaem-portfolio --skill sql-optimization-patterns-algusaem

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes scripts (resource) and references (resource) and assets (resource) components.

What problem does it solve?

SQL performance bottlenecks hamper application responsiveness and increase load on databases. This Skill provides proven patterns and techniques to turn inefficient queries into fast, scalable operations by applying indexing strategies, proper query design, and explain plan insights.

Core Features & Use Cases

  • Indexing strategies: Create appropriate indexes (B-Tree, partial, covering) to support common query patterns and reduce sequential scans.
  • Query optimization patterns: Rewrite queries, leverage joins and aggregations efficiently, and use EXPLAIN ANALYZE to verify improvements.
  • Use Case: When a service experiences slow response times due to large tables, apply these patterns to diagnose and remediate performance issues with minimal schema changes.

Quick Start

Run EXPLAIN ANALYZE on a slow query and apply a recommended indexing strategy on the relevant table.

Frequently Asked Questions about sql-optimization-patterns

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

FAQPage Schema
How do I use EXPLAIN ANALYZE to fix slow SQL queries?▼

Use EXPLAIN ANALYZE to diagnose slow SQL queries by revealing the execution plan and runtime, then apply indexing strategies and query rewrites to reduce sequential scans and improve performance.

What is a covering index and when should I use one in MySQL?▼

A covering index includes all columns referenced in a query, allowing MySQL to retrieve results directly from the index structure without accessing the underlying table data, significantly reducing query latency.

How do I optimize PostgreSQL query plans for large production tables?▼

Optimize PostgreSQL query plans for large tables by applying appropriate indexing strategies, rewriting inefficient aggregations and joins, and verifying improvements through EXPLAIN ANALYZE to minimize sequential scans.

Can I rewrite SQL joins and aggregations to speed up query execution without changing the schema?▼

Yes, you can rewrite SQL joins and aggregations to execute more efficiently without major schema changes, leveraging proper query design patterns and indexing strategies to reduce the load on large tables.

What's the best way to reduce sequential scans on large database tables?▼

The best way to reduce sequential scans is to create appropriate B-Tree, partial, or covering indexes that directly support your common query patterns, verified through EXPLAIN ANALYZE execution plan analysis.

Why does my SQL query ignore the index I just created?▼

Your query may ignore an index if the query plan generated by EXPLAIN ANALYZE determines a sequential scan is cheaper, often due to low selectivity, unsupported query patterns, or needing a covering index instead.

Related Skills

Explore More Software Engineering Skills→
Axel-DaMageAxel-DaMage
2

database-optimizer

Analyze and optimize PostgreSQL and MySQL database performance.

Community
Advanced
Dianshu-LiaoDianshu-Liao
2

sql-optimization-patterns

Analyze EXPLAIN output and rewrite SQL with indexing patterns.

Community
Advanced
botPhUbotPhU

database-expert

Analyze SQL and NoSQL workloads and produce prioritized database optimization plans.

Community
Advanced
francanetefrancanete
1

postgres-query-optimization

Analyzes slow PostgreSQL queries using EXPLAIN ANALYZE to recommend index and query optimizations.

Community
Advanced