Speed up query to large database using eloquent.

Posted 1 year ago by afoster009

Hello there, i am trying to fetch over 50k records from my DB using eloquent but it just takes forever, and in all honestly 50k records isn't even that big of a deal.

Right now i am trying to use the built in chunk() method but that doesn't seem to help, nor do i really understand what its doing.

A little background on the setup i have here.

I have 2 models.

Formula Model:

class Formula extends Model
{
    //

    protected $table = 'Formula';
    protected $primaryKey = 'FormID';
    protected $hidden = ['SSMA_TimeStamp'];
    public function mix()
    {
        return $this->hasMany('App\Mix', 'FormID');
    }



}

Mix Model:

class Mix extends Model
{
    //
    protected $table = 'Mix';
    protected $primaryKey = 'FormID';
    protected $hidden = ['SSMA_TimeStamp'];
    public function formula(){
        return $this->belongsTo('App\Formula', 'FormID' )->select();
    }
}

Basically i am trying to grab all formulas and their respective mixes through an api call to this route.

Route::get('/formulas', function(){


    App\Formula::with('Mix')->chunk(200, function($formulas){
       foreach ($formulas as $formula){
          return $formula;
       }
    });


});

as it stands i get no JSON in my browser but when i var_dump($formula) i see some data.

So i must be doing something wrong here, is anyone able to point me in the right direction?

PS: Returning this:

return App\Formula::with('Mix')->limit(300)->get();

gets me 300 records of my desired data but when i try to fetch all of them i exceed my max execution time which is why i tried using chunk in the first place.

if more information is needed let me know.

Thanks in advance!

Please sign in or create an account to participate in this conversation.