Such a situation, in the database there is a table which consists of the columns " OperationID " and " Operation_Sum ". There is a script that is in the crown; every hour it changes the value of the " Operation_Sum " column, adding the calculated coefficient to the current value.

I do this, scoop up all the records from the table, go through each record, take the current value of " Operation_Sum " from it, add the coefficient to it and UPDATE update the record in the database. It turns out that if there is a million records in the table, then in a cycle a million times the database will be accessed. Is this normal for php? Or can you somehow update the strings with a bundle?

    1 answer 1

    Everything is done with one simple request? which will immediately update all records in the table without having to read them first

    UPDATE mytable SET operation_sum = operation_sum + :my_coef;

    And if for calculating the coefficient, only data from the database and data available in the MySQL server are used, then PHP and cron are not needed here. Enough embedded EVENTS

    • one
      PHP and cron are not needed here. Enough built-in EVENTS This is provided that the "calculated coefficient" can be calculated on the MySQL server. - Akina
    • @Akina thanks for the clarification - Anton Shchyrov
    • @AntonShchyrov, thanks, the fact is that for each record the coefficient is different, but sometimes it is the same, it is not stored in the database, but is calculated according to a certain algorithm. Is it possible to transfer to the query that you wrote for example an array with new values ​​and roll them into the rows of the table? - alexey romashev
    • @AntonShchyrov; Can it be easier in my case to drop a table and somehow, with the help of an INSERT, fill all rows with a single query? So is it possible? Or is it not comme il faut? - alexey romashev
    • @AntonShchyrov, or another option came to mind, may not loop into the database in a loop, but form a query string? And then, after the cycle, to make a request to the database, only the line is supposed to be a huge one, will it strain the server? - alexey romashev