what is the fastest way to update thousands rows in mysql
mysql, performance, python
Solution
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.
Problem
- 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 ...