Laravel Eloquent Relationship Performance

5

I realize that in Laravel 4 Framework , when we have a relationship, instead of using JOIN of Mysql in the source code of Eloquent , it uses a select.

For example. I have these two Models.

class Usuario extends Eloquent{

   public function nivel()
   {
       return $this->belongsTo('Nivel');
   }
}

class Nivel extends Eloquent
{

}

When I make the following query:

$usuarios = Usuario::with('nivel')->get();

foreach($usuarios as $usuario) {

   echo $usuario->nivel->nome;
}

And I use DB::getQueryLog() , instead of displaying it:

SELECT * FROM usuarios JOIN niveis ON niveis.id = usuarios.nivel_id

It does this

SELECT * FROM usuarios
SELECT * FROM niveis WHERE id IN(5, 7, 8, 9, 10)

And that's because I used with('nivel') . For if I used to do this:

$usuarios = Usuario::all();

foreach($usuarios as $usuario){

   echo $usuario->nivel->nome;
}

The return of DB::getQueryLog() would be this, if there are 3 users:

SELECT * FROM usuarios
SELECT * FROM niveis WHERE id = 1
SELECT * FROM niveis WHERE id = 1
SELECT * FROM niveis WHERE id = 3

That is, when I do not specify with , if I have 100 users in the result, it will make 100 selects to relate to the table levels

I really like using Laravel 4 because of its great productivity and organization.

But I wonder if this "separate select" it does could lead to performance loss.

    
asked by anonymous 29.01.2015 / 16:00

1 answer

4

This feature you're talking about is called eager loading . It checks the links between the tables and decreases the number of queries to increase the performance of your application.

At first, it is easier to analyze your problem by considering the amount of queries performed: the number dropped from 1 + n to only 1 or 2 queries , in case there are results for the first query . In this case it is quite likely that eager loading has helped your application in terms of performance.

When Laravel makes two queries instead of just doing one with JOIN , I think the reason is that he does not know the structure of his database. Probably the technical decision of Eloquent to make two queries is that, in the second query, it does it using the primary key of the table which, by feature, is already indexed and certainly brings good results.

On the other hand, queries with JOIN s, in many cases, are usually expensive and in this case the developer should write the queries in the application, instead of trusting Eloquent to do so.

    
29.01.2015 / 16:18