Corrigindo o Problema de Consultas N+1: Como Melhoramos o Tempo de Resposta da API de 3s para 200ms

Introdução

In March 2024, our team faced a critical performance issue in our Laravel-based e-learning platform API. What started as occasional user complaints about slow page loads quickly escalated into a full-blown performance crisis affecting over 50,000 active users.

Our API endpoint for fetching course listings was taking an average of 3.2 seconds to respond. In some cases, response times exceeded 8 seconds, causing mobile app timeouts and significant user frustration.

After a thorough investigation, we discovered the culprit: N+1 query problems throughout our codebase. This case study documents our journey from identifying the issue to implementing solutions that reduced our API response time to 200ms – a 94% improvement.

O Que Você Vai Aprender

By the end of this article, you'll understand:

• How to identify N+1 query problems in your Laravel application
• Practical solutions using eager loading and query optimization
• Tools and techniques for monitoring database performance
• Real-world code examples with before/after comparisons
• Best practices to prevent N+1 queries in future development
• Performance benchmarking strategies

O Problema: Nossa Crise de Performance da API

Os Sintomas

Our application is an e-learning platform that serves courses to students via a mobile app and web interface. Users were experiencing:

• Slow course listing pages (3-8 second load times)
• Mobile app timeouts on 3G/4G connections
• Increased server CPU usage (averaging 75-85%)
• Database connection pool exhaustion during peak hours
• Higher AWS RDS costs due to increased IOPS consumption

O Impacto no Negócio

The performance issues had real business consequences:

• User complaints increased by 340% in two weeks
• App store rating dropped from 4.5 to 3.2 stars
• Mobile app abandonment rate increased by 28%
• Infrastructure costs increased by $1,200/month
• Customer support tickets doubled

Métricas Iniciais

Here were our baseline metrics before optimization:

Endpoint: GET /api/v1/courses
Average Response Time: 3,247ms
Database Queries: 847 queries per request
Peak Response Time: 8,120ms
Success Rate: 94.3% (timeouts causing failures)
Concurrent Users: ~500 during peak hours

Contexto: O Contexto da Aplicação

Nosso Stack Tecnológico

• Framework: Laravel 10.x
• Database: MySQL 8.0 on AWS RDS (db.t3.large)
• Cache: Redis 7.0
• Server: AWS EC2 (t3.medium instances, 3 servers)
• Load Balancer: AWS Application Load Balancer
• Monitoring: Laravel Telescope, New Relic

Pisão Geral do Schema do Banco de Dados

Our application has the following key models and relationships:

// Course Model
class Course extends Model
{
    public function instructor() {
        return $this->belongsTo(User::class, 'instructor_id');
    }

    public function categories() {
        return $this->belongsToMany(Category::class);
    }

    public function lessons() {
        return $this->hasMany(Lesson::class);
    }

    public function enrollments() {
        return $this->hasMany(Enrollment::class);
    }

    public function reviews() {
        return $this->hasMany(Review::class);
    }
}

// Lesson Model
class Lesson extends Model
{
    public function course() {
        return $this->belongsTo(Course::class);
    }

    public function videos() {
        return $this->hasMany(Video::class);
    }

    public function attachments() {
        return $this->hasMany(Attachment::class);
    }
}

// Review Model
class Review extends Model
{
    public function user() {
        return $this->belongsTo(User::class);
    }

    public function course() {
        return $this->belongsTo(Course::class);
    }
}

A typical course listing page needed to display:

• Course title, description, and thumbnail
• Instructor name and profile picture
• Number of lessons
• Average rating from reviews
• Number of enrolled students
• Category tags
• First 3 recent reviews with user details

Descoberta: Como Encontramos o Problema N+1

Passo 1: Laravel Debugbar Installeren

Our first step was installing Laravel Debugbar to get visibility into database queries:

composer require barryvdh/laravel-debugbar --dev

Configuration (config/debugbar.php):

return [
    'enabled' => env('DEBUGBAR_ENABLED', false),
    'except' => [
        'telescope*',
        'horizon*',
    ],
];

Environment Setup (.env.local):

DEBUGBAR_ENABLED=true
DB_LOG_QUERIES=true

Passo 2: De Schokkende Ontdekking

When we examined a single request to /api/v1/courses, the Debugbar showed:

Total Queries: 847
Total Query Time: 2,847ms
Duplicated Queries: 789

The smoking gun: 789 duplicate queries! This was a textbook N+1 problem.

Passo 3: De Controller Code Analyseren

Here's what our original controller looked like:

// BAD CODE - Before Optimization
class CourseController extends Controller
{
    public function index()
    {
        $courses = Course::where('status', 'published')
            ->orderBy('created_at', 'desc')
            ->paginate(20);

        return CourseResource::collection($courses);
    }
}

The API Resource (CourseResource.php):

// BAD CODE - Before Optimization
class CourseResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'title' => $this->title,
            'description' => $this->description,
            'thumbnail' => $this->thumbnail_url,

            // N+1 Problem: This triggers a query for each course
            'instructor' => [
                'id' => $this->instructor->id,
                'name' => $this->instructor->name,
                'avatar' => $this->instructor->avatar_url,
            ],

            // N+1 Problem: Count query for each course
            'lessons_count' => $this->lessons->count(),

            // N+1 Problem: Multiple queries per course
            'average_rating' => $this->reviews->avg('rating'),
            'total_reviews' => $this->reviews->count(),
            'enrolled_students' => $this->enrollments->count(),

            // N+1 Problem: Collection query for each course
            'categories' => $this->categories->pluck('name'),

            // N+1 Problem: Nested relationship
            'recent_reviews' => $this->reviews()
                ->with('user')
                ->latest()
                ->take(3)
                ->get()
                ->map(function ($review) {
                    return [
                        'rating' => $review->rating,
                        'comment' => $review->comment,
                        'user_name' => $review->user->name,
                        'user_avatar' => $review->user->avatar_url,
                    ];
                }),
        ];
    }
}

Passo 4: Query Analyse

Using Laravel Telescope, we analyzed the actual queries being executed:

-- Initial query (1 query)
SELECT * FROM `courses`
WHERE `status` = 'published'
ORDER BY `created_at` DESC
LIMIT 20;

-- For EACH of the 20 courses, the following queries ran:

-- Instructor query (20 queries)
SELECT * FROM `users` WHERE `id` = ? LIMIT 1;

-- Lessons count (20 queries)
SELECT * FROM `lessons` WHERE `course_id` = ?;

-- Reviews for average rating (20 queries)
SELECT * FROM `reviews` WHERE `course_id` = ?;

-- Enrollments count (20 queries)
SELECT * FROM `enrollments` WHERE `course_id` = ?;

-- Categories (20 queries)
SELECT `categories`.* FROM `categories`
INNER JOIN `category_course` ON `categories`.`id` = `category_course`.`category_id`
WHERE `category_course`.`course_id` = ?;

-- Recent reviews (20 queries)
SELECT * FROM `reviews` WHERE `course_id` = ? ORDER BY `created_at` DESC LIMIT 3;

-- For each review, get the user (60 additional queries for 3 reviews per course)
SELECT * FROM `users` WHERE `id` = ? LIMIT 1;

Total Queries Breakdown:

• 1 initial course query
• 20 instructor queries
• 20 lessons queries
• 20 reviews queries (for average)
• 20 enrollment queries
• 20 categories queries
• 20 recent reviews queries
• 60 user queries (for review authors)
• Total: 181 queries for just 20 courses

With pagination showing 20 courses, we were running 181 queries when we should have been running approximately 7-10 queries!

Entendendo as Consultas N+1

O Que é um Problema de Consulta N+1?

The N+1 query problem occurs when:

1. You fetch N records from the database (1 query)
2. For each record, you execute an additional query to fetch related data (N queries)
3. Total queries = 1 + N = N+1

Visual Example:

Initial Query (1):     [Course 1] [Course 2] [Course 3] ... [Course 20]
                            ↓          ↓          ↓              ↓
Instructor Queries(20): [User 1]   [User 2]   [User 3]   ... [User 20]
                            ↓          ↓          ↓              ↓
Reviews Queries (20):  [Reviews]  [Reviews]  [Reviews]  ... [Reviews]
                            ↓          ↓          ↓              ↓
And so on...

Total: 1 + 20 + 20 + 20 + ... = 181 queries

Por Que Isso é um Problema?

1. Database Connection Overhead: Each query requires network round-trip
2. Query Parsing Time: MySQL must parse and execute each query
3. Connection Pool Exhaustion: Too many concurrent queries
4. Increased Latency: Network latency multiplied by number of queries
5. Resource Consumption: Higher CPU, memory, and I/O usage

A Matemática Por Trás do Nosso Problema

Average single query time: 15ms
Number of queries: 181

Sequential execution time: 15ms × 181 = 2,715ms (2.7 seconds)
Add overhead (parsing, network): ~500ms
Total response time: ~3,200ms (3.2 seconds)

O Processo de Investigação

Fase 1: Alle N+1 Problemen Identificeren

We used Laravel Telescope's Queries tab to track down every instance:

Telescope Configuration:

// config/telescope.php
'watchers' => [
    Watchers\QueryWatcher::class => [
        'enabled' => env('TELESCOPE_QUERY_WATCHER', true),
        'slow' => 50, // Log queries slower than 50ms
    ],
],

We created a spreadsheet tracking:

Fase 2: Benchmarks Creëren

We created a benchmark test to measure improvements:

// tests/Performance/CourseApiPerformanceTest.php
<?php

namespace Tests\Performance;

use Tests\TestCase;
use App\Models\Course;
use Illuminate\Support\Facades\DB;

class CourseApiPerformanceTest extends TestCase
{
    public function test_course_index_query_count()
    {
        // Seed test data
        Course::factory()
            ->count(20)
            ->hasInstructor()
            ->hasLessons(10)
            ->hasReviews(15)
            ->hasCategories(3)
            ->create();

        DB::enableQueryLog();

        $response = $this->getJson('/api/v1/courses');

        $queries = DB::getQueryLog();
        $queryCount = count($queries);

        // Assert query count is optimized
        $this->assertLessThan(15, $queryCount,
            "Query count is {$queryCount}, expected less than 15"
        );

        // Assert response time
        $this->assertLessThan(300, $this->getResponseTime($response),
            "Response time exceeded 300ms"
        );

        DB::disableQueryLog();
    }

    protected function getResponseTime($response)
    {
        return $response->headers->get('X-Response-Time') ?? 0;
    }
}

Fase 3: Prestatiedoelen Stellen

Based on industry standards and user experience research:

Solução 1: Eager Loading Implementeren

Eager Loading Begrijpen

Eager loading solves the N+1 problem by loading all related data in a single (or few) queries using SQL JOINs.

Laravel provides three main methods:

1. with() - Eager load relationships
2. load() - Lazy eager load (load relationships after initial query)
3. loadMissing() - Load only if not already loaded

Implementatie: Controller Wijzigingen

// GOOD CODE - After Optimization
class CourseController extends Controller
{
    public function index()
    {
        $courses = Course::query()
            ->where('status', 'published')
            // Eager load all required relationships
            ->with([
                'instructor:id,name,avatar_url',
                'categories:id,name',
                'reviews:id,course_id,rating',
                'reviews.user:id,name,avatar_url',
            ])
            // Use withCount for counting relationships
            ->withCount([
                'lessons',
                'enrollments',
                'reviews',
            ])
            // Use withAvg for average calculations
            ->withAvg('reviews', 'rating')
            ->orderBy('created_at', 'desc')
            ->paginate(20);

        return CourseResource::collection($courses);
    }
}

Belangrijkste Verbeteringen Uitgelegd

1. Selectief Kolommen Laden

'instructor:id,name,avatar_url'

Instead of loading all columns from the users table, we only select the columns we need. This reduces:

• Memory usage
• Network transfer size
• Database I/O

Importante: Always include the foreign key (id in this case) when using selective columns.

2. Gebruik van withCount()

->withCount(['lessons', 'enrollments', 'reviews'])

This adds a {relation}_count attribute to your model using efficient SQL COUNT queries:

-- Instead of loading all records and counting in PHP
SELECT COUNT(*) FROM lessons WHERE course_id IN (1,2,3,...,20);

-- Generates a single optimized query
SELECT courses.*,
       (SELECT COUNT(*) FROM lessons WHERE course_id = courses.id) as lessons_count,
       (SELECT COUNT(*) FROM enrollments WHERE course_id = courses.id) as enrollments_count
FROM courses;

3. Gebruik van withAvg()

->withAvg('reviews', 'rating')

Calculates the average in the database rather than loading all reviews:

SELECT courses.*,
       (SELECT AVG(rating) FROM reviews WHERE course_id = courses.id) as reviews_avg_rating
FROM courses;

Bijgewerkte API Resource

// GOOD CODE - After Optimization
class CourseResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'title' => $this->title,
            'description' => $this->description,
            'thumbnail' => $this->thumbnail_url,

            // No query - already eager loaded
            'instructor' => [
                'id' => $this->instructor->id,
                'name' => $this->instructor->name,
                'avatar' => $this->instructor->avatar_url,
            ],

            // No query - uses withCount()
            'lessons_count' => $this->lessons_count,
            'enrolled_students' => $this->enrollments_count,
            'total_reviews' => $this->reviews_count,

            // No query - uses withAvg()
            'average_rating' => round($this->reviews_avg_rating ?? 0, 2),

            // No query - already eager loaded
            'categories' => $this->categories->pluck('name'),

            // No query - already eager loaded with nested user
            'recent_reviews' => $this->reviews
                ->sortByDesc('created_at')
                ->take(3)
                ->map(function ($review) {
                    return [
                        'rating' => $review->rating,
                        'comment' => $review->comment,
                        'user_name' => $review->user->name,
                        'user_avatar' => $review->user->avatar_url,
                    ];
                })
                ->values(),
        ];
    }
}

Resultados Após a Solução 1

Before:
Total Queries: 181
Response Time: 3,247ms
Database Time: 2,847ms

After:
Total Queries: 8
Response Time: 687ms
Database Time: 445ms

Improvement: 95.6% fewer queries, 78.8% faster response time

Solução 2: Geneste Relaties Optimaliseren

Het Probleem met Recente Reviews

Even with eager loading, our "recent reviews" feature had issues:

// This still causes problems
'recent_reviews' => $this->reviews
    ->sortByDesc('created_at')
    ->take(3)

Why? Because:

1. We're loading ALL reviews for each course
2. Then sorting them in PHP (memory intensive)
3. Then taking only the first 3

For a course with 500 reviews, we're loading 497 unnecessary records!

Oplossing: Constrained Eager Loading

Laravel 8+ introduced constrained eager loading with closures:

// BETTER CODE
class CourseController extends Controller
{
    public function index()
    {
        $courses = Course::query()
            ->where('status', 'published')
            ->with([
                'instructor:id,name,avatar_url',
                'categories:id,name',

                // Constrained eager loading - only load what we need
                'reviews' => function ($query) {
                    $query->select('id', 'course_id', 'user_id', 'rating', 'comment', 'created_at')
                          ->latest()
                          ->limit(3);
                },
                'reviews.user:id,name,avatar_url',
            ])
            ->withCount(['lessons', 'enrollments', 'reviews'])
            ->withAvg('reviews as reviews_avg_rating', 'rating')
            ->orderBy('created_at', 'desc')
            ->paginate(20);

        return CourseResource::collection($courses);
    }
}

Geavanceerd: Subquery Selects Gebruiken

For even better performance on aggregate data:

use Illuminate\Database\Eloquent\Builder;

class CourseController extends Controller
{
    public function index()
    {
        $courses = Course::query()
            ->where('status', 'published')
            ->select([
                'courses.*',

                // Add subquery for average rating
                'average_rating' => Review::selectRaw('ROUND(AVG(rating), 2)')
                    ->whereColumn('course_id', 'courses.id'),

                // Add subquery for latest review date
                'latest_review_date' => Review::select('created_at')
                    ->whereColumn('course_id', 'courses.id')
                    ->latest()
                    ->limit(1),
            ])
            ->with([
                'instructor:id,name,avatar_url',
                'categories:id,name',
                'reviews' => fn($q) => $q->latest()->limit(3)->with('user:id,name,avatar_url'),
            ])
            ->withCount(['lessons', 'enrollments'])
            ->orderBy('created_at', 'desc')
            ->paginate(20);

        return CourseResource::collection($courses);
    }
}

Generated SQL (simplified):

SELECT
    courses.*,
    (SELECT ROUND(AVG(rating), 2) FROM reviews WHERE course_id = courses.id) as average_rating,
    (SELECT created_at FROM reviews WHERE course_id = courses.id ORDER BY created_at DESC LIMIT 1) as latest_review_date,
    (SELECT COUNT(*) FROM lessons WHERE course_id = courses.id) as lessons_count,
    (SELECT COUNT(*) FROM enrollments WHERE course_id = courses.id) as enrollments_count
FROM courses
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

-- Then only 3 additional queries for related data:
-- 1. Load all instructors for these 20 courses
-- 2. Load all categories for these 20 courses
-- 3. Load 3 recent reviews for each course (with users)

Resultados Após a Solução 2

Before Solution 2:
Total Queries: 8
Response Time: 687ms
Database Time: 445ms
Memory Usage: 24MB

After Solution 2:
Total Queries: 6
Response Time: 312ms
Database Time: 178ms
Memory Usage: 8MB

Improvement: 54.6% faster, 66.7% less memory

Solução 3: Strategische Query Refactoring

Database Indexering

We discovered our queries were slow due to missing indexes:

-- Check for missing indexes
EXPLAIN SELECT * FROM courses WHERE status = 'published' ORDER BY created_at DESC LIMIT 20;

Resultado: Full table scan on 50,000 courses!

Indexen Toevoegen via Migratie

// database/migrations/2024_03_15_add_performance_indexes.php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::table('courses', function (Blueprint $table) {
            // Composite index for common query pattern
            $table->index(['status', 'created_at']);

            // Index foreign keys
            $table->index('instructor_id');
        });

        Schema::table('reviews', function (Blueprint $table) {
            // Composite index for average rating queries
            $table->index(['course_id', 'rating']);

            // Index for latest reviews
            $table->index(['course_id', 'created_at']);
        });

        Schema::table('lessons', function (Blueprint $table) {
            $table->index('course_id');
        });

        Schema::table('enrollments', function (Blueprint $table) {
            // Composite index for counting active enrollments
            $table->index(['course_id', 'status']);
        });
    }

    public function down()
    {
        Schema::table('courses', function (Blueprint $table) {
            $table->dropIndex(['status', 'created_at']);
            $table->dropIndex(['instructor_id']);
        });

        Schema::table('reviews', function (Blueprint $table) {
            $table->dropIndex(['course_id', 'rating']);
            $table->dropIndex(['course_id', 'created_at']);
        });

        Schema::table('lessons', function (Blueprint $table) {
            $table->dropIndex(['course_id']);
        });

        Schema::table('enrollments', function (Blueprint $table) {
            $table->dropIndex(['course_id', 'status']);
        });
    }
};

Query Prestatie-impact

Before Indexes:
Query Time: 178ms
Rows Examined: 50,000

After Indexes:
Query Time: 12ms
Rows Examined: 20

Improvement: 93.3% faster queries

Query Scopes Maken voor Herbruikbaarheid

To avoid repeating complex eager loading logic, we created query scopes:

// app/Models/Course.php
class Course extends Model
{
    /**
     * Scope for API listing with all relationships
     */
    public function scopeWithApiRelations(Builder $query)
    {
        return $query->with([
            'instructor:id,name,avatar_url',
            'categories:id,name',
            'reviews' => fn($q) => $q->latest()->limit(3)->with('user:id,name,avatar_url'),
        ])
        ->withCount(['lessons', 'enrollments', 'reviews'])
        ->withAvg('reviews as reviews_avg_rating', 'rating');
    }

    /**
     * Scope for published courses only
     */
    public function scopePublished(Builder $query)
    {
        return $query->where('status', 'published');
    }

    /**
     * Scope for ordering by popularity
     */
    public function scopePopular(Builder $query)
    {
        return $query->withCount('enrollments')
            ->orderBy('enrollments_count', 'desc');
    }
}

Updated Controller:

class CourseController extends Controller
{
    public function index()
    {
        $courses = Course::published()
            ->withApiRelations()
            ->orderBy('created_at', 'desc')
            ->paginate(20);

        return CourseResource::collection($courses);
    }

    public function popular()
    {
        $courses = Course::published()
            ->withApiRelations()
            ->popular()
            ->paginate(20);

        return CourseResource::collection($courses);
    }
}

Poordelen van Query Scopes

1. DRY Principle - Define once, use everywhere
2. Maintainability - Changes in one place
3. Testability - Easy to test scopes independently
4. Readability - Self-documenting code

Solução 4: Query Resultaat Caching Implementeren

For data that doesn't change frequently, we implemented caching:

Redis Cachingstrategie

// app/Http/Controllers/CourseController.php
use Illuminate\Support\Facades\Cache;

class CourseController extends Controller
{
    public function index(Request $request)
    {
        $page = $request->get('page', 1);
        $cacheKey = "courses:list:page:{$page}";

        // Cache for 5 minutes
        $courses = Cache::remember($cacheKey, 300, function () {
            return Course::published()
                ->withApiRelations()
                ->orderBy('created_at', 'desc')
                ->paginate(20);
        });

        return CourseResource::collection($courses);
    }
}

Cache Invalidatie

Using Model Events:

// app/Models/Course.php
class Course extends Model
{
    protected static function booted()
    {
        // Clear cache when course is created/updated/deleted
        static::saved(function () {
            self::clearCourseListCache();
        });

        static::deleted(function () {
            self::clearCourseListCache();
        });
    }

    public static function clearCourseListCache()
    {
        // Clear all course list cache pages
        Cache::tags(['courses'])->flush();
    }
}

// app/Models/Review.php
class Review extends Model
{
    protected static function booted()
    {
        // Clear course cache when review is added
        static::created(function ($review) {
            Cache::tags(['courses'])->flush();
        });
    }
}

Using Cache Tags:

class CourseController extends Controller
{
    public function index(Request $request)
    {
        $page = $request->get('page', 1);

        $courses = Cache::tags(['courses', 'course-list'])
            ->remember("courses:list:page:{$page}", 300, function () {
                return Course::published()
                    ->withApiRelations()
                    ->orderBy('created_at', 'desc')
                    ->paginate(20);
            });

        return CourseResource::collection($courses);
    }
}

Conditionele Caching

Don't cache everything - use smart conditions:

class CourseController extends Controller
{
    public function index(Request $request)
    {
        // Don't cache if user is authenticated (personalized data)
        if ($request->user()) {
            return $this->getCoursesWithoutCache($request);
        }

        // Cache for guest users
        $page = $request->get('page', 1);
        $cacheKey = "courses:public:page:{$page}";

        $courses = Cache::remember($cacheKey, 600, function () {
            return Course::published()
                ->withApiRelations()
                ->orderBy('created_at', 'desc')
                ->paginate(20);
        });

        return CourseResource::collection($courses);
    }

    private function getCoursesWithoutCache(Request $request)
    {
        $courses = Course::published()
            ->withApiRelations()
            ->orderBy('created_at', 'desc')
            ->paginate(20);

        return CourseResource::collection($courses);
    }
}

Resultados Após Caching

Cache Hit (Warm Cache):
Total Queries: 0
Response Time: 45ms
Database Time: 0ms

Cache Miss (Cold Cache):
Total Queries: 6
Response Time: 312ms
Database Time: 178ms

Average with 80% Cache Hit Rate:
Response Time: 98ms
Database Load: Reduced by 80%

Resultados e Métricas de Performance

Melhoria Geral de Performance

Linha do Tempo de Performance

Week 1: Discovery and Analysis
- Installed Laravel Debugbar and Telescope
- Identified N+1 queries
- Created benchmarks

Week 2: Initial Eager Loading Implementation
- Implemented basic with() and withCount()
- Result: 3,247ms → 687ms (78.8% improvement)

Week 3: Advanced Optimization
- Constrained eager loading
- Subquery selects
- Result: 687ms → 312ms (54.6% additional improvement)

Week 4: Database and Caching
- Added indexes
- Implemented Redis caching
- Result: 312ms → 198ms (36.5% additional improvement)
- With cache: 45ms (85.6% from week 3)

Resultados de Impacto no Negócio

After deploying all optimizations:

Métricas de Experiência do Usuário

• App Store Rating: 3.2 → 4.6 stars (43.8% increase)
• User Complaints: Decreased by 89%
• Mobile App Abandonment: Decreased by 42%
• Page Load Satisfaction: Increased from 34% to 91%

Métricas Técnicas

• Server CPU Usage: 75-85% → 25-35% (65% reduction)
• Database CPU Usage: 82% → 18% (78% reduction)
• Database IOPS: 15,000 → 3,500 (77% reduction)
• Concurrent User Capacity: 500 → 2,000+ (4x increase)

Economia de Custos

• AWS RDS Costs: $890/month → $320/month (64% reduction)
• EC2 Costs: Scaled down from 3 to 2 instances, saving $185/month
• Total Monthly Savings: $755/month ($9,060/year)

Resultados de Testes de Carga

We performed load testing using Apache JMeter:

Test Configuration:
- Concurrent Users: 1,000
- Ramp-up Time: 60 seconds
- Test Duration: 10 minutes
- Endpoint: GET /api/v1/courses

Before Optimization:

Average Response Time: 3,247ms
Error Rate: 12.3% (timeouts)
Throughput: 18 requests/second
Failed Requests: 123 out of 1,000

After Optimization:

Average Response Time: 198ms
Error Rate: 0.1% (network errors)
Throughput: 285 requests/second
Failed Requests: 1 out of 1,000

Melhores Práticas e Lições Aprendidas

1. Gebruik Altijd Eager Loading voor Relaties

❌ Bad:

$courses = Course::all();

foreach ($courses as $course) {
    echo $course->instructor->name; // N+1 query
}

✅ Good:

$courses = Course::with('instructor')->get();

foreach ($courses as $course) {
    echo $course->instructor->name; // No additional query
}

2. Gebruik withCount() in Plaats van Collecties Tellen

❌ Bad:

$course->lessons->count(); // Loads all lessons into memory

✅ Good:

// In controller
$course = Course::withCount('lessons')->find($id);

// In view/resource
$course->lessons_count; // No query, no memory overhead

3. Selecteer Alleen Benodigde Kolommen

❌ Bad:

Course::with('instructor')->get(); // Loads all columns from users table

✅ Good:

Course::with('instructor:id,name,email')->get(); // Only needed columns

4. Gebruik Constrained Eager Loading voor Gefilterde Relaties

❌ Bad:

$course->reviews->where('rating', '>=', 4)->take(5); // Loads all reviews

✅ Good:

$course = Course::with(['reviews' => function ($query) {
    $query->where('rating', '>=', 4)->limit(5);
}])->find($id);

5. Benut Database Aggregaten

❌ Bad:

// Loads all reviews and calculates in PHP
$averageRating = $course->reviews->avg('rating');

✅ Good:

// Calculates in database
$course = Course::withAvg('reviews', 'rating')->find($id);
$averageRating = $course->reviews_avg_rating;

6. Maak Herbruikbare Query Scopes

✅ Good:

// app/Models/Course.php
public function scopeWithFullDetails($query)
{
    return $query->with(['instructor', 'categories'])
                 ->withCount(['lessons', 'enrollments']);
}

// Usage
Course::withFullDetails()->get();

7. Gebruik Load() voor Conditioneel Laden

$courses = Course::all();

// Only load relationships if needed
if ($request->has('include_instructor')) {
    $courses->load('instructor');
}

if ($request->has('include_reviews')) {
    $courses->load(['reviews' => fn($q) => $q->latest()->limit(5)]);
}

8. Monitor Queries in Ontwikkeling

Enable Query Logging in Development:

// app/Providers/AppServiceProvider.php
public function boot()
{
    if (app()->environment('local')) {
        DB::listen(function ($query) {
            if ($query->time > 100) {
                Log::warning('Slow query detected', [
                    'sql' => $query->sql,
                    'bindings' => $query->bindings,
                    'time' => $query->time,
                ]);
            }
        });
    }
}

9. Schrijf Tests voor Query Aantallen

public function test_course_listing_is_optimized()
{
    Course::factory()->count(20)->create();

    DB::enableQueryLog();

    $response = $this->getJson('/api/v1/courses');

    $queryCount = count(DB::getQueryLog());

    $this->assertLessThan(10, $queryCount,
        "Expected less than 10 queries, got {$queryCount}"
    );
}

10. Cache Strategisch

✅ Good Caching Strategy:

// Cache public data with longer TTL
Cache::remember('courses:featured', 3600, function () {
    return Course::featured()->withApiRelations()->get();
});

// Don't cache user-specific data
$enrolledCourses = Course::whereHas('enrollments', function ($q) use ($user) {
    $q->where('user_id', $user->id);
})->get(); // No caching

Ferramentas e Recursos Utilizados

Ferramentas de Desenvolvimento

1. Laravel Debugbar

composer require barryvdh/laravel-debugbar --dev

Features we used:

• Query count and execution time
• Duplicate query detection
• Memory usage tracking
• Timeline visualization

2. Laravel Telescope

composer require laravel/telescope
php artisan telescope:install
php artisan migrate

Features we used:

• Query watcher for slow query detection
• Request timeline
• Model event tracking
• Exception tracking

3. Laravel Clockwork

Alternative to Debugbar with browser extension:

composer require itsgoingd/clockwork

Benefits:

• Cleaner UI
• Works with Vue/React SPAs
• Query analysis tools
• Performance metrics

Ferramentas de Análise de Banco de Dados

4. MySQL EXPLAIN

EXPLAIN SELECT * FROM courses WHERE status = 'published' ORDER BY created_at DESC LIMIT 20;

Understanding EXPLAIN output:

• type: ALL = Full table scan (BAD)
• type: index = Index scan (BETTER)
• type: ref = Index lookup (GOOD)
• rows = Number of rows examined

5. MySQL Slow Query Log

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1; -- 100ms

-- Check slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Ferramentas de Teste de Performance

6. Apache JMeter

Used for load testing:

• Simulate 1,000+ concurrent users
• Measure response times under load
• Identify bottlenecks

7. New Relic APM

Application Performance Monitoring:

• Real-time performance metrics
• Transaction traces
• Database query analysis
• Error tracking

Ferramentas de Análise Estática

8. Laravel Enlightn

composer require enlightn/enlightn
php artisan enlightn

Detects:

• N+1 query patterns
• Missing indexes
• Performance anti-patterns
• Security issues

9. Larastan (PHPStan for Laravel)

composer require nunomaduro/larastan --dev
./vendor/bin/phpstan analyse

Catches:

• Type errors
• Unused variables
• Potential bugs

Monitoramento e Alertas

10. Laravel Horizon (for Queue Monitoring)

composer require laravel/horizon
php artisan horizon:install

Features:

• Queue metrics
• Failed job monitoring
• Real-time updates

Prevenir Consultas N+1 em Desenvolvimento Futuro

1. Code Review Checklist

We created a code review checklist for all pull requests:

## Performance Checklist

- [ ] All Eloquent relationship accesses use eager loading
- [ ] Used `withCount()` instead of `->count()` on collections
- [ ] Used `withAvg()`, `withSum()` for aggregates
- [ ] Selected only required columns in relationships
- [ ] Added database indexes for new queries
- [ ] Tested with Laravel Debugbar for query count
- [ ] No N+1 queries detected in Telescope
- [ ] Load test passed with >100 concurrent requests

2. Geautomatiseerd Testen

GitHub Actions Workflow:

# .github/workflows/performance-tests.yml
name: Performance Tests

on: [pull_request]

jobs:
  performance:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v2

      - name: Setup PHP
        uses: shivammathur/setup-php@v2
        with:
          php-version: 8.2

      - name: Install Dependencies
        run: composer install

      - name: Run Performance Tests
        run: |
          php artisan test --testsuite=Performance

      - name: Check Query Count
        run: |
          php artisan test --filter=test_.*_query_count

Performance Test Example:

// tests/Performance/ApiPerformanceTest.php
class ApiPerformanceTest extends TestCase
{
    public function test_course_listing_has_acceptable_query_count()
    {
        Course::factory()->count(20)->hasReviews(10)->create();

        DB::enableQueryLog();
        $this->getJson('/api/v1/courses');
        $queries = DB::getQueryLog();

        $this->assertLessThan(
            10,
            count($queries),
            'Course listing exceeded maximum query count. Found: ' . count($queries)
        );
    }

    public function test_course_listing_response_time_is_acceptable()
    {
        Course::factory()->count(20)->create();

        $start = microtime(true);
        $this->getJson('/api/v1/courses');
        $duration = (microtime(true) - $start) * 1000;

        $this->assertLessThan(
            500,
            $duration,
            "Response time {$duration}ms exceeded 500ms threshold"
        );
    }
}

3. Ontwikkelomgeving Setup

Automatic Debugbar in Local:

// config/debugbar.php
return [
    'enabled' => env('APP_ENV') === 'local',

    'options' => [
        'db' => [
            'with_params' => true,
            'backtrace' => true,
            'timeline' => true,
            'duration_background' => true,
            'explain' => [
                'enabled' => true,
                'types' => ['SELECT'],
            ],
            'hints' => true,
            'show_copy' => true,
        ],
    ],
];

4. Aangepast Artisan Commando voor Query Analyse

// app/Console/Commands/AnalyzeEndpointQueries.php
<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Route;

class AnalyzeEndpointQueries extends Command
{
    protected $signature = 'analyze:queries {endpoint}';
    protected $description = 'Analyze database queries for a specific endpoint';

    public function handle()
    {
        $endpoint = $this->argument('endpoint');

        DB::enableQueryLog();

        $response = $this->call('GET', $endpoint);

        $queries = DB::getQueryLog();

        $this->info("Total Queries: " . count($queries));
        $this->info("Total Time: " . collect($queries)->sum('time') . "ms");

        // Find duplicate queries
        $duplicates = collect($queries)
            ->groupBy('query')
            ->filter(fn($group) => $group->count() > 1);

        if ($duplicates->count() > 0) {
            $this->error("\nDuplicate Queries Found:");

            foreach ($duplicates as $query => $instances) {
                $this->warn("Executed {$instances->count()} times:");
                $this->line($query);
            }
        }

        // Show slowest queries
        $this->info("\nSlowest Queries:");
        collect($queries)
            ->sortByDesc('time')
            ->take(5)
            ->each(function ($query) {
                $this->line("{$query['time']}ms: {$query['query']}");
            });
    }
}

Usage:

php artisan analyze:queries /api/v1/courses

5. IDE Snippets voor Eager Loading

PHPStorm Live Template:

// Abbreviation: eload
// Description: Eloquent eager loading with common relationships

$${MODEL} = ${MODEL}::with([
    '${RELATION}',
])
->withCount(['${COUNT_RELATION}'])
->get();

6. Documentatiestandaarden

We updated our documentation to include performance notes:

/**
 * Get all published courses with related data
 *
 * Performance: Uses eager loading to prevent N+1 queries
 * Expected query count: 6-8 queries
 * Expected response time: <300ms for 20 results
 *
 * @return \Illuminate\Http\Resources\Json\AnonymousResourceCollection
 */
public function index()
{
    $courses = Course::published()
        ->withApiRelations()
        ->paginate(20);

    return CourseResource::collection($courses);
}

7. Team Training

We conducted training sessions covering:

1. Understanding ORM Behavior

   • How Eloquent lazy loads relationships
   • When queries are actually executed
   • Difference between get(), all(), find()

2. Hands-on Workshop

   • Identifying N+1 queries
   • Using Debugbar and Telescope
   • Writing performant queries

3. Performance Culture

   • "Performance is a feature"
   • Always test with realistic data volumes
   • Monitor production metrics

Conclusão

Resumo da Nossa Jornada

Over four weeks, we transformed our Laravel API from a slow, inefficient system to a high-performance platform:

• 93.9% reduction in response time (3,247ms → 198ms)
• 96.7% fewer database queries (181 → 6 queries)
• $9,060/year in infrastructure cost savings
• 4x increase in concurrent user capacity
• 43.8% improvement in app store ratings

Principais Conclusões

1. N+1 queries are common - They affect almost every Laravel application at some point
2. Early detection is crucial - Use Debugbar and Telescope from day one
3. Eager loading is your friend - Master with(), withCount(), and withAvg()
4. Database indexes matter - They can provide 10x-100x performance improvements
5. Cache strategically - Not everything should be cached, but public data should be
6. Test performance - Include query count assertions in your test suite
7. Monitor production - Use APM tools to catch regressions early

O Que Vem a Seguir?

Our optimization journey continues:

Upcoming Improvements:

• Implement database read replicas for even better scalability
• Add full-text search using Laravel Scout + Meilisearch
• Optimize image delivery with CDN and lazy loading
• Implement GraphQL for more efficient data fetching
• Add request-level caching with Varnish

Reflexões Finais

Performance optimization is not a one-time task—it's an ongoing process. By establishing good practices, monitoring systems, and a performance-conscious culture, we've set ourselves up for sustainable growth.

The N+1 query problem taught us that small inefficiencies compound quickly at scale. A single extra query multiplied by thousands of requests per hour can bring down even a powerful server.

The good news? With modern tools and best practices, these problems are entirely preventable and fixable.

Get Professional Laravel Optimization Services →

Perguntas Frequentes

P1: How do I detect N+1 queries in my application?

Answer: Use Laravel Debugbar or Telescope in your local environment:

# Install Debugbar
composer require barryvdh/laravel-debugbar --dev

# Or install Telescope
composer require laravel/telescope
php artisan telescope:install

Then check the "Queries" tab for duplicate queries or high query counts.

P2: Does eager loading always improve performance?

Answer: Not always. If you're loading relationships you don't actually use, you're wasting resources. Only eager load what you need:

// Bad: Loading relationships that aren't used
$courses = Course::with(['lessons', 'reviews', 'enrollments'])->get();
echo $courses->first()->title; // Only using title

// Good: Only load what you use
$courses = Course::select('id', 'title')->get();

P3: What's the difference between with() and load()?

Answer:

• with(): Eager loading at query time (before the model is retrieved)
• load(): Lazy eager loading (after the model is retrieved)

// with() - loads instructor immediately
$courses = Course::with('instructor')->get();

// load() - loads instructor after initial query
$courses = Course::all();
$courses->load('instructor'); // Loads for all courses in one query

Use load() when you conditionally need relationships:

$courses = Course::all();

if ($request->has('include_instructor')) {
    $courses->load('instructor');
}

P4: How do I eager load nested relationships?

Answer: Use dot notation:

// Load courses with reviews and review authors
$courses = Course::with('reviews.user')->get();

// Access without additional queries
foreach ($courses as $course) {
    foreach ($course->reviews as $review) {
        echo $review->user->name; // No N+1 query
    }
}

P5: Can I use eager loading with pagination?

Answer: Yes! Eager loading works perfectly with pagination:

$courses = Course::with('instructor')
    ->withCount('lessons')
    ->paginate(20);

The eager loading applies only to the current page's results.

P6: How do I handle polymorphic relationships?

Answer: Use morphWith or eager load with type constraints:

// Polymorphic relationship (comments on courses and lessons)
$comments = Comment::with('commentable')->get();

// Or use morphMap for cleaner code
Relation::morphMap([
    'course' => Course::class,
    'lesson' => Lesson::class,
]);

P7: What about very large datasets?

Answer: Use chunk() or cursor() for memory efficiency:

// Process 1000 records at a time
Course::with('instructor')->chunk(1000, function ($courses) {
    foreach ($courses as $course) {
        // Process each course
    }
});

// Or use cursor for even better memory efficiency
foreach (Course::with('instructor')->cursor() as $course) {
    // Process each course
}

P8: Should I always use withCount()?

Answer: Yes, when you only need the count. It's much more efficient:

// Bad: Loads all lessons into memory
$course->lessons->count(); // Memory intensive

// Good: Counts in database
$course = Course::withCount('lessons')->find($id);
$course->lessons_count; // No memory overhead

P9: How do I optimize API resources?

Answer: Only include needed data and use conditional relationships:

class CourseResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'title' => $this->title,

            // Conditional attributes
            'instructor' => $this->when(
                $this->relationLoaded('instructor'),
                new UserResource($this->instructor)
            ),

            // Use when() to avoid loading relationships unnecessarily
            'lessons' => $this->when(
                $request->include_lessons,
                LessonResource::collection($this->lessons)
            ),
        ];
    }
}

P10: How can I prevent N+1 queries in my team?

Answer: Implement these practices:

1. Code Review Checklist - Require query count checks
2. Automated Tests - Assert query counts in tests
3. CI/CD Integration - Run performance tests on every PR
4. Monitoring - Use APM tools in production
5. Training - Educate team on Eloquent performance
6. Documentation - Document expected query counts

// Example test
public function test_endpoint_query_count()
{
    DB::enableQueryLog();
    $this->get('/api/courses');
    $this->assertCount(6, DB::getQueryLog());
}

Recursos Adicionais

Documentação Oficial

• Laravel Eloquent Relationships
• Laravel Eager Loading
• Laravel Query Optimization

Tools

• Laravel Debugbar
• Laravel Telescope
• Laravel Enlightn

Artigos

• Eloquent Performance Patterns
• Laravel Beyond CRUD

Community

• Laravel News
• Laracasts Forum
• Laravel.io Community