How to speed up this eloquent query in Laravel 5.2?

$countLeaseCommercial = DB::table('listings')
            ->join('properties', function ($join) {
                $join->on('listings.property_id', '=', 'properties.id')
                    ->where('properties.type', '=', 'Commercial')
                    ->where('listings.status', '=', 'Leased')
                    ->where('listings.user_id', '=', $this->user->id);
            })
            ->count();

How to speed up this query to be faster?

SilenceBringer

5 years ago

Level 55

hi @joshsalway

You can play with the query itself. For example, rewrite it to something like:

$countLeaseCommercial = DB::table('properties')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('listings')
            ->whereRaw('properties.id = listings.property_id')
            ->where('properties.type', '=', 'Commercial');
    })
    ->where('listings.status', '=', 'Leased')
    ->where('listings.user_id', '=', $this->user->id)
    ->count();

Also you can make index in mysql columns you filtered on (properties -> type, listings -> status), and be sure that user_id and property_id are indexes too

Tray2

5 years ago

Level 73

you can do that by adding the proper indexes to the fields you filter on.

I higly suggest that you take a look at this course by Jonathan Reinink

https://eloquent-course.reinink.ca/

Please or to participate in this conversation.