laravel-eloquent-vs-collection-count.md

Laravel Eloquent vs Collection Count

This can be a little confusing as the syntax barely changes but it makes a big difference.

Using Collection "count"

This seems harmless, but I'll explain why it can be bad for performance

$post->comments->count();

In the background the first thing this does is select all the comments for the given post

// $post->comments
select * from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null

Then it hydrates all the Comment models and puts them into a collection. From there it's a simple count() on the items array within the collection.

Using Eloquent "count"

Using the Eloquent (or Query Builder) method runs the count as a SQL statement

// $post->comments()->count();
select count(*) as aggregate from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null

That's it, no hydrating models, no collection to fill, no array count.

With a small amount of data you probably won't see a big difference in performance, but it will become more noticeable as your table grows.

Stats

As an example, I setup a post model which has 100 000 comments against it. Using MySQL as my database and installing barryvdh/laravel-debugbar to see how long queries take, here are the result comparisons.

Using the collection count method

Route::get('/test-collection', function () {
    return App\Post::where('title', 'More Post')->first()->comments->count();
});

317.24ms | select * from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null
312.82ms | select * from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null
310.98ms | select * from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null
312.49ms | select * from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null
317.32ms | select * from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null

Using the eloquent count method

Route::get('/test-eloquent', function () {
    return App\Post::where('title', 'More Post')->first()->comments()->count();
});

15.81ms | select count(*) as aggregate from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null
16.74ms | select count(*) as aggregate from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null
18.12ms | select count(*) as aggregate from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null
16.49ms | select count(*) as aggregate from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null
19.58ms | select count(*) as aggregate from `comments` where `comments`.`post_id` = '1' and `comments`.`post_id` is not null

As you can see the difference is considerable even just at 100 000 records, almost 300ms!

Conclusion

If I haven't convinced you by now, I'm not sure what will! For the sake of your users use the eloquent method! :D

Demo Application

• https://github.com/patoui/laravel-eloquent-vs-collection-count