sql-optimization

✓ Official★ 36,200

by github · part of github/awesome-copilot

Universal SQL performance optimization across MySQL, PostgreSQL, SQL Server, Oracle, and other databases. Covers query analysis, index strategy design, subquery optimization, and JOIN tuning with before/after examples for each technique Addresses common anti-patterns including SELECT *, function calls in WHERE clauses, inefficient pagination, and correlated subqueries Provides database-agnostic guidance on batch operations, temporary tables, covering indexes, and partial indexes Includes...

🔥🔥🔥🔥✓ VerifiedFreeQuick setup

#email #database #git #monitoring

🧩 One of 7 skills in the github/awesome-copilot package — works on its own, and pairs well with its siblings.

Inspect the full instructions your agent will receiveExpand

This is the exact playbook injected into your agent when the skill activates — shown here so you can audit it before installing. You don't need to read it to use the skill.

by github

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

Copy & paste — that's it

-- ❌ 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 (
 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

Copy & paste — that's it

-- ❌ 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

Copy & paste — that's it

-- ❌ 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 1000
ORDER BY id 
LIMIT 20;

Aggregation Optimization

Copy & paste — that's it

-- ❌ 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

Copy & paste — that's it

-- ❌ 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

Copy & paste — that's it

-- ❌ BAD: Function calls in WHERE clause
SELECT * FROM orders 
WHERE UPPER(customer_email) = '[email protected]';

-- ✅ GOOD: Index-friendly WHERE clause
SELECT * FROM orders 
WHERE customer_email = '[email protected]';
-- Consider: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));

OR vs UNION Optimization

Copy & paste — that's it

-- ❌ BAD: Complex OR conditions
SELECT * FROM products 
WHERE (category = 'electronics' AND price = '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

Copy & paste — that's it

-- ✅ 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

Copy & paste — that's it

-- ✅ 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

Copy & paste — that's it

-- 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

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

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