How I Improved Laravel Performance - 2453 Queries Down To 4

Hello everyone.

I have a laravel code that actually visualize list of the users who are featured in the platform. Unfortunately this code takes 2453 queries to load this data to the admin user. Today, I will show you how to can improve it from there to only 4.

Summary

Currently I have this code which has the following result:

Table of Content

• Code Investigation

  • Observation:

• Refactoring

  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5

• Assesment after refactoring

• Full Code

• Video Tutorial

Code Investigation

Unfortunately this code is not written in an optimized way, therefore I can see code duplication, dumping all the records in a single page (without pagination) etc.

Here is the controller:

public function showUsersFeatured(Request $request)
{
    if ($request->segment(2) == 'unitedstates') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'unitedstates')->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'unitedstates')->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'unitedstates')->where('gender', '=', 'woman')->get();
        $country = 'United States';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    } elseif ($request->segment(2) == 'singapore') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'singapore')->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'singapore')->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'singapore')->where('gender', '=', 'woman')->get();
        $country = 'Singapore';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    } elseif ($request->segment(2) == 'thailand') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'thailand')->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'thailand')->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'thailand')->where('gender', '=', 'woman')->get();
        $country = 'Thailand';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    } elseif ($request->segment(2) == 'indonesia') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'indonesia')->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'indonesia')->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'indonesia')->where('gender', '=', 'woman')->get();
        $country = 'Indonesia';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    } elseif ($request->segment(2) == 'home') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('featurehome', '!=', 0)->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('featurehome', '!=', 0)->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('featurehome', '!=', 0)->where('gender', '=', 'woman')->get();
        $country = 'Home';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    }
    $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'malaysia')->get();
    $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'malaysia')->where('gender', '=', 'man')->get();
    $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'malaysia')->where('gender', '=', 'woman')->get();
    $country = 'Malaysia';
    return view('admin.featured-users.showfeatured')->with([
        'usersfeatured' => $usersfeatured,
        'usersfeaturedman' => $usersfeaturedman,
        'usersfeaturedwoman' => $usersfeaturedwoman,
        'country' => $country,
    ]);
    return view('admin.featured-users.showfeatured')->with([
        'usersfeatured' => $usersfeatured,
        'usersfeaturedman' => $usersfeaturedman,
        'usersfeaturedwoman' => $usersfeaturedwoman,
        'country' => $country,
    ]);
}

Observation:

• Similar pattern of code
• Return to views in multiple times
• If-else code block
• Dead code (unused)

Refactoring

I will do refactoring step by step.

Step 1

Get the right country based on your segment.

$segment = $request->segment(2);
$countries = [
    'unitedstates' => ['name' => 'United States', 'featuredin' => 'unitedstates'],
    'singapore' => ['name' => 'Singapore', 'featuredin' => 'singapore'],
    'thailand' => ['name' => 'Thailand', 'featuredin' => 'thailand'],
    'indonesia' => ['name' => 'Indonesia', 'featuredin' => 'indonesia'],
    'home' => ['name' => 'Home', 'featuredin' => null, 'featurehome' => true],
    'malaysia' => ['name' => 'Malaysia', 'featuredin' => 'malaysia'], // Default country
];
$countryData = $countries[$segment] ?? $countries['malaysia'];

Step 2

Categories common behavior and put it in one place.

$baseQuery = User::where('isfeatured', '!=', 0);
if (isset($countryData['featurehome'])) {
    $baseQuery->where('featurehome', '!=', 0);
} else {
    $baseQuery->where('featuredin', '=', $countryData['featuredin']);
}

This 6 lines of code actually reduces the 30 lines of code and many duplicated code.

Step 3

Paginate the $baseQuery and then use the lazy load of the relationship records.

$usersfeatured = $baseQuery->paginate(50);
$usersfeatured->load(['usercity', 'tempphoto']);
$usersfeaturedman = $usersfeatured->where('gender', 'man');
$usersfeaturedwoman = $usersfeatured->where('gender', 'woman');

After that, I applied where condition for getting the featured user for man and woman.

Step 4

Finally I would love to pass these data to the view.

return view('admin.featured-users.showfeatured')->with([
    'usersfeatured' => $usersfeatured,
    'usersfeaturedman' => $usersfeaturedman,
    'usersfeaturedwoman' => $usersfeaturedwoman,
    'country' => $countryData['name'],
]);

Full refactored method here:

public function index(Request $request)
{
    $segment = $request->segment(2);
    $countries = [
        'unitedstates' => ['name' => 'United States', 'featuredin' => 'unitedstates'],
        'singapore' => ['name' => 'Singapore', 'featuredin' => 'singapore'],
        'thailand' => ['name' => 'Thailand', 'featuredin' => 'thailand'],
        'indonesia' => ['name' => 'Indonesia', 'featuredin' => 'indonesia'],
        'home' => ['name' => 'Home', 'featuredin' => null, 'featurehome' => true],
        'malaysia' => ['name' => 'Malaysia', 'featuredin' => 'malaysia'], // Default country
    ];
    $countryData = $countries[$segment] ?? $countries['malaysia'];
    $baseQuery = User::where('isfeatured', '!=', 0);
    if (isset($countryData['featurehome'])) {
        $baseQuery->where('featurehome', '!=', 0);
    } else {
        $baseQuery->where('featuredin', '=', $countryData['featuredin']);
    }
    $usersfeatured = $baseQuery->paginate(50);
    $usersfeatured->load(['usercity', 'tempphoto']);  // Lazy eager load after main fetch
    $usersfeaturedman = $usersfeatured->where('gender', 'man');
    $usersfeaturedwoman = $usersfeatured->where('gender', 'woman');
    return view('admin.featured-users.showfeatured')->with([
        'usersfeatured' => $usersfeatured,
        'usersfeaturedman' => $usersfeaturedman,
        'usersfeaturedwoman' => $usersfeaturedwoman,
        'country' => $countryData['name'],
    ]);
}

Step 5

I tweak the blade view file a bit also (⚠️ which does not improve the performance.)

@foreach($usersfeatured as $key => $userfeatured)
  <tr class="even:bg-gray-50">
      <td class="whitespace-nowrap py-4 pl-4 pr-3 text-sm font-medium text-gray-900 sm:pl-3">{{ ++$key }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->username }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->featuredsince ? $userfeatured->featuredsince->format('d M, Y') : '' }} </td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->featuredin }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->created_at->diffForHumans() }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->membershiptype }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->gender }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ optional($userfeatured->usercity)->currentcountry }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
          @if($userfeatured->tempphoto)
              <a href="" target="_blank">
                  <img src="{{ $userfeatured->profilePhoto() }}" alt="" width="50" height="50">
              </a>
          @endif
      </td>
      <td
          class="relative whitespace-nowrap py-4 pl-3 pr-4 text-right text-sm font-medium sm:pr-3">
          <a href="#" class="text-indigo-600 hover:text-indigo-900">Unfeature</a>
      </td>
  </tr>
@endforeach

Assesment after refactoring

After refactoring, here is the result that I found.

Full Code

I created a pull request in the github. You can find the the full code here.

Here is my proposed solution

Video Tutorial

If you are a visual learner instead of text, you can watch this video. I always appreciate if you have different thoughts to make this better.

I hope this solution will help you in the future.

Thanks for reading.