Optimizing SQL

Database-Specific Optimizations

PostgreSQL-Specific Features

Partial Indexes (index subset of rows):

CREATE INDEX idx_active_users_login
ON users (last_login)
WHERE status = 'active';

Expression Indexes (index computed values):

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

Covering Indexes (avoid heap access):

CREATE INDEX idx_users_email_covering
ON users (email) INCLUDE (id, name);

For comprehensive PostgreSQL optimization, see references/postgresql.md.

MySQL-Specific Features

Index Hints (override optimizer):

SELECT * FROM orders USE INDEX (idx_orders_customer)
WHERE customer_id = 123;

Storage Engine Selection:

• InnoDB (default): Transactional, row-level locks, clustered primary key
• MyISAM: Faster reads, no transactions, table-level locks

For comprehensive MySQL optimization, see references/mysql.md.

SQL Server-Specific Features

Query Store (track query performance over time):

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

Execution Plan Warnings:

• Look for yellow exclamation marks in graphical execution plans
• Thick arrows indicate high row counts

For comprehensive SQL Server optimization, see references/sqlserver.md.

Advanced Optimization Techniques

Common Table Expressions (CTEs)

Break complex queries into readable, maintainable parts:

WITH active_customers AS (
  SELECT id, name FROM customers WHERE status = 'active'
),
recent_orders AS (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT ac.name, COALESCE(ro.order_count, 0) as orders
FROM active_customers ac
LEFT JOIN recent_orders ro ON ac.id = ro.customer_id;

EXISTS vs IN for Subqueries

Use EXISTS for better performance with large datasets:

-- ✅ Good: EXISTS stops at first match
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- ❌ Less efficient: IN builds full list
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

Denormalization Decision Framework

Consider denormalization when:

• Query joins 3+ tables frequently
• Data is relatively static (infrequent updates)
• Read performance is critical
• Write overhead is acceptable

Denormalization Strategies:

1. Duplicate columns: Copy foreign key data into main table
2. Summary tables: Pre-aggregate data
3. Materialized views: Database-maintained denormalized views
4. Application caching: Redis/Memcached for frequently accessed data

Optimization Workflow Example

Scenario: API endpoint taking 2 seconds to load

Step 1: Identify Slow Query

Use APM/observability tools to identify database query causing delay

Step 2: Run EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

Step 3: Analyze Output

Seq Scan on orders (cost=0.00..2500.00 rows=10)
  Filter: (customer_id = 123)
  Rows Removed by Filter: 99990

Problem: Sequential scan filtering 99,990 rows

Step 4: Add Composite Index

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Step 5: Verify Improvement

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

Index Scan using idx_orders_customer_created (cost=0.42..12.44 rows=10)
  Index Cond: (customer_id = 123)

Result: 200x faster (2000ms → 10ms)

Monitoring and Maintenance

Regular Optimization Tasks:

• Review slow query logs weekly
• Update database statistics regularly (ANALYZE in PostgreSQL, UPDATE STATISTICS in SQL Server)
• Monitor index usage (drop unused indexes)
• Archive old data to keep tables manageable
• Review execution plans for critical queries quarterly

PostgreSQL Statistics Update:

ANALYZE table_name;

MySQL Statistics Update:

ANALYZE TABLE table_name;

SQL Server Statistics Update:

UPDATE STATISTICS table_name;

Related Skills

• databases-relational: Schema design and database fundamentals
• observability: Performance monitoring and slow query detection
• api-patterns: API-level optimization (pagination, caching)
• performance-engineering: Application performance profiling

Additional Resources

For comprehensive documentation, reference these files:

• references/explain-guide.md - Detailed EXPLAIN plan interpretation
• references/scan-types.md - Scan type meanings and performance implications
• references/indexing-decisions.md - When and how to add indexes
• references/index-types.md - Database-specific index types
• references/composite-indexes.md - Multi-column index design
• references/anti-patterns.md - Common anti-patterns with solutions
• references/efficient-patterns.md - Efficient query patterns
• references/postgresql.md - PostgreSQL-specific optimizations
• references/mysql.md - MySQL-specific optimizations
• references/sqlserver.md - SQL Server-specific optimizations

For working SQL examples, see examples/ directory.