Solving N+1 Query Problem: From 500ms to 50ms

N+1 query adalah salah satu silent killers yang paling sering aku temukan di codebase. Aplikasi terlihat fine di development dengan 10 rows data, tapi begitu production punya ribuan records, suddenly jadi super slow.

What is N+1 Query Problem?

Bayangkan kamu punya tabel

users

posts

Kamu mau display list of users dengan jumlah posts mereka. Code yang salah terlihat seperti ini:

// ❌ BAD: N+1 Query Problem
$users = User::all(); // 1 query

foreach ($users as $user) {
    echo $user->name . ' has ' . $user->posts->count() . ' posts'; // N queries
}

Total queries: 1 + N (dimana N = jumlah users)

Jika ada 100 users, berarti 101 queries! 😱

Real-World Impact

Di salah satu project yang aku handle, ada halaman dashboard yang load 500ms. Setelah profiling dengan Laravel Debugbar, ternyata:

Total Queries: 1,247 queries
Query Time: 487ms

Problem utamanya: N+1 queries di 3 different relationships.

How to Detect N+1 Queries

Method 1: Laravel Debugbar (PHP/Laravel)

Install debugbar:

composer require barryvdh/laravel-debugbar --dev

Debugbar akan highlight N+1 queries dengan warning ⚠️.

Method 2: Query Logging (General PHP)

// Enable query log
DB::enableQueryLog();

// Run your code
$users = User::all();
foreach ($users as $user) {
    $posts = $user->posts;
}

// Check queries
dd(DB::getQueryLog());

Method 3: Database Slow Query Log

Di MySQL/PostgreSQL, enable slow query log:

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1; -- 100ms threshold

Solutions

Solution 1: Eager Loading (Laravel/Eloquent)

// ✅ GOOD: Eager Loading
$users = User::with('posts')->get(); // 2 queries total!

foreach ($users as $user) {
    echo $user->name . ' has ' . $user->posts->count() . ' posts';
}

Queries:

-- Query 1: Get all users
SELECT * FROM users;

-- Query 2: Get all posts for those users
SELECT * FROM posts WHERE user_id IN (1, 2, 3, ...);

Total: 2 queries, tidak peduli ada berapa users!

Solution 2: Join Query (Raw SQL)

$users = DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->select('users.*', DB::raw('COUNT(posts.id) as posts_count'))
    ->groupBy('users.id')
    ->get();

Single query dengan JOIN + aggregation.

Solution 3: Eager Loading di Golang (GORM)

// ❌ BAD: N+1 in Go
var users []User
db.Find(&users)

for _, user := range users {
    var posts []Post
    db.Where("user_id = ?", user.ID).Find(&posts) // N queries
}

// ✅ GOOD: Preload in Go
var users []User
db.Preload("Posts").Find(&users) // 2 queries

Solution 4: DataLoader Pattern (untuk GraphQL/API)

Untuk API atau GraphQL, gunakan DataLoader pattern untuk batch requests:

const DataLoader = require('dataloader');

const postLoader = new DataLoader(async (userIds) => {
  const posts = await db.posts.findAll({
    where: { userId: { in: userIds } }
  });
  
  // Group by userId
  return userIds.map(id => 
    posts.filter(post => post.userId === id)
  );
});

// Usage
const postsForUser1 = await postLoader.load(1);
const postsForUser2 = await postLoader.load(2);
// Both requests batched into single query!

Benchmark Results

Project yang aku optimize (Laravel app dengan 500 users, 5000 posts):

Before (N+1):

Queries: 1,247
Time: 487ms
Memory: 12MB

After (Eager Loading):

Queries: 15
Time: 52ms
Memory: 8MB

Improvement:

• 90% fewer queries
• 89% faster response time
• 33% less memory usage

Advanced: Nested Eager Loading

Untuk relationships yang lebih dalam:

// Load posts dengan comments dan authors
$users = User::with([
    'posts.comments.author',
    'posts.tags'
])->get();

Atau dengan conditions:

$users = User::with([
    'posts' => function ($query) {
        $query->where('published', true)
              ->orderBy('created_at', 'desc')
              ->limit(5);
    }
])->get();

Best Practices

1. Always use eager loading untuk relationships yang pasti diakses
2. Profile before optimizing - jangan assume, measure!
3. Use query caching untuk data yang jarang berubah
4. Limit results - jangan load ribuan records sekaligus
5. Consider pagination untuk large datasets

Common Pitfalls

❌ Over-eager loading - Load relationships yang tidak dipakai
❌ Lazy loading di loops - Selalu suspect jika ada query di dalam loop
❌ Ignoring indices - N+1 solved tapi query tetap slow karena missing index

Tools untuk Auto-Detection

• Laravel N+1 Query Detector package
• Database Profilers: New Relic, Datadog APM
• Custom middleware untuk log query counts

// Simple query counter middleware
public function handle($request, $next)
{
    DB::enableQueryLog();
    $response = $next($request);
    
    $queries = DB::getQueryLog();
    if (count($queries) > 50) {
        Log::warning('High query count: ' . count($queries));
    }
    
    return $response;
}

Kesimpulan

N+1 query problem adalah low-hanging fruit untuk database optimization. Dengan eager loading dan proper relationship management:

• Faster response times (bisa sampai 10x faster)
• Reduced database load
• Better user experience
• Lower infrastructure costs

Pro tip: Setup automatic N+1 detection di development environment untuk catch issues sebelum production!

Punya experience dengan N+1 queries? Share di komentar! 💬