Laravel Performance
This episode is for members only

Sign up to access "Laravel Performance" right now.

Get started
Already a member? Sign in to continue
Playing
10. Fetching counts efficiently

Episodes

0%
Your progress
  • Total: 1h 9m
  • Played: 0m
  • Remaining: 1h 9m
Join or sign in to track your progress

Transcript

00:00
So we're heading over to a completely different page
00:02
with the starter project now. And this is the author's page. This lists all of the authors that we have in the database, or the users that we have in the database.
00:12
And it shows the amount of posts that they have. And we know that's the case because we used our seeder to generate them. But why would this page be slow?
00:20
Let's take a look at the debug bar and just see what's going on here. So we've got the query count here, which is just two. We're grabbing all users.
00:28
Again, we could limit the select on this, but it's not gonna make too much difference. Then we're selecting all of the posts from the users because we're counting the amount of posts that we have.
00:39
We have this eager loaded, which is absolutely fine. So everything looks good. But if you take a look at the memory usage here, you can see this is 167 megabytes
00:49
with a 500 millisecond-ish page load time. What is going on here and why is this so slow? Well, let's head over to the controller just here. You can see we're eager loading the posts in,
01:02
which we've already looked at. So that's not the issue. The query count is low. Let's head over to the author's index blade file
01:10
and take a look at this as well. So we're iterating through the authors. We don't have too many. We have about five and we're outputting the author name.
01:18
And from that relationship posts, which is a Laravel collection, we're using the count method. We're actually doing this twice
01:26
because we're using the plural functionality of Laravel string helper to output either post if the user has one post or posts if the user has multiple posts.
01:37
So the question is, why is this so slow? Well, what we're actually doing here is we have a huge amount of items within the posts collection and we're using that twice here.
01:49
What we're then doing is using the count method on a collection directly within PHP. Now this isn't very fast because what this does is it will have to count all of the items
02:00
that exist within this posts collection. And that is actually pretty slow. Now, if we get rid of the pluralized version of this, for example, and we just say posts,
02:10
if we didn't care about the pluralization, let's take a look at what happens to the memory usage and the request duration. So you can see that it's working
02:18
in exactly the same way as well. So it's not that we're doing that twice and that's causing the issue. The whole issue is that we're counting
02:26
on this posts collection. Now, this kind of thing is incredibly common where we're showing counts for particular things. So this is a really important tip.
02:35
And to solve this, we use database aggregates or counts. So let's go and refactor this to make this better. Now, if we head over to the controller again and we just do a die dump on authors
02:51
and we grab the first author from this, this gives us a good indication of what data we're working with here. We have all of this author information,
02:59
which we don't necessarily need. So while we're here, what we'll do is just do a select on only the information we need. So we just need the author's ID and the author's name here.
03:10
That's gone ahead and reduced the amount of data we get back from here, which is great. Now let's go ahead and again, put in our query method in here
03:18
so we can pull these down neatly onto individual lines so we can see what we're doing. And what we're now gonna do is grab the count at the database level.
03:26
So if we get rid of this die dump and give this a refresh, you can see that for each of the users that we're grabbing in here, it doesn't really contain any kind of counts.
03:36
That is the goal here, to do that at the database level and then just get an integer back that we can display on our template rather than having to do a count within our template.
03:47
So to do this, what we do is say with count in here on our query and we choose the relationship name in here, which is really important. So in this case, it's just posts.
04:00
So now if we bring back and die dump on the first author, what we actually get here is an additional column. So or an additional attribute on this model, which is posts count.
04:12
And of course you can see that is now just an integer value. Now, just a little tip here, if we wanted to find out how this query is constructed, we can actually just say to SQL
04:22
and then die dump the result of this. And if we just go down here and die dump on the authors, what we actually get now is a query string here that we can then use to paste into our database client.
04:35
If we want to, we can look at exactly what is happening here. So the query here is selecting everything from the post where the user ID equals the user ID from the post table.
04:46
And we're grabbing that sub query here as post count. So it's counting that within the same query. Of course, we can also see this in our debug bar, but if you did need to see that in code anywhere,
04:56
you can do that. So we're going to go ahead and say, get in here again. We're going to get rid of authors first. And of course, if we refresh the page,
05:03
we're going to get the same memory usage and request duration issue, because we are not going ahead and actually utilizing that new attribute that's been added.
05:13
So now what we can do is we can just switch this out. So for each of the authors, we now have a post count attribute on there. We can just change this up.
05:21
So there's no iterating over or counting anything inside of that Laravel collection. Now, if we refresh the page here, you can see that the memory usage and request duration
05:32
hasn't actually gone down. If we just go ahead and get rid of the egoload now, that's really crucial, because now we don't need to egoload in the posts
05:40
to be able to grab the count from the collection that's returned from that egoload on that relationship. So if we give this a refresh now, you can see that's down to two megabytes
05:51
and about 50 milliseconds. So we've got rid of the need to egoload because we don't need to count on that related model. And we've introduced a with count in here,
06:02
which gives us that attribute that we saw earlier. Now, just a couple of tips. You can rename these. So if we were to just go ahead
06:10
and die dump on authors and first again, and we give that a refresh, sure enough, we see posts underscore count. That's the default.
06:18
Laravel will take the relationship name and it will append on underscore count. What you can do is at the database level, say as, and you can choose any name you want for this.
06:28
So you could say author post count if you needed to, for example. And you can see that if we refresh this and look at this, you can see the attribute name has changed it.
06:38
Usually I keep these as defaults unless it's going to interfere with anything or unless I have a really good reason to change this around.
06:44
But you know that you've got the option to do that now. So now we have a 160 megabyte page down to two megabytes. And we also have the request duration down to about 50 milliseconds, which is perfect.

Episode summary

In this episode, we switch over to the authors page of our app, where we list all the users along with how many posts they've made. Right off the bat, everything seems fine: we're eager loading posts, query count is low, and there aren't that many authors. But when we check the debug bar, we notice the page is super slow and eating up way too much memory. What's up with that?

We dig into the Blade template and notice we're calling the count method twice on each author's posts collection. Even with eager loading, this still means every single post is loaded into memory, just so PHP can count them. If an author has a lot of posts, that really adds up and kills performance — even more so if you're calling count multiple times.

The fix? Instead of loading all posts and counting in PHP, we use Eloquent's withCount to grab the post count directly from the database as an extra attribute. That way, we just get a simple integer for each author, no need to touch the massive related collections.

Once that's in place, we can ditch the old eager load, update the template to use the new posts_count attribute, and — wow — our memory usage plummets from 160MB to 2MB, and the page loads way faster. As a bonus, we show how you can even customize the name of the count attribute if you want.

This lesson is all about why you should let your database do the heavy lifting for simple aggregates, and shows you exactly how to wire up withCount for these super-common scenarios.

Episode discussion

No comments, yet. Be the first!