Good health to all. He did, did and got the 30 minute script work :( In the DB, the table has a product ($ X1), and it has 7 warehouses (A100-A700)

Maybe there is the possibility of optimizing this.

Maybe there is something more universal in SQL queries, which I do not know yet. What is this brute-force UPDATE by seven lines?

$result1 = mysql_query ("UPDATE $table SET $table.field_01='$A1', $table.field_02='$B1' WHERE $table.field_04='A100' and $table.field_05='$X'", $db); $result2 = mysql_query ("UPDATE $table SET $table.field_01='$A2', $table.field_02='$B2' WHERE $table.field_04='A200' and $table.field_05='$X'", $db); $result3 = mysql_query ("UPDATE $table SET $table.field_01='$A3', $table.field_02='$B3' WHERE $table.field_04='A300' and $table.field_05='$X'", $db); $result4 = mysql_query ("UPDATE $table SET $table.field_01='$A4', $table.field_02='$B4' WHERE $table.field_04='A400' and $table.field_05='$X'", $db); $result5 = mysql_query ("UPDATE $table SET $table.field_01='$A5', $table.field_02='$B5' WHERE $table.field_04='A500' and $table.field_05='$X'", $db); $result6 = mysql_query ("UPDATE $table SET $table.field_01='$A6', $table.field_02='$B6' WHERE $table.field_04='A600' and $table.field_05='$X'", $db); $result7 = mysql_query ("UPDATE $table SET $table.field_01='$A7', $table.field_02='$B7' WHERE $table.field_04='A700' and $table.field_05='$X'" $db);

Too this place to me, somehow, I don’t like it ... PS Corrected a bit, where there is $ X (it comes 1, for 7 records, then another, for the next 7)

  • Do you have a field field_05 or field_04 unique? - Deonis
  • not. ordinary. It is that they are repeated 7 times for each product (which is also reflected as 7 identical ID code entries) - I_CaR
  • one
    @I_CaR, there is already no place for comments)) In general, in the query that you showed there are errors. The names of the table fields are wrapped in reverse quotes (English layout, the letter "E"), and the data that is entered into the database is wrapped correctly - in single quotes. Plus, I repeat that the request will work only if the field, in your example, Sklad_ID or the ItemID field , will be like a unique or primary key. - Deonis
  • about an error with a sign (`), I immediately noticed at the first test. But with more difficult fields, how can they become unique if they are repeated? In that table, a unique other field "message_id" How can this request be tied for it? If the data on this field does not come at all. those. they are not present at all in XML and this field is just a counter field. here read: askdev.ru/mysql/9421/kak-rabotayet-ON-DUPLICATE-KEY-UPDATE/new/… there the request is tied for the UID, a unique field. But my fields in the request, and with incoming data from XML, are not unique: ( - I_CaR
  • Made a bunch of ALTER TABLE message219_for_test ADD UNIQUE key_unique (Sklad_ID, ItemID) pulled out the values ​​of the Message_ID key field and used it through a query: tavil.ru/netcat/tmp/code_2.html And again the null-result ... The same link (warehouse-id_goods) is, why not furychit? And for the first one it does not fry, tavil.ru/netcat/tmp/code.html and for the second (with the capture of the key field) tavil.ru/netcat/tmp/code_2.html . And it does not give errors, maybe in the request, what's wrong? The data in variables come, field names rechecked. - I_CaR

2 answers 2

In general, I can’t wait for an answer to my question in a comment. And if you still have either the field_04 or field_05 field is PRIMARY or UNIQUE , then the query can be made like this:

 INSERT INTO $table (`field_01`, `field_02`, `field_04`, `field_05` ) VALUES ('$A1', '$B1', 'A100', '$X1'), ('$A2', '$B2', 'A200', '$X2'), ('$A3', '$B3', 'A300', '$X3'), ('$A4', '$B4', 'A400', '$X4') ON DUPLICATE KEY UPDATE `field_01` = VALUES(`field_01`) , `field_02` = VALUES(`field_02`)
  • one
    Hardly these fields are unique, otherwise the database for 30 minutes would not have thought, even with a very large amount. By the way, yes, judging by the description of the structure, the UNIQUE key still asks for a bunch of fields field_04 and field_05 - Pavel Vershinin
  • @Pavel Vershinin, about 30 minutes, so this is an exaggeration)) I do not even see anything threatening in the requests of the vehicle. Freaked out and launched 50 such requests. Result - no more than 2 seconds. Brakes can be a completely different reason. - Deonis
  • Not! no no!! They are not unique, they are repeated 7 times for each product (which is also reflected, as 7 identical ID code entries) UNIQUE in that database in general on other fields. There is something tempting in your code. And why INSERT, but not UPDATE? Although it is also required in the future, to introduce new ones. Now while UPDATE on changes I spend. - I_CaR
  • one
    > Why INSERT, and not UPDATE? Although it will be required in the future too, for the introduction of new ones. Have you looked through the request to the end? )) The chip in ON DUPLICATE KEY UPDATE , i.e. if the unique key / keys match, the INSERT is not executed, and the existing entry is updated. - Deonis
  • one
    @I_CaR, something is wrong here. > on LAN is done at 60% faster. Well I am meticulous. )) I drove to the host. And believe it or not, but on the host it was faster than on LAN and much faster. 100 requests - less than a second. And on the host is not nginx, which is faster, and Apache / 2.2.14 - Deonis

I would add LOW_PRIORITY to requests. And probably the index on the field field_04 and field_05

  • in OS LOW_PRIORITY, on the contrary processes slow down their execution (take for example winrar). And in the execution of the UPDATE command it will give +? - I_CaR
  • one
    Well, let's say, with LOW_PRIORITY actual execution of the request will be postponed until the database becomes more free. Tobish request will queue up, and the script will go on. But forget about it, Deonis gave you great advice, much better than mine. Just make your notes unique, something like: ALTER TABLE table ADD UNIQUE UNIQUE ( field_04 , field_05 ) You surely cannot field_05 same product within the same warehouse? - Pavel Vershinin
  • About the uniqueness, I realized when ON DUPLICATE KEY UPDATE, but how to fix a field that repeats? Can unique fields be repeated? The product (ID) is repeated by the number of warehouses. And warehouses, in turn, are repeated in the line of another product already. - I_CaR
  • one
    Do you have a bunch of fields warehouse + product can be repeated? I think no. Therefore, I propose to hang the unique key NOT on the same field, but on both the warehouse and the goods ALTER TABLE test ADD UNIQUE key_unique ( field_04 , field_05 ) - Pavel Vershinin
  • Made a bunch of ALTER TABLE message219_for_test ADD UNIQUE key_unique (Sklad_ID, ItemID) selected the values ​​of the Message_ID key field and made it through the query: tavil.ru/netcat/tmp/code_2.html And again the null-result ... - I_CaR