Optimizing MySQL UPDATE

Question

MySQL

Good evening everyone! Help with this problem. There is a blank table with the following structure: id - AI seria - varchar(4) number - int(6) enabled - int(1) id_doc - int(4) id_user - int(3) fio_user - varchar(200) time - int( 12) time_end - int(12)

There are currently 10,000 records in the table (Approximately 1500 are added each month). You need to do an UPDATE, change the enabled field from 1 to 0 for 1,000 records. The user enters a series for example SSSS and let's say the numbers are 1-1000, I parse them, and I go through them in a loop with such a request: UPDATE blank SET enabled = '0', time_end = '$time' WHERE seria = '$seria' AND number = '$number_with_null' LIMIT 1it runs for several minutes. Is there any way to speed up this process? Thanks in advance.

Answer 1

UPDATE blank SET enabled=true WHERE seria='seria' AND number BETWEEN 1 AND 1000;Maybe something like this? It is not necessary to do a cycle with a heap of requests.

Answer 2

Most likely the brakes are due to the default settings of the InnoDB engine. You must either wrap all requests in a transaction, or make one request, like this:

AND number = IN (....все номера...)