optimizing-sql

Name: optimizing-sql
Author: ancoleman

52installs59forks

How to install optimizing-sql?

ancoleman/ai-design-components/optimizing-sql

$npx -y skills add ancoleman/ai-design-components --skill optimizing-sql

Installs into the current project.

Summary

Optimize SQL query performance through EXPLAIN analysis, indexing strategies, and query rewriting for PostgreSQL, MySQL, and SQL Server. Use when debugging slow queries, analyzing execution plans, or improving database performance.

Files · 1

View on GitHub

SKILL.md

1	# SQL Optimization
2
3	Provide tactical guidance for optimizing SQL query performance across PostgreSQL, MySQL, and SQL Server through execution plan analysis, strategic indexing, and query rewriting.
4
5	## When to Use This Skill
6
7	Trigger this skill when encountering:
8	- Slow query performance or database timeouts
9	- Analyzing EXPLAIN plans or execution plans
10	- Determining index requirements
11	- Rewriting inefficient queries
12	- Identifying query anti-patterns (N+1, SELECT *, correlated subqueries)
13	- Database-specific optimization needs (PostgreSQL, MySQL, SQL Server)
14
15	## Core Optimization Workflow
16
17	### Step 1: Analyze Query Performance
18
19	Run execution plan analysis to identify bottlenecks:
20
21	PostgreSQL:
22	```sql
23	EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
24	```
25
26	MySQL:
27	```sql
28	EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;
29	```
30
31	SQL Server:
32	Use SQL Server Management Studio: Display Estimated Execution Plan (Ctrl+L)
33
34	Key Metrics to Monitor:
35	- Cost: Estimated resource consumption
36	- Rows: Number of rows processed (estimated vs actual)
37	- Scan Type: Sequential scan vs index scan
38	- Execution Time: Actual time spent on operation
39
40	For detailed execution plan interpretation, see `references/explain-guide.md`.
41
42	### Step 2: Identify Optimization Opportunities
43
44	Common Red Flags:
45
46	\| Indicator \| Problem \| Solution \|
47	\|-----------\|---------\|----------\|
48	\| Seq Scan / Table Scan \| Full table scan on large table \| Add index on filter columns \|
49	\| High row count \| Processing excessive rows \| Add WHERE filter or index \|
50	\| Nested Loop with large outer table \| Inefficient join algorithm \| Index join columns \|
51	\| Correlated subquery \| Subquery executes per row \| Rewrite as JOIN or EXISTS \|
52	\| Sort operation on large result set \| Expensive sorting \| Add index matching ORDER BY \|
53
54	For scan type interpretation, see `references/scan-types.md`.
55
56	### Step 3: Apply Indexing Strategies
57
58	Index Decision Framework:
59
60	```
61	Is column used in WHERE, JOIN, ORDER BY, or GROUP BY?
62	├─ YES → Is column selective (many unique values)?
63	│ ├─ YES → Is table frequently queried?
64	│ │ ├─ YES → ADD INDEX
65	│ │ └─ NO → Consider based on query frequency
66	│ └─ NO (low selectivity) → Skip index
67	└─ NO → Skip index
68	```
69
70	Index Types by Use Case:
71
72	PostgreSQL:
73	- B-tree (default): General-purpose, supports <, ≤, =, ≥, >, BETWEEN, IN
74	- Hash: Equality comparisons only (=)
75	- GIN: Full-text search, JSONB, arrays
76	- GiST: Spatial data, geometric types
77	- BRIN: Very large tables with naturally ordered data
78
79	MySQL:
80	- B-tree (default): General-purpose index
81	- Full-text: Text search on VARCHAR/TEXT columns
82	- Spatial: Spatial data types
83
84	SQL Server:
85	- Clustered: Table data sorted by index (one per table)
86	- Non-clustered: Separate index structure (multiple allowed)
87
88	For comprehensive indexing guidance, see `references/indexing-decisions.md` and `references/index-types.md`.
89
90	### Step 4: Design Composite Indexes
91
92	For queries filtering on multiple columns, use composite indexes:
93
94	Column Order Matters:
95	1. Equality filters first (most selective)
96	2. Additional equality filters (by selectivity)
97	3. Range filters or ORDER BY (last)
98
99	Example:
100	```sql
101	-- Query pattern
102	SELECT * FROM orders
103	WHERE customer_id = 123 AND status = 'shipped'
104	ORDER BY created_at DESC
105	LIMIT 10;
106
107	-- Optimal composite index
108	CREATE INDEX idx_orders_customer_status_created
109	ON orders (customer_id, status, created_at DESC);
110	```
111
112	For composite index design patterns, see `references/composite-indexes.md`.
113
114	### Step 5: Rewrite Inefficient Queries
115
116	Common Anti-Patterns to Avoid:
117
118	*1. SELECT (Over-fetching)**
119	```sql
120	-- ❌ Bad: Fetches all columns
121	SELECT * FROM users WHERE id = 1;
122
123	-- ✅ Good: Fetch only needed columns
124	SELECT id, name, email FROM users WHERE id = 1;
125	```
126
127	2. N+1 Queries
128	```sql
129	-- ❌ Bad: 1 + N queries
130	SELECT * FROM users LIMIT 100;
131	-- Then in loop: SELECT * FROM posts WHERE user_id = ?;
132
133	-- ✅ Good: Single JOIN
134	SELECT users.*, posts.id AS post_id, posts.title
135	FROM users
136	LEFT JOIN posts ON users.id = posts.user_id;
137	```
138
139	3. Non-Sargable Queries (functions on indexed columns)
140	```sql
141	-- ❌ Bad: Function prevents index usage
142	SELECT * FROM orders WHERE YEAR(created_at) = 2025;
143
144	-- ✅ Good: Sargable range condition
145	SELECT * FROM orders
146	WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
147	```
148
149	4. Correlated Subqueries
150	```sql
151	-- ❌ Bad: Subquery executes per row
152	SELECT name,
153	(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)
154	FROM users;
155
156	-- ✅ Good: JOIN with GROUP BY
157	SELECT users.name, COUNT(orders.id) AS order_count
158	FROM users
159	LEFT JOIN orders ON users.id = orders.user_id
160	GROUP BY

People who liked this also viewed

6 picks

Type

optimizing-sql

52installs59forks

View on GitHub

How to install optimizing-sql?

ancoleman/ai-design-components/optimizing-sql

$npx -y skills add ancoleman/ai-design-components --skill optimizing-sql

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# SQL Optimization
2
3	Provide tactical guidance for optimizing SQL query performance across PostgreSQL, MySQL, and SQL Server through execution plan analysis, strategic indexing, and query rewriting.
4
5	## When to Use This Skill
6
7	Trigger this skill when encountering:
8	- Slow query performance or database timeouts
9	- Analyzing EXPLAIN plans or execution plans
10	- Determining index requirements
11	- Rewriting inefficient queries
12	- Identifying query anti-patterns (N+1, SELECT *, correlated subqueries)
13	- Database-specific optimization needs (PostgreSQL, MySQL, SQL Server)
14
15	## Core Optimization Workflow
16
17	### Step 1: Analyze Query Performance
18
19	Run execution plan analysis to identify bottlenecks:
20
21	PostgreSQL:
22	```sql
23	EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
24	```
25
26	MySQL:
27	```sql
28	EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;
29	```
30
31	SQL Server:
32	Use SQL Server Management Studio: Display Estimated Execution Plan (Ctrl+L)
33
34	Key Metrics to Monitor:
35	- Cost: Estimated resource consumption
36	- Rows: Number of rows processed (estimated vs actual)
37	- Scan Type: Sequential scan vs index scan
38	- Execution Time: Actual time spent on operation
39
40	For detailed execution plan interpretation, see `references/explain-guide.md`.
41
42	### Step 2: Identify Optimization Opportunities
43
44	Common Red Flags:
45
46	\| Indicator \| Problem \| Solution \|
47	\|-----------\|---------\|----------\|
48	\| Seq Scan / Table Scan \| Full table scan on large table \| Add index on filter columns \|
49	\| High row count \| Processing excessive rows \| Add WHERE filter or index \|
50	\| Nested Loop with large outer table \| Inefficient join algorithm \| Index join columns \|
51	\| Correlated subquery \| Subquery executes per row \| Rewrite as JOIN or EXISTS \|
52	\| Sort operation on large result set \| Expensive sorting \| Add index matching ORDER BY \|
53
54	For scan type interpretation, see `references/scan-types.md`.
55
56	### Step 3: Apply Indexing Strategies
57
58	Index Decision Framework:
59
60	```
61	Is column used in WHERE, JOIN, ORDER BY, or GROUP BY?
62	├─ YES → Is column selective (many unique values)?
63	│ ├─ YES → Is table frequently queried?
64	│ │ ├─ YES → ADD INDEX
65	│ │ └─ NO → Consider based on query frequency
66	│ └─ NO (low selectivity) → Skip index
67	└─ NO → Skip index
68	```
69
70	Index Types by Use Case:
71
72	PostgreSQL:
73	- B-tree (default): General-purpose, supports <, ≤, =, ≥, >, BETWEEN, IN
74	- Hash: Equality comparisons only (=)
75	- GIN: Full-text search, JSONB, arrays
76	- GiST: Spatial data, geometric types
77	- BRIN: Very large tables with naturally ordered data
78
79	MySQL:
80	- B-tree (default): General-purpose index
81	- Full-text: Text search on VARCHAR/TEXT columns
82	- Spatial: Spatial data types
83
84	SQL Server:
85	- Clustered: Table data sorted by index (one per table)
86	- Non-clustered: Separate index structure (multiple allowed)
87
88	For comprehensive indexing guidance, see `references/indexing-decisions.md` and `references/index-types.md`.
89
90	### Step 4: Design Composite Indexes
91
92	For queries filtering on multiple columns, use composite indexes:
93
94	Column Order Matters:
95	1. Equality filters first (most selective)
96	2. Additional equality filters (by selectivity)
97	3. Range filters or ORDER BY (last)
98
99	Example:
100	```sql
101	-- Query pattern
102	SELECT * FROM orders
103	WHERE customer_id = 123 AND status = 'shipped'
104	ORDER BY created_at DESC
105	LIMIT 10;
106
107	-- Optimal composite index
108	CREATE INDEX idx_orders_customer_status_created
109	ON orders (customer_id, status, created_at DESC);
110	```
111
112	For composite index design patterns, see `references/composite-indexes.md`.
113
114	### Step 5: Rewrite Inefficient Queries
115
116	Common Anti-Patterns to Avoid:
117
118	*1. SELECT (Over-fetching)**
119	```sql
120	-- ❌ Bad: Fetches all columns
121	SELECT * FROM users WHERE id = 1;
122
123	-- ✅ Good: Fetch only needed columns
124	SELECT id, name, email FROM users WHERE id = 1;
125	```
126
127	2. N+1 Queries
128	```sql
129	-- ❌ Bad: 1 + N queries
130	SELECT * FROM users LIMIT 100;
131	-- Then in loop: SELECT * FROM posts WHERE user_id = ?;
132
133	-- ✅ Good: Single JOIN
134	SELECT users.*, posts.id AS post_id, posts.title
135	FROM users
136	LEFT JOIN posts ON users.id = posts.user_id;
137	```
138
139	3. Non-Sargable Queries (functions on indexed columns)
140	```sql
141	-- ❌ Bad: Function prevents index usage
142	SELECT * FROM orders WHERE YEAR(created_at) = 2025;
143
144	-- ✅ Good: Sargable range condition
145	SELECT * FROM orders
146	WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
147	```
148
149	4. Correlated Subqueries
150	```sql
151	-- ❌ Bad: Subquery executes per row
152	SELECT name,
153	(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)
154	FROM users;
155
156	-- ✅ Good: JOIN with GROUP BY
157	SELECT users.name, COUNT(orders.id) AS order_count
158	FROM users
159	LEFT JOIN orders ON users.id = orders.user_id
160	GROUP BY

People who liked this also viewed

6 picks

Type