Ghost Worker Ghost Worker - 13 days ago 5
MySQL Question

Performance Wise: which is better in Laravel Database

I have been using laravel for a while now and I was just curious about which is more better for performance. Taking this scenario, I have two tables a department table and a unit table with the following structure

DEPARTMENT TABLE

Field Type Null Key
---------- ---------------- ------- ------
id int(10) unsigned NO PRIMARY
name varchar(255) NO


UNIT TABLE

Field Type Null Key
------------- ---------------- ------ ------
id int(10) unsigned (NULL) PRIMARY
unit varchar(255) (NULL)
department_id int(10) unsigned (NULL) FOREIGN
created_at timestamp (NULL)
updated_at timestamp (NULL)


In the
Department
model I have
Unit
with
hasMany
and in
unit
model I have department with
belongsTo


Now my question is if I want to get all units in a department which approach is best between approach one and approach two

APPROACH ONE

$department = Department::find($id);
$units = $department->unit;


APPROACH TWO

$units = DB::table('units AS a')
->select(array('a.id AS id','department_id', 'unit'))
->join('departments AS b', 'a.department_id', '=', 'b.id')
->where('b.id', '=', $id)
->get();


which approach is faster and better.

Thanks.

Answer

APPROACH ONE by far.

If really exists a difference in which is faster, we will be talking about micro-optimization, and for these cases we will go for the cleaner and most understandable way, so the first one.

Also I would like to recommend other improvements:

One department has many units, so the relationship name should be units, not unit.

$units = $department->units;

Also use implicit binding when you can.

public function yourMethod (Department $department)
{
    $units = $department->units;
    return view('your-view', compact('units'));
}

UPDATE

I reproduced an example on my project with Laravel Debugbar, these are the results for 5 executions with each query:

APPROACH ONE:

  • 320μs
  • 310μs
  • 300μs
  • 320μs
  • 320μs

~314μs

APPROACH TWO:

  • 310μs
  • 330μs
  • 360μs
  • 300μs
  • 310μs

~322μs

As you can see, there is no difference, or at least it is insignificant. So, having the same performance, you should go for the first approach because is better quality code.