mercredi 13 mars 2019

Laravel Optimise sum query

I would like to get sum go deposit, withdraw and net count. Currently, I have ~10,335,633 rows data(~1.8GB), it takes more than 7 seconds to calculate the sum. How should I improve my query to make it faster?

Query:

        $depositQuery = Deposit::whereBetween('created_at', [$start, $end])->approved();
        $withdrawQuery = Withdraw::whereBetween('created_at', [$start, $end])->approved();
        if($request->has('user_id')){
            $depositQuery = $depositQuery->where('user_id', $request->user_id);
            $withdrawQuery = $withdrawQuery->where('user_id', $request->user_id);
        }
        $deposits = $depositQuery->sum('amount');
        $withdraws = ($withdrawQuery->sum('amount'))*-1;
        $net = $deposits + $withdraws;

Result:

Deposit     Withdraw     Net Amount
20,946.00   15,066.00    5,880.00



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire