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?