eevan79 Posted March 26, 2011 Share Posted March 26, 2011 How to delete all records but keep 1000 most recent records (by ID or date whatever) in MySql database? Quote Link to comment Share on other sites More sharing options...
gristoi Posted March 26, 2011 Share Posted March 26, 2011 If you want it to delete all historical records but keep the last 1000 etc then a good option would be to use a mysql trigger. This could listen for each insert / update , check the number of rows and delete all but the newest 1000. ( only if your using mysql >5.1. Have a look at: http://dev.mysql.com/doc/refman/5.0/en/triggers.html Quote Link to comment Share on other sites More sharing options...
eevan79 Posted March 26, 2011 Author Share Posted March 26, 2011 Thanks for reply...I'll read detailed information later. Maybe it is better to use 2 queries - SELECT count(*) and DELETE FROM table ORDER BY id ASC LIMIT count . Does anyone knows a better solution? Quote Link to comment Share on other sites More sharing options...
techdude Posted March 29, 2011 Share Posted March 29, 2011 Try, DELETE * FROM table LIMIT 1001, 2000 This will delete all the records from 1001 to 2000. Just run it multiple times if there are more records to delete. Please note: It is recommended that you delete the rows in small amounts (from 100 to 1000 at a time) to avoid disturbing normal access to the database. After everything is complete, run this last command to reclaim unused space, and to defragment the data file. OPTIMIZE TABLE table Post back with more questions! Quote Link to comment Share on other sites More sharing options...
fenway Posted April 3, 2011 Share Posted April 3, 2011 Why not filter on date? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.