Sql optimization patterns

Skill tranhieutt/software_development_department/.claude/skills/sql-optimization-patterns

Software Development Department

Install

npx -y skills add tranhieutt/software_development_department --skill sql-optimization-patterns

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

What its author says it does

Copied from the file, not written here

Provides SQL optimization patterns for query performance, indexing strategies, schema design, and database tuning. Use when optimizing slow queries, designing indexes, or tuning database performance.

SKILL.md

2.7 KB, as published. Nobody here has run it

SQL Optimization Patterns

Query optimization, indexing, and performance tuning for PostgreSQL, MySQL, and SQLite.

Index Strategy

When to Create Index

```sql -- High selectivity columns (many unique values) CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index: order matters (equality first, then range) CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Covering index (includes all needed columns) CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at); ```

When NOT to Index

Low cardinality columns (boolean, status with few values)
Small tables (< 1000 rows)
Write-heavy tables with rare reads

Query Patterns

Avoid SELECT *

```sql -- Bad SELECT * FROM orders WHERE user_id = 1;

-- Good (select only needed columns) SELECT id, total, status FROM orders WHERE user_id = 1; ```

Avoid N+1 (use JOIN or subquery)

```sql -- Bad: N+1 queries from application -- Good: Single query with JOIN SELECT o.id, o.total, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'pending'; ```

Pagination (keyset, not OFFSET)

```sql -- Bad: OFFSET scans all skipped rows SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- Good: Keyset pagination SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20; ```

EXPLAIN ANALYZE

```sql EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 AND status = 'pending'; ```

Read output:

Seq Scan = missing index
Index Scan = good
Nested Loop with high row count = check join strategy

Schema Anti-Patterns

Anti-Pattern	Problem	Fix
EAV (Entity-Attribute-Value)	No type safety, slow queries	Use JSONB or proper columns
God table	Too many columns	Normalize into related tables
No constraints	Data integrity issues	Add CHECK, FK, UNIQUE constraints
String dates	Sorting/filtering issues	Use TIMESTAMP type

Connection Pooling

``` App → Pool (min: 5, max: 20) → PostgreSQL ```

Tools: PgBouncer (PostgreSQL), ProxySQL (MySQL).

Related Skills

database-architect — schema design
postgres-patterns — PostgreSQL specifics
nosql-expert — NoSQL alternatives
db-review — database code review

Gives 0 of the 12 instructions most performance cost skills give

Counted across 803 of the 1,058 authors here whose files we hold, read 2026-08-06

keep skill files under 500 linesin 82 of 803, across 17 files
use imperative form in instructionsin 81 of 803, across 10 files
draft assertions while test runs are in progressin 75 of 803, across 9 files
create two to three realistic test promptsin 74 of 803, across 8 files
write skill descriptions to be pushyin 72 of 803, across 7 files
save test cases to evals jsonin 72 of 803, across 6 files
ask questions about edge cases and input formatsin 71 of 803, across 6 files
save timing data immediately when runs completein 70 of 803, across 5 files
include all trigger conditions in the skill descriptionin 69 of 803, across 3 files
launch all test runs in a single turnin 69 of 803, across 3 files
capture intent before writing a skillin 67 of 803, across 1 file
import directly instead of barrel filesin 52 of 803, across 15 files

Said here and by no other author read

order composite indexes by equality then range
replace EAV with JSONB or proper columns
normalize God tables into related tables
add check, foreign key, and unique constraints
use timestamp types for dates
use connection pooling tools

Grouped from the skills themselves: near-identical wordings counted once, and counted by distinct author, so one author publishing three of these counts once.