CODEWITHSUNDEEP
phpmysqleloquentlaravel-9
user15216913
user15216913

Reputation:

Laravel eloquent slow loading data

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.

Upvotes: 1

Views: 290

Answers (1)

thephper
thephper

Reputation: 2610

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.

Upvotes: 0

Related Questions