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 ----------------+
