To implement scheduled tasks in ecshop, I will first talk about my ideas, and please give me your ideas.

Description of requirements:

There are about 1 million user records in the user table. Now I want to
determine whether the user is an old user or a new user (user_type new new user old old user) based on the registration time (create_time) field
1. New users: within two weeks of registration and within 14 (inclusive) days.
2. Old users: Register for more than two weeks and more than 14 (exclusive) days.
My thoughts are as follows:

Because ECShop is not a pure OOP framework, I plan to create a crontab directory in the root directory
Create a new user.php in it
In user.php, first count the total number of records in the entire table
Then get 100 items in paging, and loop to update the data of the entire table
When querying, the where condition filter is already the data of old users
In other words, where is always user_type='new', and the entire table
defaults All new

Here comes the problem. There are 1 million records in the table. I am afraid that when I execute my PHP script,
the loop search database MySQL will be brought down. What should I do?

1 million data with 100 entries per page, that is 10,000 pages, how to deal with it?
Is the for loop idea reliable?

Reply content:

Description of requirements:

There are about 1 million user records in the user table. Now I want to
determine whether the user is an old user or a new user (user_type new new user old old user) based on the registration time (create_time) field
1. New users: within two weeks of registration and within 14 (inclusive) days.
2. Old users: Register for more than two weeks and more than 14 (exclusive) days.
My thoughts are as follows:

Because ECShop is not a pure OOP framework, I plan to create a crontab directory in the root directory
Create a new user.php in it
In user.php, first count the total number of records in the entire table
Then get 100 items in paging, and loop to update the data of the entire table
When querying, the where condition filter is already the data of old users
In other words, where is always user_type='new', and the entire table
defaults All new

Here comes the problem. There are 1 million records in the table. I am afraid that when I execute my PHP script,
the loop search database MySQL will be brought down. What should I do?

1 million data with 100 entries per page, that is 10,000 pages, how to deal with it?
Is the for loop idea reliable?

I think there is no rush to do this. It is best to analyze the needs first. The key point is what is the difference between old users and new users? At the same time, whether batch operations are involved (for example, sending website messages to old users in groups is a batch operation, but a specific offer cannot be used until the current user is an old user, which is not a batch operation).

Many times, the distinction between new and old users is only displayed, without any essential changes, or it is always applied to the current logged-in user. At this time, you do not need to add a user_type field and directly determine the create_time of the current user - It is sufficient if time() is greater than 142460*60.

If you really need this function, don’t worry too much, just pay attention to the details:
1- The first update is for all data. It can be done with one update statement. You can just come early in the morning and execute it directly on the database (please Make sure the statement is correct again and again), much faster than you think.
2- Scheduled updates: Note that you only need to perform checks each time for users with user_type = new and create_time < ..., not everyone, so there is no performance problem. Could it be possible to register 100,000 people in the last 14 days? The user_type and create_time fields should be indexed, which is also necessary to improve efficiency.

Batch update directly based on the creation time. After one statement is executed, it can be executed regularly every day.
Current time - (Creation time 86400*14) < 0 means new user

UPDATE user SET user_type = 'old' WHERE (unix_timestamp(now()) - (create_time 86400*14)) < 0;

You can evaluate the increment of your database, because you can actually modify it every day to become an old user, so the amount of data filtered will be greatly reduced

Add an index to the create_time of the user table (the index field must be placed on the left side of where), execute the following sql,

<code>UPDATE user SET user_type = 'old' WHERE create_time < date_sub(curdate(),interval 14 day)</code>

Just execute it at 3:00 am every day (truncate), 1 million pieces of data is not a lot