there is such a table:

┌──────┬──────────────────────┬────┐ │ Path │ Name │ id │ ├──────┼──────────────────────┼────┤ │ 0 │ D:\1.bat │ 1 │ │ 0 │ D:\Nichrome.rar │ 2 │ │ 0 │ C:\pagefile.sys │ 3 │ │ 0 │ E:\Уроки вождения.avi│ 4 │ │ 0 │ D:\$RECYCLE.BIN │ 5 │ └──────┴──────────────────────┴────┘

you need to break the second column into components and lead to this form:

 ┌──────┬──────────────────────┬────┐ │ Path │ Name │ id │ ├──────┼──────────────────────┼────┤ │ D:\ │ 1.bat │ 1 │ │ D:\ │ Nichrome.rar │ 2 │ │ C:\ │ pagefile.sys │ 3 │ │ E:\ │ Уроки вождения.avi │ 4 │ │ D:\ │ $RECYCLE.BIN │ 5 │ └──────┴──────────────────────┴────┘

the problem is that there are about 800 thousand lines, and UPDATE is corrected line by line with the UPDATE operator, in 5 minutes (more in IIS I don’t know where to set up, all settings are ignored) only 32000 lines are obtained, I don’t know how to enter several hundreds in 1 rows, as in the case of the INSERT statement, a script without UPDATE requests to the database bypasses all 800 thousand rows in just 40 seconds. Can there be minds that can help? Already thought the whole thing in CSV unload and shovel through the file, I think it will turn out many times faster

  • 2
    Those. stupidly the first 3 characters in another column? then update tab set path=substr(name,1,3), name=substr(name,4) just before this check with select that these substr really give what you expect - Mike
  • @Mike, you see that the fields are called path and filename , not drive and something else. - teran
  • What is the problem to make a request directly to the database, bypassing pkhp in principle as an unnecessary link in this issue? - teran
  • @teran If I thought that everything was really that simple, I would write an answer, not a comment. I wrote how it could be if the data were such, expecting the TS to see it would lead other examples of the data and formulate the task more accurately. - Mike
  • You can also add a WHERE Path = 0 condition. In this case, if the query is interrupted, it can be restarted and it continues from the interruption point, since after execution the cell will not be equal to 0 - Sergey Strelchenko

1 answer 1

You should not execute UPDATE requests in a loop; moreover, you may want to refuse to perform such a task from PHP. It is best to perform this conversion using pure SQL using a single UPDATE query. Suppose you only deal with Windows paths and the separator is always a slash. In this case, the task is reduced to finding the last slash in the substring Name and placing everything to the left of it in the Path , and everything to the right of it in Name . You can perform this task using the following query:

 UPDATE pathes SET Path = SUBSTRING(Name, 1, LENGTH(Name) - LENGTH(SUBSTRING_INDEX(Name, '\\', -1))), Name = SUBSTRING_INDEX(Name, '\\', -1);

If you don’t give up PHP, it’s probably worth doing this query in a few tricks, controlling whether or not the conversion was done using the WHERE and limiting the number of rows processed at once using LIMIT .

 UPDATE pathes SET Path = SUBSTRING(Name, 1, LENGTH(Name) - LENGTH(SUBSTRING_INDEX(Name, '\\', -1))), Name = SUBSTRING_INDEX(Name, '\\', -1) WHERE Path <> '0' LIMIT 50000;

This will allow not to block the table for too long time and consistently convert the entire data array.

  • Thank you, this is what you need, I come across MySql insofar as, therefore, I do not know all the subtleties of requests, the cyclic update through php is generally a terrible braking thing, but this query is a general thing! I will now delve into the study of MySql :) - Roman Loginov