Re: [NEWBIE] How To Trim Database To N Records
Is there a query that will, say, trim a table down to a million rows (with some sort order, of course, as I'm interested in deleting the oldest ones)? If you have got a datecolumn, you might also want to delete anything that is older than x days (2 in my example): DELETE FROM database.table WHERE datecolumn <= DATE_SUB(sysdate(),INTERVAL 2 day); Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [NEWBIE] How To Trim Database To N Records
--- "David T. Ashley" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm a beginning MySQL user ... > > I have a table of log entries. Over time, the entries could grow to > be > numerous. I'm like to trim them to a reasonable number. > > Is there a query that will, say, trim a table down to a million rows > (with > some sort order, of course, as I'm interested in deleting the oldest > ones)? > > The sorting isn't a problem. I've just never seen an SQL statement > that > will drop rows until a certain number remain ... > > Thanks, Dave. > There are two ways to do this but this is the simplest: (this should set the value of @lastID to whatever is the 11st oldest ID) SELECT @lastID:= ID, @lastDate:= datecolumn FROM yourtablename ORDER BY datecolumn desc, id desc LIMIT 10,1; Now, assuming that there are several records within the same second (very possible depending on your traffic) and that you want to limit the database to exactly 10 rows (see previous query), then this should work for you: DELETE FROM yourtablename WHERE datecolumn < @lastDate or (datecolum = @lastDate AND id < @lastID); Let us know how it works... Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [NEWBIE] How To Trim Database To N Records
At 11:15 PM -0400 4/12/06, David T. Ashley wrote: Hi, I'm a beginning MySQL user ... I have a table of log entries. Over time, the entries could grow to be numerous. I'm like to trim them to a reasonable number. Is there a query that will, say, trim a table down to a million rows (with some sort order, of course, as I'm interested in deleting the oldest ones)? The sorting isn't a problem. I've just never seen an SQL statement that will drop rows until a certain number remain ... Thanks, Dave. Something like this might work (untested): select @n:=count(*) from your_table delete from your_table order by time_stamp limit @n-100 Of course, you'd want to try it on a test table first, not live data! This assumes 100 is the max number of records you want to keep, you want to delete the oldest records based on the time_stamp column, AND that the record count when you do this delete is always > 100. You'd need to do some additional checking first if that isn't the case, as I don't know at the moment what the behavior for a negative or zero limit is (the docs below should tell you). More info: http://dev.mysql.com/doc/refman/4.1/en/user-variables.html http://dev.mysql.com/doc/refman/4.1/en/example-user-variables.html http://dev.mysql.com/doc/refman/4.1/en/delete.html steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[NEWBIE] How To Trim Database To N Records
Hi, I'm a beginning MySQL user ... I have a table of log entries. Over time, the entries could grow to be numerous. I'm like to trim them to a reasonable number. Is there a query that will, say, trim a table down to a million rows (with some sort order, of course, as I'm interested in deleting the oldest ones)? The sorting isn't a problem. I've just never seen an SQL statement that will drop rows until a certain number remain ... Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
