sql-optimization

Name: sql-optimization
Author: github

13kinstalls4.6kforks

How to install sql-optimization?

github/awesome-copilot/sql-optimization

$npx -y skills add github/awesome-copilot --skill sql-optimization

Installs into the current project.

Summary

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

Files · 1

View on GitHub

SKILL.md

1	# SQL Performance Optimization Assistant
2
3	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.
4
5	## 🎯 Core Optimization Areas
6
7	### Query Performance Analysis
8	```sql
9	-- ❌ BAD: Inefficient query patterns
10	SELECT * FROM orders o
11	WHERE YEAR(o.created_at) = 2024
12	AND o.customer_id IN (
13	SELECT c.id FROM customers c WHERE c.status = 'active'
14	);
15
16	-- ✅ GOOD: Optimized query with proper indexing hints
17	SELECT o.id, o.customer_id, o.total_amount, o.created_at
18	FROM orders o
19	INNER JOIN customers c ON o.customer_id = c.id
20	WHERE o.created_at >= '2024-01-01'
21	AND o.created_at < '2025-01-01'
22	AND c.status = 'active';
23
24	-- Required indexes:
25	-- CREATE INDEX idx_orders_created_at ON orders(created_at);
26	-- CREATE INDEX idx_customers_status ON customers(status);
27	-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);
28	```
29
30	### Index Strategy Optimization
31	```sql
32	-- ❌ BAD: Poor indexing strategy
33	CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);
34
35	-- ✅ GOOD: Optimized composite indexing
36	-- For queries filtering by email first, then sorting by created_at
37	CREATE INDEX idx_users_email_created ON users(email, created_at);
38
39	-- For full-text name searches
40	CREATE INDEX idx_users_name ON users(last_name, first_name);
41
42	-- For user status queries
43	CREATE INDEX idx_users_status_created ON users(status, created_at)
44	WHERE status IS NOT NULL;
45	```
46
47	### Subquery Optimization
48	```sql
49	-- ❌ BAD: Correlated subquery
50	SELECT p.product_name, p.price
51	FROM products p
52	WHERE p.price > (
53	SELECT AVG(price)
54	FROM products p2
55	WHERE p2.category_id = p.category_id
56	);
57
58	-- ✅ GOOD: Window function approach
59	SELECT product_name, price
60	FROM (
61	SELECT product_name, price,
62	AVG(price) OVER (PARTITION BY category_id) as avg_category_price
63	FROM products
64	) ranked
65	WHERE price > avg_category_price;
66	```
67
68	## 📊 Performance Tuning Techniques
69
70	### JOIN Optimization
71	```sql
72	-- ❌ BAD: Inefficient JOIN order and conditions
73	SELECT o.*, c.name, p.product_name
74	FROM orders o
75	LEFT JOIN customers c ON o.customer_id = c.id
76	LEFT JOIN order_items oi ON o.id = oi.order_id
77	LEFT JOIN products p ON oi.product_id = p.id
78	WHERE o.created_at > '2024-01-01'
79	AND c.status = 'active';
80
81	-- ✅ GOOD: Optimized JOIN with filtering
82	SELECT o.id, o.total_amount, c.name, p.product_name
83	FROM orders o
84	INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
85	INNER JOIN order_items oi ON o.id = oi.order_id
86	INNER JOIN products p ON oi.product_id = p.id
87	WHERE o.created_at > '2024-01-01';
88	```
89
90	### Pagination Optimization
91	```sql
92	-- ❌ BAD: OFFSET-based pagination (slow for large offsets)
93	SELECT * FROM products
94	ORDER BY created_at DESC
95	LIMIT 20 OFFSET 10000;
96
97	-- ✅ GOOD: Cursor-based pagination
98	SELECT * FROM products
99	WHERE created_at < '2024-06-15 10:30:00'
100	ORDER BY created_at DESC
101	LIMIT 20;
102
103	-- Or using ID-based cursor
104	SELECT * FROM products
105	WHERE id > 1000
106	ORDER BY id
107	LIMIT 20;
108	```
109
110	### Aggregation Optimization
111	```sql
112	-- ❌ BAD: Multiple separate aggregation queries
113	SELECT COUNT(*) FROM orders WHERE status = 'pending';
114	SELECT COUNT(*) FROM orders WHERE status = 'shipped';
115	SELECT COUNT(*) FROM orders WHERE status = 'delivered';
116
117	-- ✅ GOOD: Single query with conditional aggregation
118	SELECT
119	COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
120	COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
121	COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
122	FROM orders;
123	```
124
125	## 🔍 Query Anti-Patterns
126
127	### SELECT Performance Issues
128	```sql
129	-- ❌ BAD: SELECT * anti-pattern
130	SELECT * FROM large_table lt
131	JOIN another_table at ON lt.id = at.ref_id;
132
133	-- ✅ GOOD: Explicit column selection
134	SELECT lt.id, lt.name, at.value
135	FROM large_table lt
136	JOIN another_table at ON lt.id = at.ref_id;
137	```
138
139	### WHERE Clause Optimization
140	```sql
141	-- ❌ BAD: Function calls in WHERE clause
142	SELECT * FROM orders
143	WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';
144
145	-- ✅ GOOD: Index-friendly WHERE clause
146	SELECT * FROM orders
147	WHERE customer_email = 'john@example.com';
148	-- Consider: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));
149	```
150
151	### OR vs UNION Optimization
152	```sql
153	-- ❌ BAD: Complex OR conditions
154	SELECT * FROM products
155	WHERE (category = 'electronics' AND price < 1000)
156	OR (category = 'books' AND price < 50);
157
158	-- ✅ GOOD: UNION approach for better optimization
159	SELECT * FROM products WHERE category = 'electronics' AND price < 1000
160	UNION ALL
161	SELECT * FROM products WHERE category = 'books' AND price < 5

People who liked this also viewed

6 picks

Type

sql-optimization

13kinstalls4.6kforks

View on GitHub

How to install sql-optimization?

github/awesome-copilot/sql-optimization

$npx -y skills add github/awesome-copilot --skill sql-optimization

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# SQL Performance Optimization Assistant
2
3	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.
4
5	## 🎯 Core Optimization Areas
6
7	### Query Performance Analysis
8	```sql
9	-- ❌ BAD: Inefficient query patterns
10	SELECT * FROM orders o
11	WHERE YEAR(o.created_at) = 2024
12	AND o.customer_id IN (
13	SELECT c.id FROM customers c WHERE c.status = 'active'
14	);
15
16	-- ✅ GOOD: Optimized query with proper indexing hints
17	SELECT o.id, o.customer_id, o.total_amount, o.created_at
18	FROM orders o
19	INNER JOIN customers c ON o.customer_id = c.id
20	WHERE o.created_at >= '2024-01-01'
21	AND o.created_at < '2025-01-01'
22	AND c.status = 'active';
23
24	-- Required indexes:
25	-- CREATE INDEX idx_orders_created_at ON orders(created_at);
26	-- CREATE INDEX idx_customers_status ON customers(status);
27	-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);
28	```
29
30	### Index Strategy Optimization
31	```sql
32	-- ❌ BAD: Poor indexing strategy
33	CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);
34
35	-- ✅ GOOD: Optimized composite indexing
36	-- For queries filtering by email first, then sorting by created_at
37	CREATE INDEX idx_users_email_created ON users(email, created_at);
38
39	-- For full-text name searches
40	CREATE INDEX idx_users_name ON users(last_name, first_name);
41
42	-- For user status queries
43	CREATE INDEX idx_users_status_created ON users(status, created_at)
44	WHERE status IS NOT NULL;
45	```
46
47	### Subquery Optimization
48	```sql
49	-- ❌ BAD: Correlated subquery
50	SELECT p.product_name, p.price
51	FROM products p
52	WHERE p.price > (
53	SELECT AVG(price)
54	FROM products p2
55	WHERE p2.category_id = p.category_id
56	);
57
58	-- ✅ GOOD: Window function approach
59	SELECT product_name, price
60	FROM (
61	SELECT product_name, price,
62	AVG(price) OVER (PARTITION BY category_id) as avg_category_price
63	FROM products
64	) ranked
65	WHERE price > avg_category_price;
66	```
67
68	## 📊 Performance Tuning Techniques
69
70	### JOIN Optimization
71	```sql
72	-- ❌ BAD: Inefficient JOIN order and conditions
73	SELECT o.*, c.name, p.product_name
74	FROM orders o
75	LEFT JOIN customers c ON o.customer_id = c.id
76	LEFT JOIN order_items oi ON o.id = oi.order_id
77	LEFT JOIN products p ON oi.product_id = p.id
78	WHERE o.created_at > '2024-01-01'
79	AND c.status = 'active';
80
81	-- ✅ GOOD: Optimized JOIN with filtering
82	SELECT o.id, o.total_amount, c.name, p.product_name
83	FROM orders o
84	INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
85	INNER JOIN order_items oi ON o.id = oi.order_id
86	INNER JOIN products p ON oi.product_id = p.id
87	WHERE o.created_at > '2024-01-01';
88	```
89
90	### Pagination Optimization
91	```sql
92	-- ❌ BAD: OFFSET-based pagination (slow for large offsets)
93	SELECT * FROM products
94	ORDER BY created_at DESC
95	LIMIT 20 OFFSET 10000;
96
97	-- ✅ GOOD: Cursor-based pagination
98	SELECT * FROM products
99	WHERE created_at < '2024-06-15 10:30:00'
100	ORDER BY created_at DESC
101	LIMIT 20;
102
103	-- Or using ID-based cursor
104	SELECT * FROM products
105	WHERE id > 1000
106	ORDER BY id
107	LIMIT 20;
108	```
109
110	### Aggregation Optimization
111	```sql
112	-- ❌ BAD: Multiple separate aggregation queries
113	SELECT COUNT(*) FROM orders WHERE status = 'pending';
114	SELECT COUNT(*) FROM orders WHERE status = 'shipped';
115	SELECT COUNT(*) FROM orders WHERE status = 'delivered';
116
117	-- ✅ GOOD: Single query with conditional aggregation
118	SELECT
119	COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
120	COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
121	COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
122	FROM orders;
123	```
124
125	## 🔍 Query Anti-Patterns
126
127	### SELECT Performance Issues
128	```sql
129	-- ❌ BAD: SELECT * anti-pattern
130	SELECT * FROM large_table lt
131	JOIN another_table at ON lt.id = at.ref_id;
132
133	-- ✅ GOOD: Explicit column selection
134	SELECT lt.id, lt.name, at.value
135	FROM large_table lt
136	JOIN another_table at ON lt.id = at.ref_id;
137	```
138
139	### WHERE Clause Optimization
140	```sql
141	-- ❌ BAD: Function calls in WHERE clause
142	SELECT * FROM orders
143	WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';
144
145	-- ✅ GOOD: Index-friendly WHERE clause
146	SELECT * FROM orders
147	WHERE customer_email = 'john@example.com';
148	-- Consider: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));
149	```
150
151	### OR vs UNION Optimization
152	```sql
153	-- ❌ BAD: Complex OR conditions
154	SELECT * FROM products
155	WHERE (category = 'electronics' AND price < 1000)
156	OR (category = 'books' AND price < 50);
157
158	-- ✅ GOOD: UNION approach for better optimization
159	SELECT * FROM products WHERE category = 'electronics' AND price < 1000
160	UNION ALL
161	SELECT * FROM products WHERE category = 'books' AND price < 5

People who liked this also viewed

6 picks

Type