The Sneakiest Performance Bug I’ve Ever Encountered — N+1, Just Vibing in My Query Logs

I didn’t set out to write a blog post about N+1 queries. Initially, I wasn’t aware this was the issue.

Midway through working on PeriChat, I paused feature development to focus on refactoring and performance tuning. I was feeling confident — applying DRY principles, the Single Responsibility Principle, and even incorporating a few design patterns for good measure.

Then, something unexpected caught my attention.

For each Conversation Subject — essentially, every user or group the current user had interacted with — a separate query was executed to retrieve the unread message count.

On the surface, this appeared to be a lightweight component — merely a straightforward list of users and groups. Nothing that would typically trigger performance concerns.

But behind that quiet UI was a noisy backend.

Every time this component rendered, the unread counts were fetched one-by-one — each with its own query.

One subject? Two queries. Twenty subjects? Twenty-one queries.

That math wasn’t scaling, and performance was starting to sag — but there wasn’t anything obviously wrong in the code itself.

So, I popped open Clockwork to dig deeper.

Here’s the actual code that started it all.

//Group.php and User.php - data transformation method
public function toConversationArray(?int $conversationId = null): array
{
    return [
        'id'                  => $this->c_id ?? $conversationId,
        'name'                => $this->name,
        'avatar'              => $this->avatar,
        'type'                => ConversationTypeEnum::GROUP->value, // Private->value for user entity
        'typeId'              => $this->id,
        'groupUserIds'        => $this->users()->pluck('users.id'), // undefined for user entity
        'lastMessage'         => $this->last_message,
        'lastMessageSenderId' => $this->last_message_sender,
        'lastMessageDate'     => $this->last_message_date ? Carbon::parse($this->last_message_date)
                                        ->setTimezone('UTC')->toIso8601String() : null,
        'unreadMessageCount'  => $this->getUnreadCount(), // The culprit
    ];
}

// Group.php and User.php - query to get unread count
public function getUnreadCount()
{
    $query = DB::table('group_user') // user_conversation table for User entity
        ->select('unread_messages_count')
        ->where('group_id', $this->id)
        ->where('user_id', Auth::id());

    return $query->first()?->unread_messages_count;
}

At a glance, toConversationArray() looks like a simple data accessor, but that getUnreadCount() inside the method was running a query for each instance of the model — which added up fast.

As I examined the code snippet, it became clear that this method was the culprit, as confirmed by the Clockwork analysis showing multiple database events — 25 in total for just 15 conversation subjects.

That’s when it really clicked: I had walked straight into a textbook N+1 Query Problem — the classic kind, hidden behind a perfectly normal-looking UI component. And it had been vibing in my query logs the whole time.

Clockwork analysis showing a single request triggering dozens of DB queries

You might be wondering: how bad could it really be?

Well — to truly grasp this, let's dive deeper into what N+1 actually is.

The N+1 Query Dilemma Explained

The N+1 query problem is a common performance issue in applications that use Object-Relational Mapping (ORM) tools like Laravel’s Eloquent, Django ORM, or Hibernate.

This problem arises when your app makes a single query to fetch a list of records but then executes additional queries for each item in that list to retrieve related data. This is the “+N” in the problem.

Often, this occurs due to lazy loading, where the ORM fetches related data only when it is explicitly accessed. So instead of executing one query to retrieve all your data and perhaps one more for the related data, you end up with 1 plus however many items are in your list.

To see how this plays out in the real world, let me reiterate what was happening in my case.

I was fetching a list of conversation subjects — users and groups a person has messaged with — and for each one, I needed to display the unread message count.

The problem? The unread count wasn’t being fetched upfront. It was calculated inside a data transformation layer — specifically, the toConversationArray() method from the snippet above — where method getUnreadCount() ran its own query per subject.

So, if I fetched 15 subjects, I was actually running 1 query to get the subjects, and 15 more — one per subject — just for the unread counts.

That’s 16 queries in total. Doesn’t sound too bad, you’ll think,

Scale that up: 150 users and 50 groups, that’s 200 subjects? Now we’re talking 201 queries for a single page load.

This is where N+1 stops being harmless and starts quietly tanking your app’s performance — the cost doesn’t just add up, it compounds (yeah, I’m quoting Atomic Habits — I read books now😅).

These issues aren’t always obvious. Sometimes, you don’t notice them until you’re staring at your query logs or digging through a performance profiler.

Take the Laravel resource below as an example — this one is for the Message entity:

public function toArray(Request $request): array
{
    return [
        'id'          => $this->id,
        'message'     => $this->message ?? "",
        'senderId'    => (int) $this->sender_id,
        'receiverId'  => (int) $this->receiver_id,
        'groupId'     => $this->conversation->group_id,
        'sender'      => new UserResource($this->sender),
        'attachments' => collect($this->attachments)->isNotEmpty()
            ? MessageAttachmentResource::collection($this->attachments)
            : null,
        'createdAt'   => $this->created_at,
    ];
}

At a glance, $this->sender, $this->conversation and $this->attachments look like a simple value access through a defined Eloquent relationship. Nothing suspicious, right? but under the hood, just like the custom getUnreadCount() function we saw earlier, they all trigger their own database query to fetch related user (sender), conversation and attachment entities. Laravel wraps it all in syntactic sugar, sure, but the performance cost is still real.

And yes — that’s another N+1 query in the wild.

The good news? Laravel (being the GOAT framework that it is) has a graceful fix for this. We’ll talk about that next — and also some general fixes that don’t depend on Laravel or any framework at all.

Practical Fixes: Eliminating N+1 Query Issues.

Now that we’ve seen what the issue is — and how sneaky it can be — how do we fix it?

Let’s start with the Laravel-specific solution.

The fix is actually pretty simple — adding the with() method to the query builder instance to eager load the related data. That way, Laravel pulls everything in one go, instead of hitting the database again for each item.

Remember that Laravel resource from earlier — the one for the Message entity that quietly triggered extra queries for sender, conversation and attachments? The actual fix wasn’t in the resource itself, but in the query fetching those messages.

Here’s how I adjusted that part:

$query->where('c.type', '=', ConversationTypeEnum::PRIVATE ->value)
    // [query continues]      
    ->leftJoin('conversations as c', 'messages.conversation_id', '=', 'c.id')
    ->with(['conversation', 'sender', 'attachments']) // Avoid N+1 by eager loading
    ->latest()
    ->simplePaginate(10);

That one change — adding with() — loads the sender, conversation and all the attachments entities in a single query, avoiding the N+1 problem entirely.

So how do we solve this outside of Laravel’s built-in helpers — in a more general, framework-independent way?

Let’s walk through a general approach.

Step 1: Bulk Fetching

One of the cleanest ways to avoid N+1 problems is to bulk-fetch related data before transforming or looping through your items.

In my case, I used to call toConversationArray() on each user or group, and from there, call getUnreadCount() individually.

So, I switched to fetching all unread counts in bulk before mapping the data into a Data Transfer Object (DTO).

$userUnreadCounts = DB::table('user_conversation')
    ->where('user_id', $user->id)
    ->pluck('unread_messages_count', 'conversation_id');

$groupUnreadCounts = DB::table('group_user')
    ->where('user_id', $user->id)
    ->pluck('unread_messages_count', 'group_id');

Now I have all the unread counts available as arrays, ready to inject into my transformation logic.

Step 2: Use a Transformation Layer (Like a DTO) to Shape the Response

Next, I introduced a ConversationSubjectsDTO — a data class that transforms each User or Group model into the shape needed by the frontend, without triggering any extra queries.

👇 Here’s how I use it in the bulk-mapping logic:

collect([...$users, ...$groups])->map(
    fn ($model) => (new ConversationSubjectsDTO(
        $model,
        unreadCounts: $userUnreadCounts->union($groupUnreadCounts)->toArray()
    ))->toArray()
);

The DTO takes in the model (whether it’s a User or a Group), the conversation ID if applicable, and an optional array of pre-fetched unread counts. If those counts are available, it just looks up the value from the array. That’s it — no method calls that hit the database.

Let’s zoom in on the part of the DTO that makes it all click — the bit that chooses between bulk data and live queries:

private function resolveUnreadCount(): ?int
{
    // If we have unreadCounts array data, we lookup unread counts from there
    if ($this->unreadCounts !== null) {
        if ($this->model instanceof User) {
            $lookupId = $this->conversationId ?? $this->model->c_id ?? null;
            return $lookupId ? ($this->unreadCounts[$lookupId] ?? 0) : 0;
        }

        return $this->unreadCounts[$this->model->id] ?? 0;
    }

    // If we have a conversation ID, use model methods to get counts
    if ($this->conversationId !== null) {
        return $this->model instanceof User
            ? $this->model->getUnreadCount($this->conversationId)
            : $this->model->getUnreadCount();
    }

    return null;
}

This gives us a simple but powerful fallback mechanism:

• If the bulk unread counts array is passed in, the DTO pulls from it directly — no extra queries.

• If not, it falls back to the original getUnreadCount() call (which hits the DB).

🔗 Want to see the full implementation? check the ConversationSubjectsDTO class in the repo.

This approach keeps the transformation layer completely logic driven. Instead of calling database queries inside a loop, we run just two upfront queries (one for users, one for groups), and let the DTO handle the rest.

Cleaner, faster, and way more scalable.

The Result

From potentially hundreds of queries down to just a few — without losing any flexibility.

Whether you're using Laravel or not, the key takeaway is this: keep your data-fetching logic separate from your transformation logic. That separation makes it easier to bulk-fetch, optimize, and avoid performance killers like N+1.

Quick Note on When N+1 Isn't a Problem

I should mention that not every instance of having two queries indicates an N+1 problem. Sometimes, N is just 1 — and that’s perfectly fine.

Here’s an example to show what I mean:

$messages = Message::privateBetween($user);

$conversationId = $messages->isNotEmpty() ? $messages->first()->conversation_id : null;

return inertia('Conversation', [
   'selectedConversation' => (new ConversationSubjectsDTO($user, $conversationId))->toArray(),
    'messages'             => MessageResource::collection($messages),
]);

In this case, we’re returning a single conversation subject — not a list. So even though we’re technically doing two queries (one to fetch the message’s conversation and one via getUnreadCount() inside the DTO), this isn’t a performance issue. It’s a fixed cost, and it doesn’t scale with data size. In fact, I didn’t bother bulk-fetching unread counts here at all — the fallback inside the DTO is harmless in this edge case.

What we care about are the cases where that second query is called repeatedly — once for every record in a list. That’s when N+1 becomes a real concern.

So yeah, in situations like this — where selectedConversation only ever returns a single item but uses the same DTO structure from the bulk listing — it's not just acceptable, it's intentional. Clean code reuse, minimal performance impact.

Key Takeaway

• N+1 queries aren’t always obvious — they often hide behind innocent-looking methods that trigger queries deep inside loops or accessors.

• Debugging tools help you catch issues early — Clockwork, Laravel Debugbar, Rails' Bullet, Django Debug Toolbar, or even plain SQL logs.

• Eager loading with ->with() in Laravel is a powerful way to fetch related data up front and avoid query-per-record traps.

• When outside a framework, the pattern still applies, pull related data in bulk first, then transform — don’t mix queries into your loops.

• Introduce a transformation layer (like a DTO) to keep your mapping logic clean and detached from your database access.

• Not all repetition is bad — if you’re rendering a single item, the fallback query may be fine. Optimize where it scales.

Conclusion

Understanding and addressing N+1 query problems is crucial for optimizing application performance.

With the right structure and awareness, it’s one of those bugs that becomes easy to avoid — and even easier to spot. The key is to be proactive in identifying these issues and applying best practices to maintain clean, efficient code.

What started as a performance fix ended up reinforcing a deeper principle: fetching data with intent makes everything else — speed, structure, and scale — much easier to manage.

I hope this breakdown provides valuable insights into identifying subtle N+1 issues early and building interfaces that scale gracefully. Cheers!

Further Reading

Laravel Eager-Loading Doc