Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hey!
We have a lot of data in the database and I noticed this query is loading so slow. Could you please help me and convert this into a DB query builder code which is fully optimized?
Reviews::with('website', 'user')->where('report_status', '!=', 3)->where('report_status', '!=', 1)->latest()->take(5)->get(),
I tried with
DB::table('reviews')
->where('report_status', '!=', 3)
->where('report_status', '!=', 1)
->join('websites', 'websites.id', '=', 'reviews.website_id')
->join('users', 'users.id', '=', 'reviews.user_id')
->selectRaw('reviews.*, websites.web_seo_url as website_web_seo_url, users.name as user_name, users.profile_photo_path as user_profile_photo_path')
->orderBy('created_at', 'desc')
->limit(5)
->get()
but it's even more slower
Use EXPLAIN to see how your query uses any available indexes. It might indicate where indexes are needed.
Aside, this
->where('report_status', '!=', 3)
->where('report_status', '!=', 1)
can be replaced with
->whereNotIn('report_status', [1, 3])
@tykus Currently on reviews table, I have the following indexes
$table->index(['website_id', 'review_stars']);
Replacing
->where('report_status', '!=', 3)
->where('report_status', '!=', 1)
with
->whereNotIn('report_status', [1, 3])
lead to a little bit faster load time. So what should I do next?
Reviews::with('website', 'user')->whereNotIn('report_status', [1, 3])->latest()->take(5)->get()
Reviews::with('website', 'user')->whereNotIn('report_status', [1, 3])->latest()->take(5)->explain()->dd()
leads to this
@Laralex not familiar with that output; what is that DB, Postgres?
@tykus Yes, it is Postgres.
@tykus Hey. I just tried to remove ->latest() and now the query needs around 2ms instead of 900ms. This means the order by latest is slowing the query that much. But the problem is that I can't get the latest reviews without ordering the reviews by latest...The main purpose of this query is to get the latest 5 reviews. What can I do?
@Laralex I would suggest adding an INDEX on the created_at column
@tykus What if I also add index on report_status? Is that OK? Because I have many queries that depends on ->where('report_status', '!=', 1) or ->where('report_status', '!=', 3).
Also how can I add this index without running php artisan migrate:fresh? I need to keep the data.. Any pure sql alter table command that I can run?
BTW I'm changing
$table->index(['website_id', 'review_stars']);
to
$table->index(['website_id', 'review_stars', 'report_status', 'created_at']);
@Laralex you can alter the table in a separate migration like this
Schema::table('reviews', function (Blueprint $table) {
$table->index('created_at');
});
Be aware that a compound index ($table->index(['website_id', 'review_stars', 'report_status', 'created_at']); ) might not be at all appropriate for the query optimisation you are attempting!
@tykus Can it cause any problems?
Please or to participate in this conversation.