Deleting millions of rows in MySQL

by 
DELETE FROM `table`
WHERE (whatever criteria)
ORDER BY `id`
LIMIT 1000

Wash, rinse, repeat until zero rows affected. Maybe in a script that sleeps for a second or three between iterations.

More Related Contents:

  1. MySQL Error 1093 – Can’t specify target table for update in FROM clause
  2. Remove duplicate rows in MySQL
  3. How to do the Recursive SELECT query in MySQL?
  4. How to delete from multiple tables in MySQL?
  5. JOIN queries vs multiple queries
  6. MySQL DELETE FROM with subquery as condition
  7. SQL query: Delete all records from the table except latest N?
  8. MySQL indexes – what are the best practices?
  9. Subqueries with EXISTS vs IN – MySQL
  10. Deleting rows with MySQL LEFT JOIN
  11. mysql select from n last rows
  12. Difference in MySQL JOIN vs LEFT JOIN
  13. 60 million entries, select entries from a certain month. How to optimize database?
  14. Checking multiple columns for one value
  15. SQL DELETE with JOIN another table for WHERE condition
  16. Mysql Improve Search Performance with wildcards (%%)
  17. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  18. mysql, ifnull vs coalesce, which is faster?
  19. Mysql delete statement with limit
  20. Higher cardinality column first in an index when involving a range?
  21. How do I delete all the duplicate records in a MySQL table without temp tables
  22. MySQL “IN” queries terribly slow with subquery but fast with explicit values
  23. Delete from one table with join
  24. MySQL: Can I do a left join and pull only one row from the join table?
  25. MySQL LIMIT on DELETE statement
  26. Delete all rows with timestamp older than x days
  27. How to optimise MySQL queries based on EXPLAIN plan
  28. How to estimate SQL query timing?
  29. Deleting a row based on the max value
  30. delete rows from multiple tables

Leave a Comment