I have below a query build with eloquent on laravel, it is very slow (50 points it take to show around 10 second), i can't understand if it a problem of the query construct or some missing index on table, some advise? thanks
Points model (around 5000 records on the table)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use OwenIt\Auditing\Contracts\Auditable;
class Points extends Model implements Auditable
{
use \OwenIt\Auditing\Auditable;
use SoftDeletes;
/**
* Indicates if the model should be timestamped.
*
* @var bool
*/
public $timestamps = false;
/**
* The database table used by the model.
*
* @var string
*/
protected $table = 'points';
/**
* The database primary key value.
*
* @var string
*/
protected $primaryKey = 'id';
/**
* Attributes that should be mass-assignable.
*
* @var array
*/
protected $fillable = [
'schedule_id',
'customer_id',
'cart_zone_id',
'indirizzo',
'latitudine',
'longitudine',
'tipo',
'enabled'
];
/**
* The attributes that should be mutated to dates.
*
* @var array
*/
protected $dates = [];
/**
* The attributes that should be cast to native types.
*
* @var array
*/
protected $casts = [];
/**
* Get the Schedule for this model.
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function Schedule()
{
return $this->belongsTo('App\Models\Schedules', 'schedule_id', 'id')->withTrashed();
}
/**
* Get the Customer for this model.
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function Customer()
{
return $this->belongsTo('App\Models\Customers', 'customer_id', 'id')->withTrashed();
}
/**
* Get the CartZone for this model.
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function CartZone()
{
return $this->belongsTo('App\Models\CartZones', 'cart_zone_id', 'id')->withTrashed();
}
}
Events model (around 400k records on the table)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Carbon;
class RecordEvents extends Model
{
/**
* Indicates if the model should be timestamped.
*
* @var bool
*/
public $timestamps = false;
/**
* The database table used by the model.
*
* @var string
*/
protected $table = 'events';
/**
* The database primary key value.
*
* @var string
*/
protected $primaryKey = 'id';
/**
* Attributes that should be mass-assignable.
*
* @var array
*/
protected $fillable = [
'code',
'description',
'device_id',
'schedule_id',
'boa_id',
'parent_id',
'user_id',
'skip_unique'
];
/**
* The attributes that should be mutated to dates.
*
* @var array
*/
protected $dates = [];
/**
* The attributes that should be cast to native types.
*
* @var array
*/
protected $casts = [];
/**
* Get the device for this model.
*
* @return App\Models\Device
*/
public function device()
{
return $this->belongsTo('App\Models\Devices', 'device_id')->withTrashed();
}
/**
* Get the schedule for this model.
*
* @return App\Models\Schedule
*/
public function schedule()
{
return $this->belongsTo('App\Models\Schedules', 'schedule_id')->withTrashed();
}
/**
* Get the boa for this model.
*
* @return App\Models\Boa
*/
public function boa()
{
return $this->belongsTo('App\Models\Points', 'boa_id')->withTrashed();
}
/**
* Get the boa for this model.
*
* @return App\Models\Boa
*/
public function pvd()
{
return $this->belongsTo('App\Models\CustomersPvds', 'pvd_id')->withTrashed();
}
/**
* Get the boa for this model.
*
* @return App\Models\Boa
*/
public function cartzone()
{
return $this->belongsTo('App\Models\CartZones', 'cartzone_id')->withTrashed();
}
/**
* Get the parent for this model.
*
* @return App\Models\Parent
*/
public function parent()
{
return $this->belongsTo('App\Models\Parent', 'parent_id')->withTrashed();
}
/**
* Get the user for this model.
*
* @return App\Models\User
*/
public function user()
{
return $this->belongsTo('App\Models\Users', 'user_id')->withTrashed();
}
/**
* Get created_at in array format
*
* @param string $value
* @return array
*/
public function getCreatedAtAttribute($value)
{
return \DateTime::createFromFormat($this->getDateFormat(), $value)->format('d/m/Y H:i:s');
}
/**
* Get data in array format
*
* @param string $value
* @return string
*/
public function getDataRawAttribute($value)
{
return Carbon::createFromFormat('d/m/Y H:is', $this->created_at)->format('Y-m-d H:i:s');
}
}
Query
$pointsCartZone = Points::where('cart_zone_id', $trip->cart_zone_id)
->where('enabled', 1)
->select('*', DB::raw('(select COUNT(*) as ch from events where boa_id = points.id and schedule_id = ' . $id . ') as passed'))
->get();
