Level 122
You need to review indexes.
In your posts table make sure user_id and tag are indexed.
Dec 5, 2019
8
Level 1
WhereHas performance issue in a big database
Hi guys, I have a significant performance issue in a simple scope. The scope uses WhereHas for a table that contains 80k records.
Let's imagine a forum has 1.000 users and 100.000 posts. I want to display all users that have posts with a 'Foo' tag.
public function scopeHasPostsWithTag(Builder $query, $tag)
{
return $query->whereHas('posts', function ($query) use ($tag) {
$query->where('tag', $tag);
})->get();
}
This query executes minutes. Sometimes it results in a timeout error. I assume it reads all 100k records for each user.
Is it possible to speed up this query?
I thought about caching the whole query, but the data always changes. I have to clear the cache after every new post.
It would be perfect to use a cached function like hasPostsWithTags($tag) for each user individually and after that build scopes based on this function. But I believe it is not possible...
How to deal with it?
Level 75
->get();
Well for another thing, how about ->paginate();
Level 122
Yes, you should not have that get() statement in a scope (and thats got nothing to do with pagination)
Level 75
@snapey okay just curious, are you saying don't paginate, just get all 80,000 results at one time.
I was only suggesting to, yes fix the indexing and to paginate like most database results are done. I sincerely hope you did not think I was going against your index answer. Your replies can be hard to understand at times, so sorry if I did not totally understand the reply.
I had put "for another thing". I promise I was not telling OP to ignore your answer.
Level 1
@snapey @jlrdw You both saved my day!
Not sure why I included the "get()" statement (actually it paginated my results properly working as an API with a spatie query builder), but removing get() and the most important adding indexes heled a lot!
Actually, I did not use indexes before in my small projects, and I think this was a huge mistake.
Just added $table->index('tag'); to the posts table, and it worked fine.
Level 75
Level 1
@jlrdw You both helped me and pointed out my mistakes. Not sure what was the main problem here: indexes or get(), the most important is that the issue is solved.
And moreover, I do not want to offend anyone of you by choosing the "best answer". Not to offend you guys, I will choose my comment as the best, hah :)
Level 75
That's fine also, and yes I was only trying to help.
Please or to participate in this conversation.