Geek Answers Handbook

What is the fastest way to update thousands of rows in mysql

  • lets assume that you have a table with 1M rows and growing ...
  • every five minutes every day you run a python program that should update some fields of 50K lines

my question is: what is the fastest way to do this job?

  • launches these updates in a loop and starts after the last, what triggers the cursor fix?
  • or generate a file and run it on the command line?
  • create a temporary table with huge and fast insertion and run one update in the worksheet?
  • Ready statements?
  • Divide it by 1K updates per run to generate smaller log files?
  • disable logging when updating starts?
  • or do cases in mysql examples (but this only works up to 255 rows)

I don’t know ... will anyone do something like this? What is the best practice? I need to run it as quickly as possible ...

+4
source share
2 answers

Here are some ways to speed up your UPDATES .

When you UPDATE , the table entries are simply overwritten with the new data. And all this needs to be done again at INSERT . That is why you should always use INSERT ... ON DUPLICATE KEY UPDATE instead of REPLACE .

The first UPDATE operation in case of a key violation, and the second - DELETE / INSERT

Here is an example INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; Read more about it here .

UPDATE1: It is recommended that you do your insertions in a single query. This should speed up your UPDATES . See here for how to do this.

UPDATE2: Now I had the opportunity to read your other questions. Here is what I know -

  • and not in a loop, try to execute all UPDATE in one sql and single commit.
  • Not sure if that will make a difference. SQL more important.
  • Now you can experiment with this. Rate it. It depends on the size of TABLE and INDEXES , plus INNODB or MYISAM .
  • I don’t know about that.
  • refer to the first point.
  • Yes, it can speed things up a bit. Also check if slow_query_log is slow_query_log . This writes all slow requests to a separate log file. Turn it off.
  • Again. refer to the first point.
+4
source

Query execution: the server parses your request first and then parses it. request

then the server takes less time for parsing, then it runs faster, and not slows down in another way.

0
source

More articles:


All Articles