sql-optimization Skill

Goal

This skill focuses on ensuring database interactions are performant and scalable.

Steps

1. The Golden Rule: Indexing

• Primary Keys: Always indexed by default (B-Tree).
• Foreign Keys: MUST be indexed manually in most DBs (Postgres/MySQL) to avoid full table scans on joins.
• Where Clauses: Columns used in WHERE, ORDER BY, GROUP BY need indexes.
• Composite Indexes: Order matters! (last_name, first_name) supports lookup by last_name OR last_name + first_name, but NOT by first_name alone.

2. Analyzing Queries (EXPLAIN ANALYZE)

Always run EXPLAIN ANALYZE on suspect queries.