Is there any way to optimize the query while working with 2mil+ rows database table

I am working on Laravel query that shall count the latest month data and also group by week, for the last 3 months. I have tried to fix that in a few ways but still it’s exceeds my memory limit and load very slow. Bellow is the current code that I am using to get the final results – but same problem is also here.

Any idea how to optimize the counting and grouping the data?

$data['pois']['total'] = PoiLocation::whereYear('created_at', Carbon::now()->year)
    ->whereMonth('created_at', Carbon::now()->month)
    ->count();

$pois = PoiLocation::where('created_at', '>', (new Carbon)->subMonths(3))
    ->get()
    ->sortBy('created_at')
    ->groupBy(function ($collection) {
        return Carbon::parse($collection->created_at)->isoWeek();
    });

if ($pois->count()) {
    foreach ($pois as $item => $value) {
        $data['pois']['weeks'][$item] = $value->count();
    }
} else {
    $data['pois']['weeks'] = [];
}

if ($data['pois']['weeks']) {
    $data['pois']['high'] = max($data['pois']['weeks']);
} else {
    $data['pots']['high'] = 1;
}

protected $fillable = [
    'store_id', 'name', 'address1', 'address2', 'city','state','zip_code', 'dma_desc', 'country', 'lat' ,'lon', 'target', 'is_verified', 'polygons', 'external_id', 'brandID', 'companyID'
];

protected $dates = ['created_at', 'updated_at'];

public $timestamps = true;

Answer

You could use LazyCollections. This should heavily reduce your memory usage.

$pois = PoiLocation::query()
    ->where('created_at', '>', Carbon::now()->subMonths(3))
    ->orderBy('created_at') // sort in DB instead of wasting more memory doing the sorting.
    ->cursor() // don't load every model in memory
    ->remember() // don't repeat the query (if this line wasn't here, the query would be made 3 times. 1- $poi->all(), 2- ($poi->max() !== null) 3- $poi->max() )
    ->groupBy(function (Poilocation $poiLocation) { // param here is not a collection, it's a Poilocation. Type hint is completely optional
        return $poiLocation->created_at->isoWeek(); // $poiLocation->created_at should already be a Carbon instance because of Eloquent magic.
    })
    ->map->count();

$data['pois']['weeks'] = $poi->all();
$data['pois']['high'] = ($poi->max() !== null) ? $poi->max() : 1;

This also simplifies your logic.

• $poi->all() will either return an array, empty or not, with the keys.
• $poi->max() will return the max() of the collection. If the collection is empty, it will return null. A simple ternary operator takes care of that part of your logic as well.