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 [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]
