Re: How to delete the oldest X number of rows?

+-le 12/12/2005 11:29 -0500, John Roberts a dit :
| Hi,
| 
| I have a table that logs the performance of a system. I get an entry in
| this table each time the system does something.
| 
| I want to keep the last 50,000 rows in the table. I'd like to run a cron
| job every 5 minutes that will delete all rows > 50,000, oldest first.
| What's the most efficient way to do this query/delete?
| 
| DELETE FROM log WHERE timestamp < ????
| 
| I can come up with a way to do this using COUNT(*), but I'll bet there is a
| smarter way.

Would that :
DELETE FROM log WHERE timestamp < (select timestamp from log order by
timestamp desc limit 1 offset 50000);

do ?

-- 
Mathieu Arnold