How to speed up this sql query

Posted 7 months ago by Drfraker

In my application ( which is multitenant) I have a query that can take up to 10 seconds depending on the number of rows in the database for a given office. I'd like to speed this up but I'm not sure how to do it. The table for this query "notes" is quite large and has around 800,000 rows in it. Additionally, the note_text on the notes table can be quite large.

Here's the controller, I believe the code that is slow is in the getFilteredNotes($request) method, and possibly related to getting the count. But I'm not sure. It is difficult to test because in development environment I don't have nearly as much data in the notes table.

My questions:

  1. Is there a clear problem with the query that is making it slow?
  2. Is there a way to measure this query for debugging without replicating production data? (Due to privacy regulation, I really don't want production data on my machine)
  3. Could removing the note_text field have an effect on the time of the query to complete?
<?php

namespace App\Http\Controllers\Api;


class NotesController extends Controller
{
    private $note;

    public function __construct(NoteRepositoryInterface $note)
    {
        $this->note = $note;
    }

    public function index(Request $request)
    {
        $paginationPage = $request->page ?? 1;
        $take = 25;

        list($query, $totalCount) = $this->getFilteredNotes($request);

        $notes = $this->getPaginationData($query, $take, $paginationPage);

        $clientIds = $notes->pluck('client_unique_id')->unique()->reject(function ($id) {
            return ! $id;
        });

        $clients = app()->make(ClientRepositoryInterface::class)->getClients($clientIds);

        $notes->map(function ($note) use ($clients) {
            $note->setRelation('client', $clients->where('api_unique_id', $note->client_unique_id)->first());
        });

        return new LengthAwarePaginator($notes, $totalCount, $take, $paginationPage);
    }
  

    /**
     * Get the notes for the view and filter by search text and user.
     *
     * @param Request $request
     * @return array
     */
    private function getFilteredNotes($request)
    {
        $filter = $request->filter;

        $query = Note::with('staff')
            ->select([
                'id',
                'client_unique_id',
                'user_id',
                'created_at',
                'deleted_at',
                'service_date',
                'note_text',
                ])
            ->when($filter, function ($q) use ($filter) {
                return $q->clientName($filter)
                    ->staffName($filter)
                    ->clientId($filter);
            })
            ->when(! $request->user()->isOwner(), function ($q) use ($request) {
                return $q->ownedByUser($request->user());
            });

        return [$query, $query->count()];
    }

    /**
     * Get the paginated results for the view.
     *
     * @param $query
     * @param $take
     * @param $paginationPage
     * @return mixed
     */
    private function getPaginationData($query, $take, $paginationPage)
    {
        return $query->orderBy('service_date', 'desc')
            ->take($take)
            ->skip(($paginationPage * $take) - $take)
            ->get();
    }
}

Please sign in or create an account to participate in this conversation.