Database Query Optimization

Database Query Optimization

Purpose

Slow databases kill applications. This skill replaces guesswork with systematic performance analysis, using EXPLAIN plans and profiling to eliminate N+1 queries, eliminate unnecessary scans, and add targeted indexes so the database bears the computational load, not the application.

When to use

• Writing complex SQL queries or ORM access functions
• Resolving performance bottlenecks on read-heavy endpoints
• Designing schema migrations for growing datasets
• Refactoring loops that make repeated database calls

When NOT to use

• Schema design (different concern)
• Database selection (architectural decision)
• Caching strategies (use Caching Strategies skill)
• Application-level performance (profilers, algorithms)

Inputs required

• Slow query logs or endpoint metrics
• ORM code accessing the database
• Database schema (tables, columns, indexes)
• EXPLAIN ANALYZE capability (test environment)

Workflow

1. Profile the Bottleneck: Run EXPLAIN ANALYZE on slow queries to identify sequential scans and high-cost operations
2. Identify N+1: Locate loops making repetitive database calls for the same entity type
3. Measure Selectivity: Analyze WHERE clause filters and add indexes to highly selective columns
4. Replace Loops: Replace N+1 with single IN queries or ORM eager-loading (JOINs)
5. Remove Over-Fetching: Replace SELECT * with explicit column names
6. Add Indexes: Add B-Tree indexes to WHERE, JOIN, and ORDER BY columns in slow queries
7. Paginate: Enforce LIMIT and OFFSET (or cursor pagination) on all collection queries
8. Verify Performance: Re-run EXPLAIN ANALYZE and benchmark end-to-end latency

Rules

• MUST explicitly define selected columns (NEVER use SELECT * in production)
• MUST NEVER have database operations inside loops
• MUST perform filtering and aggregation in the database, not application memory
• MUST enforce LIMIT and OFFSET on collection queries
• MUST EXPLAIN before adding indexes (verify they reduce cost)
• MUST rollback indexes if they degrade INSERT/UPDATE performance
• MUST NOT over-index (each index has maintenance cost)

Anti-patterns

• The N+1 Problem: Fetching 50 users, then making 50 individual queries to fetch each user's profile
• Over-Indexing: Adding an index to every single column (degrades INSERT/UPDATE performance)
• Application-Side Filtering: Fetching 10,000 rows from DB and using filter() in JavaScript
• **SELECT in Production*: Fetching all columns including BLOBs when needing only id and name
• Unbounded Queries: Collection endpoints without LIMIT/OFFSET returning millions of rows
• Ignoring Selectivity: Adding indexes to low-cardinality columns (gender, status)

Failure conditions

• Database unavailable for profiling
• No query metrics/logs available
• EXPLAIN ANALYZE not supported by database
• Migration lacks rollback strategy
• Index changes cause lock timeouts on large tables

Validation checklist

• [ ] EXPLAIN ANALYZE shows acceptable query cost ( u.status === 'active');

**✅ Correct pattern (Join, eager-load, explicit columns, paginated):**
```javascript
// Single optimized query with JOIN and explicit columns
const usersWithPosts = await User.findAll({
  attributes: ['id', 'username', 'email'],
  include: [{
    model: Post,
    attributes: ['id', 'title', 'createdAt'],
    required: true
  }],
  limit: 20,
  offset: 0,
  order: [['createdAt', 'DESC']]
});

// Or raw SQL with pagination
const query = `
  SELECT u.id, u.username, p.id, p.title
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
  WHERE u.status = $1
  ORDER BY u.created_at DESC
  LIMIT $2 OFFSET $3
`;
const result = await db.query(query, ['active', 20, 0]);

// Indexes created
// CREATE INDEX idx_posts_user_id ON posts(user_id);
// CREATE INDEX idx_users_status ON users(status) WHERE status = 'active';

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: KraitDev
• Source: KraitDev/skiLL.Md
• License: MIT

Install and usage instructions live in the source repository linked above.