skills.rest
Abrahan-Eagle
Abrahan-EagleCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

sql-optimization-patterns

Analyze slow SQL queries with EXPLAIN plans and recommend indexes.

Updated May 23, 2025
Search Tags:#performance#indexing#database#sql#optimize#explain#query-plan
One-click install
npx skills add https://github.com/Abrahan-Eagle/zonix-eats-back --skill sql-optimization-patterns-abrahan-eagle
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/Abrahan-Eagle/zonix-eats-back/tree/main/.agents/skills/sql-optimization-patterns
Command: npx skills add https://github.com/Abrahan-Eagle/zonix-eats-back --skill sql-optimization-patterns-abrahan-eagle

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This skill helps identify and fix slow database queries by applying proven optimization patterns, proper indexing, and EXPLAIN analysis to reduce latency and load.

Core Features & Use Cases

  • Query plan analysis using EXPLAIN and EXPLAIN ANALYZE to identify bottlenecks.
  • Indexing strategies including B-Tree, composite, partial, expression, and covering indexes.
  • Common optimization patterns such as eliminating N+1 queries, efficient pagination, and subquery optimization.
  • Advanced techniques like materialized views and partitioning for large datasets.
  • Best practices, monitoring, and validation to ensure sustained performance gains.

Quick Start

Run a slow query through EXPLAIN, apply the recommended index, and verify the performance improvement.

Frequently Asked Questions about sql-optimization-patterns

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

FAQPage Schema
How do I read an EXPLAIN plan to fix slow SQL queries?▼

SQL query optimization patterns include eliminating N+1 queries, implementing efficient pagination, and refactoring subqueries. These patterns reduce latency and resource usage by streamlining database operations in PostgreSQL or MySQL production workloads.

What indexing strategy works best for complex PostgreSQL query tuning?▼

PostgreSQL indexing strategies include B-Tree, composite, partial, expression, and covering indexes. Choosing the right index type based on query predicates and joins drastically improves database performance and reduces query latency.

Can I use this approach to optimize MySQL production workloads?▼

Advanced SQL optimization for large datasets involves materialized views and partitioning. These techniques distribute query load and pre-compute complex joins, reducing latency when standard indexing patterns are insufficient for production workloads.

Why does my SQL query still run slow after adding a database index?▼

A query may run slow if the database index is not utilized by the query plan, often due to non-sargable predicates or outdated statistics. Analyzing the EXPLAIN plan confirms index usage and reveals hidden bottlenecks.

Related Skills

Explore More Software Engineering Skills→
sorodriguezzsorodriguezz

Examine Query Performance

Analyze SQL statements and runtime statistics to diagnose query performance in InterSystems IRIS.

Community
Intermediate
kensauruskensaurus
8

database-optimization

Profile slow queries and optimize indexes in PostgreSQL, Prisma, and Supabase.

Community
Advanced
camelranchentertainmentcamelranchentertainment
1

database-optimizer

Optimize PostgreSQL and MySQL queries with EXPLAIN ANALYZE and indexing strategies.

Community
Advanced
hung-phanhung-phan

materialized-views

Precompute complex query results as materialized views for relational databases.

Community
Intermediate