I have table users which has millions of data

users have colums like id, competed_date, thirdparty_id etc etc

thirdparty_id is new column i have to update for all users

proces to find thirdparty_id

for each user there is corresponding order table from which we have to fetch latest order from which we will get one value based on it we can calculate some rate

that rate we can search in another table thirdpary from there we will get thirdparty_id

i have did everything for individual users its working fine

now my question is how to execute this for millions of users?

i am using laravel

the process is fetch all users having thirdparty_id null and call formula function to find id and update

but fetch all means millions of data in single query? so if i am giving limit whats maximum limit i can give?

what are another options to execute?

queries i used

select id as userid from users where thirdparty_id is null limit {some limit}

in foreach of this

select amount from orders wehere user_id =userid order by created desc limit 1

some wiered formula with amount will give `rate`

select id from third party where start_rate > rate and end_rate<rate

update users set thirdparty_id=id where id=userid

CodePudding user response：

You can use chunk: https://laravel.com/docs/9.x/eloquent#chunking-results

something like this:

public const CHUNK_SIZE = 5000;

...

User::whereNull('thirdparty_id')->chunk(self::CHUNK_SIZE, function (Collection $users) {
    // $users is collection contains 5000 users, you can do mass update them or something you want  
});

CodePudding user response：

For this type of problematic, i will use a store procedure to do the update instead of using language like PHP.

Doing this the migration store proc run inside MySQL Server and you throw away all problems like PHP execution time, memory limits, apache timeout, etc.