List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:April 13 2006 3:17am
Subject:Re: [NEWBIE] How To Trim Database To N Records
View as plain text  
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-1000000

Of course, you'd want to try it on a test table first, not live data! 
This assumes 1000000 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 > 
1000000. 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 Edberg                                http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center                            sbedberg@stripped |
| Bioinformatics programming/database/sysadmin             (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
Thread
[NEWBIE] How To Trim Database To N RecordsDavid T. Ashley13 Apr
  • Re: [NEWBIE] How To Trim Database To N RecordsSteve Edberg13 Apr
  • Re: [NEWBIE] How To Trim Database To N RecordsShawn Green13 Apr
  • Re: [NEWBIE] How To Trim Database To N RecordsDominik Klein13 Apr