CODEWITHSUNDEEP
mysqllaravellaravel-5.1
Cheam Huoy San
Cheam Huoy San

Reputation: 245

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

Upvotes: 2

Views: 1232

Answers (1)

Shobi
Shobi

Reputation: 11461

Basically, laravel eloquent doesn't have anything to do with the optimization of the sum(), it simply translates your code into SQL query. And getting into your question

Remeber: You can't optimize the sum function itself *(unless you are some real jedi)

You have 1M records in your database, and MySQL sum will have to go through all the records to calculate the sum. and in that case, 7 seconds is okay I think. According to this question here, sum() on a 7M record table took about 22 seconds. So depending on your schema, machine etc... velocity may vary a bit here and there.

So how can you go ahead with improving the response time?

Somehow you will have to reduce the number of records before giving it to sum() to process.

one way I suppose you can do is, you can calculate sums of 10k record batches/chunks and store it in a separate table, with appropriate date and time range. The broad term for this is archiving. Then when you have to calculate a sum for a given range of date, use the previous complete sum from the new table and use it. You could set up a chron job to update this table one's in a day or something to make sure the data is proper always.

Another way is to partition MySQL tables. Which also seems to be a promising way. Which is also kind of archiving. but I am not very experienced with it to make any comments on it. Google should help you with this.

The indexing on the columns like created_at and the column you use for approved scope should improve the response time, but not much as if you were leveraging the archiving technique.

Also, I have MySQLmysql query cache on an answer from the same link I specified above, more info. Which you can try employing. But as of mysql 8 it has been deprecated and moreover, it won't work as expected if you have multiple instances running and if you have partitioned tables.

Upvotes: 2

Related Questions