Optimization of 3 Eloquent requests (1.5 seconds of request duration...)-CodePudding

I would like to optimize these 3 queries that allow me to display the users who have posted the most photos on our site, according to date criteria (current week, last week, since the beginning).

The query time is measured at 1.5 seconds with the debug bar, it's really long ! Do you know how to optimize it ?

public function show()
{
    $currentWeek = User::whereHas('pictures')
        ->whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()]))
        ->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])])
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    $lastWeek = User::whereHas('pictures')
        ->whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()]))
        ->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()])])
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    $overall = User::whereHas('pictures')
        ->whereHas('pictures')
        ->withCount('pictures')
        ->orderBy('pictures_count', 'DESC')
        ->limit(10)
        ->get();

    return view('users.leaderboard', [
        'currentWeek' => $currentWeek,
        'lastWeek' => $lastWeek,
        'overall' => $overall,
    ]);
}

CodePudding user response：

First things first, you've called whereHas twice on the pictures relationship, so you can get rid of the unqualified call.

$currentWeek = User::whereHas('pictures', fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()]))
    ->withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()])])
    ->orderBy('pictures_count', 'DESC')
    ->limit(10)
    ->get();

This reduces the SQL query from this:

select `users`.*, (
    select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null
) as `pictures_count`
from `users`
where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `pictures`.`deleted_at` is null)
    and exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null)
    and `users`.`deleted_at` is null
    order by `pictures_count` desc
    limit 10

To this:

select `users`.*, (
    select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null
) as `pictures_count`
from `users`
where exists (select * from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null)
    -- no second where exists clause
    and `users`.`deleted_at` is null
    order by `pictures_count` desc
    limit 10

Now, you've only got one condition in the where clause. It selects users that have pictures in the specified date range. Looks better, right?

But, you're already using withCount with a closure so you only count pictures in the date range. What happens if the condition doesn't match? It returns zero. Since you're reverse sorting by count anyway, the other call to whereHas can also go.

$currentWeek = User::withCount(['pictures' => fn ($q) => $q->whereBetween('created_at', [now()->startOfWeek(), now()->endOfWeek()])])
    ->orderBy('pictures_count', 'DESC')
    ->limit(10)
    ->get();

Now your SQL looks like this:

select `users`.*, (
    select count(*) from `pictures` where `users`.`id` = `pictures`.`user_id` and `created_at` between ? and ? and `pictures`.`deleted_at` is null
) as `pictures_count`
    from `users`
    where `users`.`deleted_at` is null
    -- no where exists clauses at all any more
    order by `pictures_count` desc
    limit 10

And it should run much faster. This does change your data somewhat; the resulting collection will always have 10 items, even if some of them are zero. If you don't want zeros in your leaderboard, just filter them out of the collection.