Timeline for Deleting millions of rows in MySQL
Current License: CC BY-SA 2.5
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 27, 2023 at 13:40 | comment | added | Anna | What about primary keys that have several columns? Does the "order by" need to include all columns? | |
| Oct 10, 2023 at 10:24 | comment | added | Jin Lim | Sleep for few seconds is crucial. If you don't sleep in between the SQL query, and row has a lot of data, It may shut down the MySQL process. | |
| May 26, 2023 at 13:05 | comment | added | E. Greeff | For me only worked with the limit: DELETE FROM dados_cliente WHERE id < 10000; --> mysql get slow/stops DELETE FROM dados_cliente WHERE id < 5000000 LIMIT 10000; --> works fine id is the primary key why this happends on mysql? | |
| Oct 10, 2022 at 14:20 | comment | added | Armin | This query does not work for me. Even when set the limit to 100 items, I get the error: Lock wait timeout exceeded; try restarting transaction | |
| Mar 5, 2021 at 6:43 | comment | added | Ng Sek Long |
Maybe in a script that sleeps for a second or three it is now possible to just use MySQL to inject the delay between query, see this: DELETE FROM ... LIMIT 1000; SELECT SLEEP(5); DELETE FROM ... LIMIT 1000; SELECT SLEEP(5); And so on
|
|
| Feb 18, 2021 at 12:07 | comment | added | khue bui | Why we have to sleep between iterations? | |
| Dec 3, 2018 at 10:07 | comment | added | tsauerwein | Here is a simple Python script which implements this approach: gist.github.com/tsauerwein/ffb159d1ab95d7fd91ef43b9609c471d | |
| Jun 5, 2017 at 17:09 | comment | added | Diogo Medeiros | I still have my doubts if a pivot table isn't the best way, but, I made a procedure, just to keep the sanity anyway: hastebin.com/nabejehure.pas | |
| Sep 22, 2016 at 18:37 | comment | added | bishop |
Note that one can't combine DELETE ... JOIN with ORDER BY or LIMIT.
|
|
| Dec 14, 2009 at 12:46 | history | edited | chaos | CC BY-SA 2.5 |
added 19 characters in body
|
| Aug 27, 2009 at 9:25 | vote | accept | Steven Surowiec | ||
| Aug 23, 2009 at 20:38 | comment | added | MarkR | If you use DELETE with LIMIT, you should really use ORDER BY to make the query deterministic; not doing so would have strange effects (including breaking replication in some cases) | |
| Aug 23, 2009 at 16:40 | history | answered | chaos | CC BY-SA 2.5 |