There is a users table and payments table

As well as the page with an affiliate program

The system groups user statistics by an affiliate program, i.e. the user entered the affiliate program page, the system gets his referral code from the table and searches for users in the users table who have activated this code (by searching) as soon as such users are found the system uses the same search to search for records of everyone who used this The code in the payments table and sums the amount of payments. Further, this amount is transferred to the original user and it turns out the statistics and, accordingly, its level in the referral program.

This whole thing slows down the page load very much (because of all requests, the page loads more than 5 seconds). It is necessary to optimize the code in order to speed up the page loading as much as possible. I can not imagine how to change requests to simplify them.

Code:

 $ref_users = User::where('invite', $user->ref_code)->get(); $all = 0; foreach($ref_users as $ref){ $all += Func::ref_deposited($ref->user_id); } $level = Func::getLevel($all);

The function of receiving the sum of all payments:

 static function ref_deposited($user_id){ $all = 0; $payments = PaySys::where('user_id', $user_id)->where('status', 1)->sum('amount'); $payments2 = PaySys2::where('user_id', $user_id)->where('status', 'complete')->sum('amount'); $deposits = TradeLog::where('user_id', $user_id)->where('status', 'success')->sum('amount'); $all = $payments + $payments2 + $deposits; return round($all, 2); }

Function level:

 static function getLevel($sum){ $sum = Func::convertToUsd($sum); if($sum >= 0 && $sum < 500){ $value['level'] = 1; $value['perc'] = 5; $value['refer'] = 0.30; $value['toNext'] = 500; }elseif($sum >= 500 && $sum < 2000){ $value['level'] = 2; $value['perc'] = 6; $value['refer'] = 0.40; $value['toNext'] = 2000; }elseif($sum >= 2000 && $sum < 5000){ $value['level'] = 3; $value['perc'] = 7; $value['refer'] = 0.45; $value['toNext'] = 5000; }elseif($sum >= 5000 && $sum < 7500){ $value['level'] = 4; $value['perc'] = 8; $value['refer'] = 0.60; $value['toNext'] = 7500; }elseif($sum >= 7500 && $sum < 10000){ $value['level'] = 5; $value['perc'] = 9; $value['refer'] = 0.65; $value['toNext'] = 10000; }elseif($sum >= 10000){ $value['level'] = 6; $value['perc'] = 10; $value['refer'] = 0.70; $value['toNext'] = 99999; } return $value; }

    1 answer 1

    Try to create a reference book of unique codes and amounts for this code, by indexing it. If I understand correctly, in this task there is no need to identify users who also used this code, only a sum is needed. So? In this scenario, the user will not expect payments. And the calculations themselves will be performed separately from the user session.

    Or perhaps there are additional restrictions on time intervals? Then you can use partitioning of the payments table by date, and then applying filters on dates in the search request. This will narrow the search.

    • Thanks for the answer. So you are proposing to create a new table with columns (code, amount) and every time someone using the referral code fills up the balance, add the amount in the line of the code? - Ilya78rus
    • Perhaps I did not correctly compiled the logic? and there is another option to quickly obtain the necessary data - Ilya78rus
    • Yes, the new table, yes, every time update the trigger, yes, it is expensive. But this way you parallelize processes, and your clients will not waste time waiting for calculations. - Amirullo Fayazov