SkillRepo
Sign InSign Up
Catalog
github/sql-optimization

github

sql-optimization

Universal SQL performance optimization assistant for comprehensive query tuning, indexing strategies, and database performance analysis across all SQL databases (MySQL, PostgreSQL, SQL Server, Oracle). Provides execution plan analysis, pagination optimization, batch operations, and performance monitoring guidance.

v1.0Latest
New~2.3kUpdated Jun 26, 2026

SQL Performance Optimization Assistant

Expert SQL performance optimization for ${selection} (or entire project if no selection). Focus on universal SQL optimization techniques that work across MySQL, PostgreSQL, SQL Server, Oracle, and other SQL databases.

🎯 Core Optimization Areas

Query Performance Analysis

-- ❌ BAD: Inefficient query patterns
SELECT * FROM orders o
WHERE YEAR(o.created_at) = 2024
  AND o.customer_id IN (
      SELECT c.id FROM customers c WHERE c.status = 'active'
  );

-- ✅ GOOD: Optimized query with proper indexing hints
SELECT o.id, o.customer_id, o.total_amount, o.created_at
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01' 
  AND o.created_at < '2025-01-01'
  AND c.status = 'active';

-- Required indexes:
-- CREATE INDEX idx_orders_created_at ON orders(created_at);
-- CREATE INDEX idx_customers_status ON customers(status);
-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Index Strategy Optimization

-- ❌ BAD: Poor indexing strategy
CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);

-- ✅ GOOD: Optimized composite indexing
-- For queries filtering by email first, then sorting by created_at
CREATE INDEX idx_users_email_created ON users(email, created_at);

-- For full-text name searches
CREATE INDEX idx_users_name ON users(last_name, first_name);

-- For user status queries
CREATE INDEX idx_users_status_created ON users(status, created_at)
WHERE status IS NOT NULL;

Subquery Optimization

-- ❌ BAD: Correlated subquery
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(price) 
    FROM products p2 
    WHERE p2.category_id = p.category_id
);

-- ✅ GOOD: Window function approach
SELECT product_name, price
FROM (
    SELECT product_name, price,
           AVG(price) OVER (PARTITION BY category_id) as avg_category_price
    FROM products
) ranked
WHERE price > avg_category_price;

📊 Performance Tuning Techniques

JOIN Optimization

-- ❌ BAD: Inefficient JOIN order and conditions
SELECT o.*, c.name, p.product_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
  AND c.status = 'active';

-- ✅ GOOD: Optimized JOIN with filtering
SELECT o.id, o.total_amount, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

Pagination Optimization

-- ❌ BAD: OFFSET-based pagination (slow for large offsets)
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ GOOD: Cursor-based pagination
SELECT * FROM products 
WHERE created_at < '2024-06-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- Or using ID-based cursor
SELECT * FROM products 
WHERE id > 1000
ORDER BY id 
LIMIT 20;

Aggregation Optimization

-- ❌ BAD: Multiple separate aggregation queries
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
SELECT COUNT(*) FROM orders WHERE status = 'delivered';

-- ✅ GOOD: Single query with conditional aggregation
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
FROM orders;

🔍 Query Anti-Patterns

SELECT Performance Issues

-- ❌ BAD: SELECT * anti-pattern
SELECT * FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

-- ✅ GOOD: Explicit column selection
SELECT lt.id, lt.name, at.value
FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

WHERE Clause Optimization

-- ❌ BAD: Function calls in WHERE clause
SELECT * FROM orders 
WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';

-- ✅ GOOD: Index-friendly WHERE clause
SELECT * FROM orders 
WHERE customer_email = 'john@example.com';
-- Consider: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));

OR vs UNION Optimization

-- ❌ BAD: Complex OR conditions
SELECT * FROM products 
WHERE (category = 'electronics' AND price < 1000)
   OR (category = 'books' AND price < 50);

-- ✅ GOOD: UNION approach for better optimization
SELECT * FROM products WHERE category = 'electronics' AND price < 1000
UNION ALL
SELECT * FROM products WHERE category = 'books' AND price < 50;

📈 Database-Agnostic Optimization

Batch Operations

-- ❌ BAD: Row-by-row operations
INSERT INTO products (name, price) VALUES ('Product 1', 10.00);
INSERT INTO products (name, price) VALUES ('Product 2', 15.00);
INSERT INTO products (name, price) VALUES ('Product 3', 20.00);

-- ✅ GOOD: Batch insert
INSERT INTO products (name, price) VALUES 
('Product 1', 10.00),
('Product 2', 15.00),
('Product 3', 20.00);

Temporary Table Usage

-- ✅ GOOD: Using temporary tables for complex operations
CREATE TEMPORARY TABLE temp_calculations AS
SELECT customer_id, 
       SUM(total_amount) as total_spent,
       COUNT(*) as order_count
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;

-- Use the temp table for further calculations
SELECT c.name, tc.total_spent, tc.order_count
FROM temp_calculations tc
JOIN customers c ON tc.customer_id = c.id
WHERE tc.total_spent > 1000;

🛠️ Index Management

Index Design Principles

-- ✅ GOOD: Covering index design
CREATE INDEX idx_orders_covering 
ON orders(customer_id, created_at) 
INCLUDE (total_amount, status);  -- SQL Server syntax
-- Or: CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- Other databases

Partial Index Strategy

-- ✅ GOOD: Partial indexes for specific conditions
CREATE INDEX idx_orders_active 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');

📊 Performance Monitoring Queries

Query Performance Analysis

-- Generic approach to identify slow queries
-- (Specific syntax varies by database)

-- For MySQL:
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC;

-- For PostgreSQL:
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC;

-- For SQL Server:
SELECT 
    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

🎯 Universal Optimization Checklist

Query Structure

  • Avoiding SELECT * in production queries
  • Using appropriate JOIN types (INNER vs LEFT/RIGHT)
  • Filtering early in WHERE clauses
  • Using EXISTS instead of IN for subqueries when appropriate
  • Avoiding functions in WHERE clauses that prevent index usage

Index Strategy

  • Creating indexes on frequently queried columns
  • Using composite indexes in the right column order
  • Avoiding over-indexing (impacts INSERT/UPDATE performance)
  • Using covering indexes where beneficial
  • Creating partial indexes for specific query patterns

Data Types and Schema

  • Using appropriate data types for storage efficiency
  • Normalizing appropriately (3NF for OLTP, denormalized for OLAP)
  • Using constraints to help query optimizer
  • Partitioning large tables when appropriate

Query Patterns

  • Using LIMIT/TOP for result set control
  • Implementing efficient pagination strategies
  • Using batch operations for bulk data changes
  • Avoiding N+1 query problems
  • Using prepared statements for repeated queries

Performance Testing

  • Testing queries with realistic data volumes
  • Analyzing query execution plans
  • Monitoring query performance over time
  • Setting up alerts for slow queries
  • Regular index usage analysis

📝 Optimization Methodology

  1. Identify: Use database-specific tools to find slow queries
  2. Analyze: Examine execution plans and identify bottlenecks
  3. Optimize: Apply appropriate optimization techniques
  4. Test: Verify performance improvements
  5. Monitor: Continuously track performance metrics
  6. Iterate: Regular performance review and optimization

Focus on measurable performance improvements and always test optimizations with realistic data volumes and query patterns.

Files1
1 files · 1.0 KB

Select a file to preview

Overall Score

78/100

Grade

B

Good

Safety

82

Quality

76

Clarity

82

Completeness

70

Summary

A comprehensive SQL performance optimization guide providing universal tuning techniques across MySQL, PostgreSQL, SQL Server, and Oracle. The skill teaches query optimization patterns, index design strategies, execution plan analysis, and performance monitoring through structured examples of anti-patterns and best practices.

Detected Capabilities

query analysisindex design guidanceperformance pattern recommendationexecution plan interpretationdatabase-agnostic optimizationschema design consultation

Trigger Keywords

Phrases that MCP clients use to match this skill to user intent.

slow query optimizationindex design strategysql execution planpagination tuningdatabase performancequery optimizationindex creationsql anti-patterns

Use Cases

  • Analyze slow query execution plans and identify bottlenecks
  • Design composite indexes and covering indexes for improved performance
  • Optimize queries using window functions and efficient JOIN strategies
  • Implement cursor-based pagination to replace slow OFFSET queries
  • Refactor correlated subqueries using window functions or CTEs
  • Batch large INSERT/UPDATE operations to reduce transaction overhead
  • Configure partial indexes for conditional query patterns
  • Monitor database performance using query profiling tools
  • Normalize schema design and apply appropriate denormalization strategies

Quality Notes

  • Excellent structure with clear sections (Core Areas, Tuning Techniques, Anti-Patterns, Checklist)
  • Rich examples showing bad patterns and good alternatives for each concept
  • Database-agnostic approach suitable for multiple SQL platforms (MySQL, PostgreSQL, SQL Server, Oracle)
  • Comprehensive checklist provides actionable verification steps
  • Practical optimization methodology (Identify → Analyze → Optimize → Test → Monitor → Iterate)
  • Database-specific examples for performance monitoring queries show awareness of platform differences
  • Clear use of visual indicators (❌ BAD vs ✅ GOOD) for readability
  • Covers wide range of optimization domains: queries, indexes, pagination, aggregation, joins, batch operations
  • No explicit limitations or edge case guidance documented
  • No error handling or recovery guidance for index operations or schema changes
  • Does not address testing strategies or rollback procedures for optimization changes
Model: claude-haiku-4-5-20251001Analyzed: Jun 26, 2026

Reviews

Add this skill to your library to leave a review.

No reviews yet

Be the first to share your experience.

Use github/sql-optimization in your dev environment

Start a Developer trial

Command Palette

Search for a command to run...