How does it measure performance improvements?

It utilizes tools like EXPLAIN ANALYZE (PostgreSQL) and EXPLAIN FORMAT=JSON (MySQL) to compare execution times, row counts, and buffer hits before and after optimization.

Can this skill suggest new database indexes?

Yes, it analyzes query access patterns and execution plans to provide specific CREATE INDEX statements that will improve query performance.

Can it refactor subqueries into window functions?

Absolutely. It identifies inefficient self-joins or correlated subqueries and replaces them with modern window functions like ROW_NUMBER() for better performance.

Which databases are supported by this skill?

This skill specifically provides tailored optimization strategies and execution plan analysis for PostgreSQL and MySQL, but it can also be used for general SQL and MongoDB optimization.

Does the optimizer ensure result parity?

Yes, the skill includes instructions to verify that optimized queries return identical result sets to the original version, specifically handling NULLs and JOIN types carefully.

SQL Query Optimizer

Name: SQL Query Optimizer
Author: jeremylongshore

byjeremylongshore

•

1,887

•

데이터베이스 관리

Optimizes database performance by analyzing execution plans and rewriting inefficient SQL queries for PostgreSQL and MySQL.

The SQL Query Optimizer skill enables developers to drastically improve database performance by identifying and eliminating common SQL anti-patterns, restructuring complex JOINs, and leveraging advanced database features like window functions and CTEs. It automates the analysis of execution plans, provides side-by-side performance comparisons, and generates precise index recommendations. Whether you are troubleshooting a slow production query or refactoring legacy code, this skill provides the automation and guidance needed to ensure your database operations are running at peak efficiency.

주요 기능

01Side-by-side performance metrics and buffer usage comparison

02Generation of performance-backed index recommendations

03Automated SQL anti-pattern detection and resolution

04Deep execution plan analysis for PostgreSQL and MySQL

05Structural query refactoring for JOINs and subqueries

061,887 GitHub stars

사용 사례

01Modernizing legacy SQL queries to utilize window functions and CTEs

02Reducing latency in slow-running production reporting queries

03Optimizing database throughput during high-traffic scaling events

주요 기능

01Side-by-side performance metrics and buffer usage comparison

02Generation of performance-backed index recommendations

03Automated SQL anti-pattern detection and resolution

04Deep execution plan analysis for PostgreSQL and MySQL

05Structural query refactoring for JOINs and subqueries

061,887 GitHub stars

사용 사례

01Modernizing legacy SQL queries to utilize window functions and CTEs

02Reducing latency in slow-running production reporting queries

03Optimizing database throughput during high-traffic scaling events