Optimize Laravel Queries & Reduce Memory Usage for Faster Performance

Hello everyone

Today I will show you how can I optimize Laravel Queries and reduce memory uses to making an application faster. I will use laravel sub-query and database raw query to make such kind of improvements.

At a Glance

Here is the stats of current implementations:

Here is the report from debugbar package:

Honestly it’s not an optimize way to write code!

Current Implementation

Here I would love to show you the current implementation.

Code in the Controller.

public function index()
{
    $messages = Message::with('conversations', 'conversations.userone', 'conversations.usertwo', 'user.messages')
        ->groupBy('conversation_id')
        ->paginate(50);

    $messagesToAdminAccounts = DB::table('users')
        ->join('messages', 'users.id', '=', 'messages.user_id')
        ->join('conversations', function ($joinagain) {
            $joinagain->on('users.id', '=', 'conversations.user_one')->orOn('users.id', '=', 'conversations.user_two');
        })
        ->where('users.createdby', '=', 'admin')
        ->select('users.*', 'messages.message', 'messages.is_seen', 'messages.isflagged', 'messages.deleted_from_sender', 'messages.deleted_from_receiver', 'messages.user_id', 'messages.conversation_id', 'conversations.user_one', 'conversations.user_two', 'conversations.status', 'conversations.ispotentialuser')
        ->groupBy('messages.conversation_id')
        ->get()->count();

    $potentialMessages  = DB::table('messages')
        ->join('conversations', 'messages.conversation_id', '=', 'conversations.id')
        ->where('conversations.ispotentialuser', '=', 1)
        ->select('messages.*')
        ->get()->count();

    $flagedMessages  = DB::table('messages')
        ->join('conversations', 'messages.conversation_id', '=', 'conversations.id')
        ->where('messages.isflagged', 1)
        ->select('messages.*')
        ->get()->count();

    $totalmessages = Message::with('Conversations', 'User')->get()->count();

    return view('all-messages')
        ->with([
          'messages' => $messages, 
          'messagestoadminaccounts' => $messagesToAdminAccounts, 
          'potentialmessages' => $potentialMessages, 
          'flagedmessages' => $flagedMessages, 
          'totalmessages' => $totalmessages
        ]);
}

Here is the code for all-messages.blade.php file. I like to show you the @foreach so that you can understand the code better.

@foreach($messages as $message)
    <tr>
        <td class="whitespace-nowrap py-4 pl-4 pr-3 text-sm font-medium text-gray-900 sm:pl-6">
            {{ $message->conversations->userone->fullname }}
        </td>
        <td class="whitespace-nowrap py-4 pl-4 pr-3 text-sm font-medium text-gray-900 sm:pl-6">
            {{ $message->conversations->usertwo->fullname }}
        </td>
        <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
            {{ $message->user->messages->count() }}
        </td>
        <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-800">
            <div class="">
                {{ $message->user->messages()->orderByDesc('created_at')->first()->created_at->format('d F Y') }}
            </div>
            <span class="text-sm text-gray-400">
                {{ Str::limit($message->user->messages()->orderByDesc('created_at')->first()->message,50) }}
            </span>
        </td>
    </tr>
@endforeach

My Observation

I found following issues with the current implementation:

• Memory leaking issue: They use ...get()->count() which actually load all records on memory.
• Unnecessary join tables: This joining might be over uses, we may not need them. Such approaches used in few places.

  DB::table('messages')
    ->join('conversations', 'messages.conversation_id', '=', 'conversations.id')
    ...

• Unnecessary Code: We can achieve some result without writing extra code. This might be overkill.

  Message::with('Conversations', 'User')->get()->count();

• Write query on blade file: It’s not a write approach to write query on the blade file. I can see here:

  // Counting
  {{ $message->user->messages->count() }}
  
  // Re-query
  {{ $message->user->messages()->orderByDesc('created_at')->first()->created_at->format('d F Y') }}
  {{ Str::limit($message->user->messages()->orderByDesc('created_at')->first()->message,50) }}

How to improve this code?

Here is my plan:

Merge similar types of queries:
I want to merge following queries into one query because they are almost similar:

$potentialMessages  = DB::table('messages')
    ->join('conversations', 'messages.conversation_id', '=', 'conversations.id')
    ->where('conversations.ispotentialuser', '=', 1)
    ->select('messages.*')
    ->get()->count();

$flagedMessages  = DB::table('messages')
    ->join('conversations', 'messages.conversation_id', '=', 'conversations.id')
    ->where('messages.isflagged', 1)
    ->select('messages.*')
    ->get()->count();

$totalmessages = Message::with('Conversations', 'User')->get()->count();

Use Sub Query instead of writing query on the blade file
Honestly it’s not a good practice to write query on the blade (template) file. The MVC pattern strictly discourage developer to follow this.

So, I would like to use subquery in the controller to achieve following parts:

{{ $message->user->messages()->orderByDesc('created_at')->first()->created_at->format('d F Y') }}

{{ Str::limit($message->user->messages()->orderByDesc('created_at')->first()->message,50) }}

Optimize Code

Here is my plan how to optimize this smelly code:

Get all messages with sub-query

$conversations = Conversation::query()
    ->withCount('messages')
    ->with('userone', 'usertwo')
    ->addSelect([
        'last_message_date' => Message::select('created_at')
            ->whereColumn('conversation_id', 'conversations.id')
            ->latest()
            ->take(1),
        'last_message' => Message::select('message')
            ->whereColumn('conversation_id', 'conversations.id')
            ->latest()
            ->take(1),
    ])
    ->withCasts([
        'last_message_date' => 'datetime'
    ])
    ->paginate();

ℹ️ BTW, I use withCasts() on fly because last_message_date column is not actual column therefore I cannot use with cast on the model side directory. If we don’t cast this, I am not able to use Carbon functionalities on the blade.

Instead of counting the total messages on the blade page, I use withCount('messages') has many relationship on the controller side so that I can use it in the blade easily.

Optimize Similar Types of Code

As I mentioned, there are some similar types of queries, here is my suggestion to merge into one query:

Message::query()
  ->join('conversations', 'messages.conversation_id', '=', 'conversations.id')
  ->select(DB::raw("
      count('id') as totalMessages,
      SUM(CASE WHEN isflagged = 1 THEN 1 ELSE 0 END) as flaggedMessages,
      SUM(CASE WHEN conversations.ispotentialuser = 1 THEN 1 ELSE 0 END) as potentialMessages
  "))
  ->first();

Here I use CASE WHEN {SOME_CONDITION} THEN 1 ELSE 0 END statement to calculate similar types of queries by one query. Check more and if you are a visual learner, then check here

Remove query on the blade file

Since I use sub query, I write blade file like this:

<td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
    {{ $conversation->messages_count }}
</td>
<td class="whitespace-nowrap px-3 py-4 text-sm text-gray-800">
    <div class="">
        {{ $conversation->last_message_date->format('d F Y') }}
    </div>
    <span class="text-sm text-gray-400">
        {{ Str::limit($conversation->last_message, 50) }}
    </span>
</td>

Full Optimized Code

I put the full optimized code in the collapsible tab.

Code for the controller:

public function refactor()
{
    $conversations = Conversation::query()
        ->withCount('messages')
        ->with('userone', 'usertwo')
        ->addSelect([
            'last_message_date' => Message::select('created_at')
                ->whereColumn('conversation_id', 'conversations.id')
                ->latest()
                ->take(1),
            'last_message' => Message::select('message')
                ->whereColumn('conversation_id', 'conversations.id')
                ->latest()
                ->take(1),
        ])
        ->withCasts([
            'last_message_date' => 'datetime'
        ])
        ->paginate();

    $messageToAdminAccounts = Conversation::query()
        ->whereHas('userone', function ($userOne) {
            $userOne->where('createdby', 'admin');
        })
        ->orWhereHas('usertwo', function ($userOne) {
            $userOne->where('createdby', 'admin');
        })
        ->count();

    $message = Message::query()
        ->join('conversations', 'messages.conversation_id', '=', 'conversations.id')
        ->select(DB::raw("
                count('id') as totalMessages,
                SUM(CASE WHEN isflagged = 1 THEN 1 ELSE 0 END) as flaggedMessages,
                SUM(CASE WHEN conversations.ispotentialuser = 1 THEN 1 ELSE 0 END) as potentialMessages
            "))->first();

    return view('refactor')
        ->with([
            'conversations' => $conversations,
            'messageToAdminAccounts' => $messageToAdminAccounts,
            'totalMessages' => $message->totalMessages,
            'flaggedMessages' => $message->flaggedMessages,
            'potentialMessages' => $message->potentialMessages,
        ]);
}

Code for the blade view:

@foreach($conversations as $conversation)
  <tr>
      <td class="whitespace-nowrap py-4 pl-4 pr-3 text-sm font-medium text-gray-900 sm:pl-6">
          {{ $conversation->userone->fullname }}
      </td>
      <td class="whitespace-nowrap py-4 pl-4 pr-3 text-sm font-medium text-gray-900 sm:pl-6">
          {{ $conversation->usertwo->fullname }}
      </td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
          {{ $conversation->messages_count }}
      </td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-800">
          <div class="">
              {{ $conversation->last_message_date->format('d F Y') }}
          </div>
          <span class="text-sm text-gray-400">
              {{ Str::limit($conversation->last_message, 50) }}
          </span>
      </td>
  </tr>
@endforeach

This optimization actually improved the query to this level:

Here is the stats of current implementations:

Here is the evidence from debugbar:

Video

If you are a visual learner, you can go with this video that covers exactly step by step process how can I improve this code:

Full Code

Here is the full code that you can get it from the GITHUB

What Next?

Still there is a roam to make it improve. One approach I can feel by indexing the database column.

Hope it will help you.

Thanks for reading.