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

trh88's avatar

Using count() with a large set of data

I have a database of around 75,000 records (!).

I would like to create a table where I can list out the records per country. Locally (homestead/vagrant) it works nicely, presumably as it has plenty of processing power.

With a smaller Digital Ocean box I am getting nginx errors (502) and have deduced this is because the query is quite inefficient.

So I am looking for some advice on how to architect this properly!

My view looks like so:

@foreach($countries as $country)
    <tr>
        <td>{{ $country->name }}</td>
        <td class="right">{{ $country->subscribers->count() }}</td>

And (part of) my model (Country) looks like so:

public function subscribers()
{
    return $this->hasMany('App\Subscriber')->where('enabled', '=', '1');
}

How would you recommend doing this? I'm not looking for complete solutions, just some pointers so that I can learn the right way.

Thanks very much

0 likes
7 replies
jlrdw's avatar

Have you tried to paginate results?

ohffs's avatar

Are you eager loading the subscribers? If so it shouldn't be doing many queries. Are you sure the problem is the queries and you're not just running out of memory on the DO box? I think homestead defaults to 1gig of ram for comparison.

trh88's avatar

@StormShadow Interesting, great links - thanks!

I wonder if you can help me, as I'm nearly there.

Following that tutorial I am able to get some correct data out by using subscriberCount() in my model:

public function subscriberCount()
{
    return $this->subscribers()
    ->selectRaw('country_id, count(*) as aggregate')
    ->groupBy('country_id');
}

This outputs a collection to the frontend:

[{"country_id":5,"aggregate":2996}]

I'm not too confident with Accessors, but I think this is what I'd need to format that response to just show the aggregate value?

pmall's avatar

It is slow because you use count() on the collection. Eloquent has to build collections which sums up at 75 000 eloquent models. For a quick fix use count on the query : $country->subscribers()->count(). It will execute a query per country. If you want to eager load the count follow the softonsofa tutorial.

1 like
StormShadow's avatar

@trh88 @pmall is 100% correct. If you want to continue with the softonsofa tutorial you would add this method

public function getSubscriberCountAttribute()
{
  // if relation is not loaded already, let's do it first
  if ( ! array_key_exists('subscriberCount', $this->relations)) 
        $this->load('subscriberCount');

       $related = $this->getRelation('subscriberCount');

        // then return the count directly
        return ($related) ? (int) $related->aggregate : 0;
     }

With this code in place you could then use this accessor to get the aggregate field.

 $country->subscriberCount;

Make sure to eager load the relation as well. Which for your code would be something like

$countries = Country::with('subscriberCount')->get();
jimmck's avatar

Eloquent does much that should be done at the database level. If you are using the same database, do not use eager loading. It simulates joins and is not needed. Join in the database and as stated use the count aggregate function. Everytime you write code to do what the database engine will do you for you; you are wasting speed and effort. And adding potential logic bugs to boot.

Please or to participate in this conversation.