Keep 1000 recent records

[eevan79]

How to delete all records but keep 1000 most recent records (by ID or date whatever) in MySql database?

[gristoi]

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

[eevan79]

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?

[techdude]

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!

[fenway]

Why not filter on date?