what is the fastest way to update thousands rows in mysql

Question

• lets assume you have a table with 1M rows and growing ...
• every five minutes of every day you run a python programm which have to update some fields of 50K rows

my question is: what is the fastest way to do the work?

• runs those updates in loop and after last one is executed than fire up a cursor commit?
• or generate file and than run it throught command line?
• create temp table by huge and fast insert and than run a single update to production table?
• do prepared statements?
• split it up to 1K updates per execute, to generate smaller logs files?
• turn off logging while running update?
• or do a cases in mysql examples (but this works only up to 255 rows)

i dont know ... have anyone do something like this? what is the best practise? i need to run it as fast as possible ...

Answer 1

Here's some ways you could speed up your UPDATES.

When you UPDATE, the table records are just being rewritten with new data. And all this must be done again on INSERT. That's why you should always use INSERT ... ON DUPLICATE KEY UPDATE instead of REPLACE.

The former one is an UPDATE operation in case of a key violation, while the latter one is DELETE / INSERT

Here's an example INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; More on this here.

UPDATE1: It's a good idea to do your inserts all in a single query. This should speed up your UPDATES. See here on how to do that.

UPDATE2: Now that I have had a chance to read your other sub-questions. Here's what I know-

• instead of in a loop, try to execute all UPDATE in a single sql & single commit.
• Not sure this is going to make any difference. SQL queries are more important.
• Now this is something you could experiment with. Benchmark it. This kind of a thing depends on the size of the TABLE & the INDEXES you have, plus INNODB or MYISAM.
• No idea about this.
• refer first point.
• Yes, this might speed your stuff up slightly. Also see if you have slow_query_log turned on. This logs all slow queries to a separate logfile. Turn this off too.
• Again. refer first point.

Answer 2

query execution process: Server first parsing your query then execute you need to analysis
the query

then server take less time to parse then he execute faster instead of slow in other way