Optimizing Queries - Eager loading takes too much memory

Question

I have following tables:

• User (about 1.000 rows)
• Fans (about 100.000 rows) - a User has many fans, a Fan belongs to one User
• Likes (about 100.000 rows - a User has many likes, a Like belongs to one User

Now I want to have a table with

• User name
• Number of fans
• Number of likes

It is a big table and I use jQuery DataTable to have it with pagnition but this here is about the server side.

I have set up all my relations and out put the table:

$users = User::get();

foreach ($users as $user) {
echo $user->name;
echo $user->fans()->count();
echo $user->likes()->count();
}

This is a lot of queries and I want to optimize. My first try was using Eager Loading.

$users = User::with('fans', 'likes')->get();

But this seems to be very havy for the server memory. On my local environment I have 16 MB memoriy size for php scripts. And it always tries to allocate more.

Is this normal? Is the best solution to simply give the scripts more memory? How else could I optimize my queries?

Answer 1

If you just want to count number of records there's no point to get all records just to count them, Now you get 201000 rows. You should add :

public function fansCountRelation()
{
    return $this->hasOne('Fan')->selectRaw('user_id, count(*) as count')
        ->groupBy('user_id');
}


public function likesCountRelation()
{
    return $this->hasOne('Like')->selectRaw('user_id, count(*) as count')
        ->groupBy('user_id');
}

public function getLikesCountAttribute()
{

    return $this->likesCountRelation ?
        $this->likesCountRelation->count : 0;
}

public function getFansCountAttribute()
{

    return $this->fansCountRelation ?
        $this->fansCountRelation->count : 0;
}

to your User model and now you will get only 1000 user rows + aggregate data for fans and likes.

And now you can use:

$users = User::with('fansCountRelation', 'likesCountRelation')->get();
foreach ($users as $user) {
    echo $user->name;
    echo $user->likes_count;
    echo $user->fans_count;
}

However you should rethink if you really need it. 1000 rows for normal usage is too much. If you use it for export purposes that's fine, but if you only display it in admin panel for example, you should use paginating for that.

And in case it takes you much time, you should make sure you have indexes for your fans and likes tables for user_id column. It should speed up your queries a lot.