Database Optimization

Why Database Speed Matters

Database queries are often the slowest part of an API. When many users request data at the same time, a slow database can become a bottleneck.

Real-world impact:

• 1 slow query slowing response time by 2 seconds
• Users get frustrated and leave
• Server uses more resources trying to handle delayed requests
• Less scalable - can't handle 10x more users

How to Find Slow Queries

Laravel Telescope

What it does: Records every database query, shows which ones are slow.

How to use:

• Visit Telescope dashboard
• Look for queries taking > 100ms
• See exact SQL being executed
• Identify patterns (same query repeated, missing indexes)

Status: ✅ Already installed

Laravel Pulse

What it does: Real-time health monitoring, shows database load.

Benefit: See when database is under stress, plan accordingly.

Status: ✅ Already installed

Common Performance Problems

1. Missing Database Indexes

Problem: Database scans entire table to find data.

Analogy: Finding a name in a phone book without alphabetical order - must check every page.

Solution: Add indexes to columns used in WHERE clauses and joins.

Tables to prioritize:

• users: Index email, organisation_id
• webforms: Index user_id, created_at
• entries: Index webform_id, user_id
• triages: Index user_id, status

2. N+1 Query Problem

Problem: Query runs 1 time for main data, then 1 time for EACH related record.

Example:

• Get 100 webforms: 1 query
• For each webform, get user: 100 queries
• Total: 101 queries instead of 1!

Solution: Use Laravel's eager loading (with() method).

3. Selecting Unnecessary Columns

Problem: Requesting 50 columns when only 5 are needed.

Impact: More data transferred, slower queries, wasted memory.

Solution: Specify only needed columns in select().

4. Inefficient Sorting

Problem: Sorting without indexes is slow on large tables.

Solution: Add indexes to columns used in ORDER BY.

5. Full Table Scans

Problem: Query without WHERE clause scans entire table.

Example: Getting all entries for a user without filtering by user_id first.

Solution: Always filter before sorting or limiting.

Optimization Steps

Phase 1: Identify (Week 1)

• Use Telescope to find slowest 10 queries
• Log which are called most frequently
• Prioritize: impact × frequency

Phase 2: Index Creation (Week 2)

• Create indexes on high-impact columns
• Run migrations in testing first
• Measure improvement

Phase 3: Query Optimization (Week 3)

• Add eager loading (with()) to prevent N+1
• Add select() to specify columns
• Consolidate related queries

Phase 4: Verification (Week 4)

• Run performance tests
• Measure response time improvements
• Monitor production with Pulse

Index Strategy

Caching Strategy

In-Memory Caching (Octane Cache)

What: Store frequently accessed data in server memory.

Use for: Language lists, configuration, user preferences

Duration: Entire server process (very fast)

Redis Caching

What: Store data in Redis (already available).

Use for: User sessions, temporary data, API responses

Duration: Hours or days

Benefit: Survives server restart

Database Query Cache

What: Cache expensive queries' results.

Example: Cache user profile for 1 hour, invalidate on profile update

Benefit: Dramatic speed improvement for read-heavy data

Monitoring & Alerts

Using Laravel Pulse

Set alerts for:

• Query taking > 500ms
• Database CPU usage > 80%
• Connection pool exhausted
• Slow endpoint response times

Regular Reviews

• Weekly: Check Telescope for slowest queries
• Monthly: Review Pulse dashboard trends
• Quarterly: Full optimization audit

Expected Improvements

After optimization, expect:

• 50-80% faster queries with proper indexes
• Reduce N+1 queries by 90%
• Handle 5-10x more concurrent users
• Lower server CPU and memory usage
• Faster user experience