#Optimizing a slow query?

1 messages · Page 1 of 1 (latest)

eager pecan
#

I'm trying to figure out a better way to query my data, since it's taking ~5+ seconds to get the results.

I have the following models:
Lead

    public function textHistory()
    {
        return $this->hasMany(LeadTextHistory::class)->orderByDesc('created_at');
    }

    public function latestText()
    {
        return $this->hasOne(LeadTextHistory::class)->ofMany('created_at', 'max');
    }

    public function scopeUnresponded($query)
    {
        return $query->whereHas('latestText', function ($query) {
            $query->where('type', 'Incoming');
        });
    }

LeadTextHistory

    public function lead()
    {
        return $this->belongsTo(Lead::class);
    }

My controller contains:

    public function index()
    {
        $user = auth()->user();

        $query = Lead::query();

        // only show leads assigned to current user unless they are admin
        if (! $user->hasRole('admin')) {
            $query->where('user_id', $user->id);
        }
        
        $query->whereHas('latestText')
            ->with([
                'user',
                'latestText',
                'latestText.user',
                'latestText.reviews',
            ]);

        if (request()->has('search') && request()->input('search') != '') {
            $query->whereHas('latestText', function ($query) {
                $query->where('text', 'like', '%'.request()->input('search').'%');
            });
        }
 
        if (request()->has('unresponded') && request()->input('unresponded') == 'true') {
            $query->unresponded();
        }

        if (request()->wantsJson()) {
            if ($query->count() > 0) {
                // return an array of totalTexts and leads
                return [
                    'totalLeads' => $query->count(),
                    'leads' => $query->paginate(100),
                ];
            }
        }

        $leads = $query->paginate(100);

        return Inertia::render('Leads/Texts', [
            'leads' => $leads,
            'totalLeads' => $leads->total(),
            'filters' => request()->all(['status', 'unresponded', 'unreviewed']),
        ]);
    }

The problem occurs when using $query->unresponded();

Can someone help point me into the right direction on how to speed up this query?

indigo niche
#

If you add the appropriate database indexes to your table you'll see significant improvements in speed. In your case you may need indexes on the following columns if they do not already exist:

lead_text_histories:

  • 'lead_id'
  • 'created_at'
  • 'type'

Sometimes you may also need conjoined indexes if the above indexes don't improve the speed enough for very large tables such as:

  • ['created_at', 'type']

keep in mind that indexes do increase the database disk usage by a noticeable amount

assuming you are using mysql here are the docs on query optimizations https://dev.mysql.com/doc/refman/8.0/en/select-optimization.html I'm sure there is equivalent docs for other engines

eager pecan
#

lead_text_histories has
id, lead_id (fk), created_at, and type

the lead_text_history table has 102,000 rows with 19,500 unique leads with texts.

when I use the unresponded query it ends up with 2,030 rows (takes about 16 seconds to load)

I'll look through those docs and see what else I can improve

image.png
eager pecan
#

I removed the first whereHas('latestText') and that sped it up by about 20% but it still feels slow. Would moving the db to rds speed this up? Right now the app and db is on a t3small ec2 instance

strange parcel
#

what I usually do when I want to have more speed is to convert my whereHas calls to join queries if possible.
For the other stuff - well, there's only so much you can do with that start/end wildcard search

#

also - just try to run an "explain" on the resulting query and see what is using indexes and what is not, check some stuff, etc.

drifting marsh
eager pecan