Story: How We Fixed N+1 Query Problem on Laravel Daily Website

We recently noticed a performance issue with Laravel Daily website: the list of courses was loading too slowly. So, this article will be a story of how we fixed the N+1 query problem.

This is a typical scenario of not optimizing the page in the beginning and noticing the performance problem only when the database grows. We're guilty of this, too.

These were the numbers on the Debugbar for the list of courses:

• 269 queries
• 48 MB RAM
• 618 ms

But when we loaded the same page as a non-logged-in user...

Totally different numbers:

• 6 queries
• 5 MB RAM
• 151 ms

Wow. So, we were loading something for the logged-in customers?

It turns out - we have a progress bar that tracks the completion of courses!

And that progress bar was written in a way, that it caused N+1 queries and expensive foreach loops.

app/Http/Controllers/CourseController.php

// ...
 
public function index()
{
    $courses = Course::published()
        ->with('media')
        ->get();
 
    return view('front.pages.courses.index', [
        'courses' => $courses,
    ]);
}

resources/views/front/pages/courses/index.blade.php

@foreach ($courses as $course)
    <li
            class="flex flex-col p-2 overflow-hidden bg-gray-700 border-t border-gray-600 shadow-2xl shadow-primary-800/10 rounded-xl">
        <header class="flex flex-col grow justify-between px-2 py-4 mt-2">
            <h2 class="text-xl font-bold">
                <a href="{{ $course->url }}">{{ $course->title }}</a>
            </h2>
        </header>
        @auth
            @if($progress = $course->currentUserProgress())
                <div class="flex flex-col justify-end grow p-2">
                    <x-course-progress :progress="$progress"/>
                </div>
            @endif
        @endauth
    </li>
@endforeach

app/Models/Course.php

// ...
 
public function currentUserProgress(): int
{
    return Cache::remember($this->getProgressCacheKey(), now()->addHour(), function () {
        $published = $this->publishedLessons;
 
        $completed_count = auth()->user()->completedLessons()
            ->whereIn('lesson_id', $published->pluck('id'))
            ->count();
 
        return toPercent($completed_count, $published->count());
    });
}
 
public function getProgressCacheKey(): string
{
    return 'user.' . auth()->id() . '.course.' . $this->id . '.progress';
}
 
// ...

app/helpers.php

// ...
 
if (! function_exists('toPercent')) {
    function toPercent($count, $total, $decimals = 0)
    {
        if ($decimals > 0) {
            return round(($count / max(1, $total)) * 100, $decimals);
        }
 
        return (int) floor(($count / max(1, $total)) * 100);
    }
}

Yes, we're using the cache for caching progress bars for specific course and specific user. But still, we do that within a loop of courses!

So, there were two problems:

• The page with the list of courses grew from 10-20 to 60+ courses now: that's the main reason why we didn't notice (or pay attention to) this problem in the first place
• Caching was ineffective: saving data for an hour, so the same user visiting the page the following day still runs the full DB query

So what did we do?

Optimized the Query

Of course, the obvious path is to work on the Query itself and making sure it does the job, instead of relying on PHP to do the heavy lifting.

But how does that look like? Well, first of all, we had to find the Query that was causing the issue. We used Laravel Debugbar for that:

This is the Controller code that causes this query problem:

$popularCourses = Course::whereKey([47, 50, 55]) // Hardcoded courses
    ->with('media')
    ->latest('id')
    ->take(3)
    ->get();
 
$courses = Course::published()
    ->with('media')
    ->get();

We repeat almost identical query (with ID differences) for each lesson. And that's the N+1 problem. So we had to optimize it:

$popularCourses = Course::whereKey([47, 50, 55]) // Hardcoded courses
    ->with('media')
    ->withCount('publishedLessons')
    ->with([
        'publishedLessons' => function (HasMany $query) {
            return $query
                ->select(['id', 'course_id'])
                ->withCount(['users' => function ($query) {
                    return $query->where('user_id', auth()->id());
                }]);
        },
    ])
    ->latest('id')
    ->take(3)
    ->get();
 
$courses = Course::published()
    ->with('media')
    ->withCount('publishedLessons')
    ->with([
        'publishedLessons' => function (HasMany $query) {
            return $query
                ->select(['id', 'course_id'])
                ->withCount(['users' => function ($query) {
                    return $query->where('user_id', auth()->id());
                }]);
        },
    ])
    ->get();

This allowed us to select only the data we need and eager load the count of completed lessons for the current user. Inside our method for progress bar, we were able to simplify it:

app/Models/Course.php

// ...
 
public function currentUserProgress(): int
{
    $published = $this->publishedLessons;
 
    return toPercent($published->sum('users_count'), $published->count());
}
 
// ...

This of course, greatly improved the performance of the page. And we were able to see the results:

• From 260 queries to 19 queries
• From 48 MB RAM to 9 MB RAM
• From 500ms+ loading time to 200ms loading time
• From 225ms to 25ms on the database

The obvious benefit!

Of course, there is still more work to do, but this was a good start of optimizing this page.

So What Have We Learned?

When this happened, we were confused. It seems like the page was working just fine recently! But then we realized - our DB has more data now.

Compare the numbers between the (re-)launch of Laravel Daily website in 2022 and now:

• We went from ~10 courses at launch to 60+ courses now
• We went from a few users to thousands of users
• We went from 0 completed lessons to 260k completed lessons in the DB table

And that's when the issue started to show up.

Any tips to ourselves (and to you) to prevent this?

• Test your DB queries with a large dataset
• Track the performance of your app: from time to time, just load the page and see how it performs, for both public and logged-in users
• Write good seeders: make sure you have enough data to test your application
• For aggregating operations, rely on the database instead of PHP loops: it's much faster in most cases
• Use cache wisely: it should come to the rescue only when the un-cached queries are already optimized

If you want to learn more, check out our PREMIUM course on Eloquent Performance.