How to use orWhere() optimally in the query?

Question

Here is my query:

$notifications = \App\Notification::query()->where('users_scope', 'customer')
   ->with('userSeen')
   ->where(function ($query) use ($user) {
    $query->where('user_id_to', 20288)
        ->orWhere(function($q) use ($user) {
            $q->Where('user_id_to', null)
              ->Where(function($q) use ($user) {
                    $q->where('expire_at', null)->where('created_at', '>=', "2020-04-03 04:18:42");
                    $q->orWhere('expire_at', '!=', null)->where('expire_at', '>', Carbon::now());
                });
        });
    })
    ->select([DB::raw('SQL_CALC_FOUND_ROWS *')])->orderBy('id', 'desc')->limit(20)->get();

Also here is the relation (used as with()):

public function userSeen()
{
    return $this->belongsToMany(User::class, NotificationSeen::class, 'notification_id', 'user_id');
}

Here is the scenario: Query above gets the last 20 user's notifications (latest notifications list). Recently, the size of the notifications table is increased too much. It has over 8 million rows at the moment. And the query takes over 10 seconds to be executed.

Noted that, all needed indexes have been created as well on both tables (notifications and userSeen tables). Also, that relation (userSeen) is like a pivot table that indicates the user either has seen the notification or not.

Any idea how can I rewrite that query to be more optimal?

---

Explanations about the logic:

• 20288 is hardcoded and will be $user->id in reality.
• when user_id_to is null, it means it's a bulk notification (must be visible for all users)
• User can see bulk notifications if they have a bigger created_at value than the user's created_at.
• Sometimes bulk notifications has an expire time (like marketing campaigns) that must be shown to the users if still not outdated.

Answer 1

Bases on the logic you mentioned in your question, this should get you what you need, however, it probably won't help that much with the speed:

$notifications = \App\Notification::query()
    ->select([DB::raw('SQL_CALC_FOUND_ROWS *')])
    ->with('userSeen')
    ->where('users_scope', 'customer')
    ->where(function ($query) use ($user) {
        $query
            ->where('user_id_to', $user->id)
            ->orWhere(function ($query) use ($user) {
                $query
                    ->whereNull('user_id_to')
                    ->where('created_at', '>=', $user->created_at)
                    ->where(function ($query) {
                        $query->whereNull('expire_at')->orWhere('expire_at', '>=', now());
                    });
            });
    })
    ->orderByDesc('id')
    ->limit(20)
    ->get();

---

I would also suggest trying 2 separate queries instead of using SQL_CALC_FOUND_ROWS. Here are a couple of SO posts that explain why:

Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
SELECT SQL_CALC_FOUND_ROWS Query very slow greater than 250000 records

You queries would then look something like:

$query = \App\Notification::query()
    ->with('userSeen')
    ->where('users_scope', 'customer')
    ->where(function ($query) use ($user) {
        $query
            ->where('user_id_to', $user->id)
            ->orWhere(function ($query) use ($user) {
                $query
                    ->whereNull('user_id_to')
                    ->where('created_at', '>=', $user->created_at)
                    ->where(function ($query) {
                        $query->whereNull('expire_at')->orWhere('expire_at', '>=', now());
                    });
            });
    })
    ->orderByDesc('id');

$count = $query->count();
$notifications = $query->limit(20)->get();

Alternatively, you could use something like paginate().

Answer 2

I am not sure what condition you have but condition you can apply like below

$notifications = \App\Notification::query()->where('users_scope', 'customer')
        ->with('userSeen')
        ->where(function ($query) use ($user) {
            $query->where('user_id_to', 20288);
                if($user){
                    $query->orWhere(function($q) use ($user) {
                        $q->Where('user_id_to', null)
                            ->Where(function($q) use ($user) {
                                $q->where('expire_at', null)->where('created_at', '>=', "2020-04-03 04:18:42");
                                $q->orWhere('expire_at', '!=', null)->where('expire_at', '>', Carbon::now());
                            });
                    });
                }

        })
        ->select([DB::raw('SQL_CALC_FOUND_ROWS *')])->orderBy('id', 'desc')->limit(20)->get();

Even you can use when clause Ref:https://laravel.com/docs/8.x/queries#conditional-clauses