What is the best way to delete old rows from MySQL on a rolling basis?

by

Try creating Event that will run on database automatically after the time interval you want.

Here is an Example:
If you want to delete entries that are more than 30 days old from some table ‘tableName’, having column entry ‘datetime’. Then following query runs every day which will do required clean-up action.

CREATE EVENT AutoDeleteOldNotifications
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY 
ON COMPLETION PRESERVE
DO 
DELETE LOW_PRIORITY FROM databaseName.tableName WHERE datetime < DATE_SUB(NOW(), INTERVAL 30 DAY)

We need to add ON COMPLETION PRESERVE to keep the event after each run. You can find more info here: http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

More Related Contents:

  1. mysql error cant fix the error [duplicate]
  2. Why can’t this entry be added because of Foreign Key error?
  3. MySQL pivot row into dynamic number of columns
  4. How should I resolve –secure-file-priv in MySQL?
  5. node-mysql multiple statements in one query
  6. MySQL server startup error ‘The server quit without updating PID file’
  7. MySQL AUTO_INCREMENT does not ROLLBACK
  8. Best type of indexing when there is LIKE clause [duplicate]
  9. MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE
  10. Difference between Key, Primary Key, Unique Key and Index in MySQL
  11. What’s the difference between MyISAM and InnoDB? [duplicate]
  12. Create new user in MySQL and give it full access to one database
  13. How can I access the MySQL command line with XAMPP for Windows?
  14. When to use STRAIGHT_JOIN with MySQL
  15. Allow all remote connections, MySQL
  16. Difference in MySQL JOIN vs LEFT JOIN
  17. Import CSV to MySQL
  18. Get most recent row for given ID
  19. MySQL Install: ERROR: Failed to build gem native extension
  20. Is merge statement available in MySQL
  21. Using an IF Statement in a MySQL SELECT query
  22. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  23. MySQL connection over SSH tunnel – how to specify other MySQL server?
  24. Storing IPv6 Addresses in MySQL
  25. Insert/ Update random date in MySQL
  26. How to determine which database is selected
  27. How to remove constraints from my MySQL table?
  28. Get total hours worked in a day mysql
  29. Is it possible to count two columns in the same query
  30. mySQL regex in the where clause

Leave a Comment