Cant retrieve record from large scale table row

Question

I am using laravel framework.In my database records table have more then 400000 row. Now I want to retrieve record using some condition logic : need to match service id need to match status

but my application cant retrieve data (unable to handle this request). I am using query using this

foreach ($ven as $ven){
    $data = $ven->data;
    $record = $data ->records()->where('status','success')->get();
}

My status column already added in index.

Need suggestion

Its not completely clear what you are asking. Can you write a list of the things, with column names, that you are trying to match — RiggsFolly, Jul 03 '18 at 15:40
`foreach ($ven as $ven){` ? or `foreach ($vens as $ven){` ? where is the service id — kenken9999, Jul 03 '18 at 15:41

score 0 · Answer 1 · answered Jul 04 '18 at 05:56

First you need to keep statuses in int form not as strings. It would help you in filtering records easily.

And use chunk for handling large datasets. I suppose $ven is for venue. So, follow the code below.

DB::table('venues')
->select('venues.*', 'records.*')
            ->join('records', 'venues.id', '=', 'records.venue_id')
             ->orderBy('id')->chunk(1000, function($venues) {
    foreach ($venues as $venue) {
        // your logic
    }
});

Note: I used query builder instead eloquent. That is because query builder is faster than eloquent.

score 0 · Answer 2 · answered Jul 04 '18 at 06:50

The way you are trying to access related records in loop could create N + 1 problem

As per official docs When accessing Eloquent relationships as properties, the relationship data is "lazy loaded". This means the relationship data is not actually loaded until you first access the property. However, Eloquent can "eager load" relationships at the time you query the parent model. Eager loading alleviates the N + 1 query problem

You could eager load your related data as

$results = ParentModel::with('records')
                        ->where(...) /* If there are any filters for ParentModel */
                        ->get();

For eager loading only 2 queries will be executed to get your data for main model and related model. Each row in $results will a collection of related records which you can iterate to list details of each related object.

To get filtered rows of related model you could modify with() method as

$results = ParentModel::with(['records' => function ($query) {
                            $query->where('status','=','success');
                        }])
                        ->where(...) /* If there are any filters for ParentModel */
                        ->get();

To get filtered rows of ParentModel on basis of related records you could use whereHas

$results = ParentModel::with('records')
                        ->where(...) /* If there are any filters for ParentModel */
                        ->whereHas('records', function ($query) {
                            $query->where('status','=','success');
                        })->get();

Cant retrieve record from large scale table row

2 Answers2