What to use when a large number of UPDATE queries?

Dealing with the issue of optimization, due to the large number of database queries, the site often returns Error 500.
As a result, now most decided to get rid of requests through the cycles and try to collect all the data into an array, and generate 1 request.

Was wondering about multiple UPDATE in one request, but as I understand it is not possible.

Googled the Internet and came across:
LOCK TABLES `products` WRITE;
UPDATE `products` SET `description`='$text1' WHERE `name`='BMW';
UPDATE `products` SET `description`='$text2' WHERE `name`='ford';
UPDATE `products` SET `description`='$text3' WHERE `name`='lans';
UNLOCK TABLES;


Question 1: How do I UPDATE if need to update 100 records. Create 100 UPDATE queries?
Question 2: does it make sense to do a LOCK TABLES on the query execution time?
Question 3: How will the site, if for example I refresh the page, and she turns to the table that we blocked? The website will be loaded as long as the table is not rasplachivaetsya or until the timeout? or just return error?
March 20th 20 at 11:33
2 answers
March 20th 20 at 11:35
1. it is possible and so. open the transaction and do 100 queries on prepared update, then commit (competing transaction to commit will see row versions according to their level of isolation). about transactions and their isolation levels set out in the documentation. and can drive the necessary changes in a separate table and make one update, Zagainov table

2. no. lock is not transaction-safe, on the other hand, the muscle itself is able to block what you need in the process

3. will wait till timeout, and then drops with the error lock wait timeout
Tell me how there is a difference between:
1. Doing through the loop the queries individually (50 pieces per minute)
foreach ($orders as $order) {
// make UPDATE query
}

2. Make one request to collect as single query using something like this:
UPDATE orders SET Request = (CASE Id WHEN 150758 THEN 'test1'
 WHEN 150690 THEN 'test2'
 WHEN 150756 THEN 'test3'
END)
WHERE id IN(150758,150690,150756);


How it makes sense to bother with option # 2 ? - samir.Metz15 commented on March 20th 20 at 11:38
@kira_Haif you appetite, there is no heavy index, the difference in the eye you wouldn't notice. but if you try to rebuild the index 50 times instead of one, it will be a heavy operation - mitchel.Schneider64 commented on March 20th 20 at 11:41
@kira_Ha, Instead of option 2, use a temporary table for changes:
it is possible to drive the necessary changes in a separate table and make one update, Zagainov table
- Violette.Bernier commented on March 20th 20 at 11:44
March 20th 20 at 11:37
No need to lock table. Use of the transaction. A 500 error you have is not because of the base, and due to the fact that code is written crookedly
Tell us about the transactions in detail, what is it?
500 error occurs during the execution of the script, but if script is run to do some action on the website which is associated with the dB 50/50 we can get this error, I asked the host support said it may be due to the large number of dB accesses for 1 second. - samir.Metz15 commented on March 20th 20 at 11:40
@kira_Ha,
1. about transactions, read the documentation. Here to help people but don't do copy-paste from the docks
2. hoster to do with it. if . the problem of interaction with the database then these events need to be captured and properly processed. This means that your code curve - Fletcher commented on March 20th 20 at 11:43
@user_test_345, 17 CRON task hanging that run every minute.
Do you think this is enough? - samir.Metz15 commented on March 20th 20 at 11:46
@kira_Ha, it's nothing - Fletcher commented on March 20th 20 at 11:49
@Susie_Zieme31, What are the problems with the base?? what could be the problem? the request is made incorrectly? or what? You better give a piece of advice, if you have an understanding, not just send the user there, do not know where, do something, do not know what. Many of the queries I'm trying to do one query, and not a dozen through cycles.

I really don't understand about some problems with DB you're talking about. I have no problem with mysql, I'm asking how to do the task in 1 query, not to create 100 queries separately, isn't that right? - samir.Metz15 commented on March 20th 20 at 11:52
@kira_Ha, 500 error means that the server has not received the expected response from the script. without seeing code it is impossible to answer the question, what's wrong, but wrong in the script - mitchel.Schneider64 commented on March 20th 20 at 11:55
@kira_Ha, you don't want to hear and learn independently. Don't want to read about the transaction of your business. You don't want in your code to catch connection errors with other services - your problems. You have no problems with the database, all right - you just have no understanding of how it works, and no desire to read the basics in the documentation and find the problem - Fletcher commented on March 20th 20 at 11:58

Find more questions by tags MySQL