Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

AR's avatar
Level 7

Very slow search/sort query

Hi, I have a search page that gives you articles sorted by the likes they get. But the page loads very slow 10 seconds! I know that the problem is with number of likes. In my view I show every post with number of likes they have. When I remove that and also remove The part where I include 'likes' the speed is OK. Is there anyway I can make it faster?

Here is the search method in my controller:

class ArticlesController extends Controller
{
    public function index()
    {
        $categories = Category::with('subcategories')->get();

        $subcategories = $categories->pluck('subcategories')->flatten();

        $countries = Country::getAll();

        $articles = (new Article)->newQuery();

        $requestValues = [
            'sortBy' => 're', //recent
            'q' => '', // search query
            'country' => 1, // country id
            'cat' => '', // category
            'sub' => '' // subcategory
        ];

        $sorts = collect([
            [
                'name' => __('labels.recent'),
                'code' => 're' // recent
            ],
            [
                'name' => __('labels.liked'),
                'code' => 'li' // likes
            ],
        ]);

        if (request()->has('sort') && $sorts->where('code', request('sort'))->first()) {
            $requestValues['sortBy'] = request('sort');
        }
        if ($requestValues['sortBy'] === 'li') {
            $articles->withCount('likes')->orderBy('likes_count', 'desc'); // this is also making it slow
        } else {
            $articles->orderBy('created_at', 'desc');
        }

        if (request()->has('country') && $countries->where('id', request('country'))->first()) {
            $requestValues['country'] = request('country');
        }

        $articles->with(['blog', 'photos', 'likes']) // 'likes' makes it slow
            ->whereHas('blog', function ($query) use ($requestValues) {
                $query->where('status', BlogStatus::APPROVED)
                    ->where('country_id', $requestValues['country']);
            })
            ->where(function ($query) {
                $query->where('status', ArticleStatus::APPROVED)
                ->orWhere('status', ArticleStatus::PENDING);
            });

        if (request()->has('q') && !empty(request('q'))) {
            $requestValues['q'] = request('q');
            $articles->where(function ($query) use ($requestValues) {
                $query->where('title_en', 'LIKE', '%' . $requestValues['q'] . '%')
                    ->orWhere('title_ar', 'LIKE', '%' . $requestValues['q'] . '%')
                    ->orWhere('body_en', 'LIKE', '%' . $requestValues['q'] . '%')
                    ->orWhere('body_ar', 'LIKE', '%' . $requestValues['q'] . '%');
            });
        }

        if (request()->has('sub') && !empty(request('sub'))) {
            if ($requestValues['sub'] = $subcategories->where('id', request('sub'))->first()) {
                $articles->where('subcategory_id', $requestValues['sub']->id);

                $requestValues['sub'] = $requestValues['sub']['id'];
            }
        } else {
            if (request()->has('cat') && !empty(request('cat'))) {
                if ($requestValues['cat'] = $categories->where('id', request('cat'))->first()) {
                    $subs = $requestValues['cat']->subcategories->pluck('id')->toArray();

                    $articles->whereIn('subcategory_id', $subs);

                    $requestValues['cat'] = $requestValues['cat']['id'];
                }
            }
        }

        $articles = $articles->simplePaginate(10);

        return view('articles.index', compact('articles', 'categories', 'countries', 'subcategories', 'requestValues', 'sorts'));
    }
}

Here is the article model

class Article extends Model
{
    public function blog()
    {
        return $this->belongsTo(Blog::class);
    }

    public function photos()
    {
        return $this->hasMany(ArticlePhoto::class);
    }

    public function subcategory()
    {
        return $this->belongsTo(Subcategory::class);
    }

    public function likes()
    {
        return $this->morphMany(Like::class, 'liked');
    }

}

here is the blog model

class Blog extends Model
{
    public function articles()
    {
        return $this->hasMany(Article::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function country()
    {
        return $this->belongsTo(Country::class);
    }
}
0 likes
4 replies
lostdreamer_nl's avatar
Level 53

it seems like you have a lot of likes in your system, each of which is being queried, put into an object etc.

Maybe it's better to refactor the app in a way that, when you have liked something, the like is also added to / cached in the article's row?

So an event listener on the like model, that updates the field articles.like_count (int)

Now you don't have to get all likes to sort by them, or to show the total count which will save you a lot of queries.

It could also be that you have forgotten to put an index on likes.liked_id / likes.liked_type

2 likes
anon34372's avatar

@AR

I will kinda repeat what @lostdreamer_nl suggested

  1. Did you make sure proper database indexes are in place?

  2. You probably should create a column named likes_count on Article and update it whenever someone likes or unlikes it. This is a very common practice.

// Like
$article->likes_count = $article->likesCount + 1;

// Unlike 
$article->likes_count = $article->likesCount - 1;
1 like
AR's avatar
Level 7

@lostdreamer_nl @JesseVeritas Thanks! So I add the column and also trigger a event ArticleLiked and then a listener UpdateLikesCount to do the update on the column articles.likes_count?

Robstar's avatar

If you're counting the likes each time, your app will become slower as time goes on.

Use "counter caching", something Jeffery does in The TDD forum series. In your articles table add a column called likes_count. When there is a new like increment this value. This is value you'd use within your filtering.

See: https://github.com/JeffreyWay/council/blob/master/database/migrations/2017_03_15_175959_create_threads_table.php#L21

https://github.com/JeffreyWay/council/blob/master/app/Reply.php#L40-L44

Personally, when there is a new "like" I'd dispatch a queued job to increment this value. However, you can do this directly within the model, as demonstrated in the links above.

1 like

Please or to participate in this conversation.