CamKem's avatar
Level 10

SQL vs Response time Optimisation Question

I have been going through testing optimisation of my queries before deployment of my app.

I have refactored a threads query for the forum index page to only grab the data that is needed to pass to the frontend.

Before a refactor, the SQL queries were averaging around 35ms and time to response around 670ms. After the refactor, the SQL query times are averaging 430ms & the time to response is averaging 590ms. So by effectively only selecting the data needed for the response to be returned, this increasing the load on the SQL server times 12 for a 13% reduction on response times.

My main question would be, is it worth placing 12 times the load on the SQL server for a 13% improvement in response times? I am likely to go with yes, but I want to know your opinions.

Secondly, I want to know if there is perhaps a more "eloquent" way in terms of SQL performance of getting just the results I need, this is my current optimised query:

PS: yes I know I could inline it & use through() instead of transform(), I am more concerned with SQL performance at this point, I split it out to debug it.

        $paginated = Thread::query()
            ->with('creator:id,username,email')
            ->with('topics:id,title,slug,color,description,icon')
            ->filter($filters)
            ->leftJoin(DB::raw('(SELECT thread_id, MAX(created_at) AS last_reply_created_at FROM replies GROUP BY thread_id) AS last_replies'), function ($join) {
                $join->on('threads.id', '=', 'last_replies.thread_id');
            })
            ->leftJoin('replies', function ($join) {
                $join->on('threads.id', '=', 'replies.thread_id')
                    ->on('replies.created_at', '=', 'last_replies.last_reply_created_at');
            })
            ->with(['replies' => function ($query) {
                $query->select('replies.id', 'replies.thread_id', 'replies.created_at', 'replies.user_id')
                    ->with('owner:id,username,email,name')
                    ->whereRaw('replies.id IN (SELECT MAX(id) FROM replies GROUP BY thread_id)');
            }])
            ->groupBy('threads.id')
            ->paginate(ForumHelpers::PAGINATION_LIMIT)
            ->withQueryString();

        $paginated->getCollection()->transform(function ($thread) {
            return [
                'title' => $thread->title,
                'body' => $thread->body,
                'slug' => $thread->slug,
                'views' => $thread->views,
                'solved' => $thread->solved,
                'created_at' => $thread->created_at->diffForHumans(),
                'updated_at' => $thread->updated_at->diffForHumans(),
                'creator' => [
                    'username' => $thread->creator->username,
                    'email' => $thread->creator->email,
                ],
                'topic' => [
                    'title' => $thread->topics->first()->title,
                    'slug' => $thread->topics->first()->slug,
                    'color' => $thread->topics->first()->color,
                    'description' => $thread->topics->first()->description,
                    'icon' => $thread->topics->first()->icon,
                ],
                'replies' => $thread->replies_count ? [
                    'count' => $thread->replies_count,
                    'first' => [
                        'created_at' => $thread->replies->last()->created_at->diffForHumans(),
                        'owner' => [
                            'username' => $thread->replies->last()->owner->username,
                        ]
                    ],
                ] : null,
            ];
        });

        return $paginated;

Would it help if I moved to using an Eloquent resource instead of a Query class?

0 likes
5 replies
LaryAI's avatar
Level 58

It's difficult to say whether it's worth placing 12 times the load on the SQL server for a 13% improvement in response times without knowing more about the specific use case and the expected traffic to the application. However, it's worth considering other options for optimizing the query before resorting to selecting only the data needed for the response.

One option to consider is indexing the database tables used in the query. This can significantly improve query performance by allowing the database to quickly find the relevant data. Another option is to use caching to store the results of the query and serve them from cache instead of querying the database every time.

As for the second question, using an Eloquent resource instead of a Query class may not necessarily improve SQL performance, but it can make it easier to format the data returned by the query and reduce the amount of code needed to transform the data into the desired format. It's worth considering if the current transformation logic becomes more complex or if the same transformation logic is used in multiple places in the application.

Snapey's avatar

You should examine the number of queries. Its easy to miss something and have an n+1 issue

Also, with the query, you can run EXPLAIN to see if you are doing a join using a non indexed column

CamKem's avatar
Level 10

@Snapey I ran EXPLAIN on the query, this was the result in TablePlus. I'm not really sure what I am looking for in regards to checking for non-indexed join? Can you see anything? I mean 731ms seems like a long time.... Query Screenshot

Please or to participate in this conversation.