Laravel eloquent slow loading data

user_15216913 0
Viewed 30

i have a piece of code here

  $shorturls = ShortUrl::withCount("clicks")->where([["url_id", "=", $id->id], ["user_id", "=", Auth::id()]])
        ->orderBy("clicks_count", "desc")
        ->paginate(10);

this query runs in 6000ms (1 million rows of data)

when i comment orderBy it will around 300-500ms

(Shorturl model has many clicks)

i want a way to have a field in my short url named clicks_count to make this query faster.

laravel-9 eloquent mysql php
1 Answers

Since you order by the clicks_count, mysql has to count the clicks for all rows in ShortUrl (1 million) before it can order. Not just the 10 paginated.

You could:

  1. Make sure that the ShortUrl<->clicks relationsship has correct indexes in the db. By looking at the query I would guess the field in the clicks table that should be indexed would be named "url_id".

  2. Even though it is indexed it could still take some time. So another ide could be to denormalize the count, and then for each click, you increment a field in the short_urls table. That way it should not have to count on read.

If that not helps, then please provide your table structure including indexes for both short_urls and clicks tables.

user_17940000
Related