OK, I *THINK* I follow you here. Couple of questions. I'm reading an online tutorial trying to figure this out, and I am led to believe mysql can't do nested queries, aka sub-queries. But you say it can? Is this recent? And I don't have a timestamp field, I have an autoincrement field, but what do you mean by the "(@aa:=id)" thing? I don't follow that. thanks.
--- Dan Greene <[EMAIL PROTECTED]> wrote: > What I would do is a classical guesstimate.... > > find the average size per record (data file > size + index file(s) size / # records in table) > > using that, find the data used per day > > using that, figure out how many days, on > average it takes to hit 20GB > > let's say it's 89 days. > > right off the top, take 10% off for safety, now > we're at 80 days > > presuming your table has a timestamp field: > > delete from log_table WHERE TO_DAYS(NOW()) - > TO_DAYS(date_col) > 80 > > if you don't have a timestamp field, but you do > have an autoincrement id field: > > figure out number of records on average = 20gb > (say it's 2M) > again, use 10% for safety (1.8M) > > select (@aa:=id) as low_id from logtable order > by id limit 18000000,1 > delete from logtable where id < @aa > > (do subqueries work with a limit clause?) > > > > -----Original Message----- > > From: Scott H > [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, November 12, 2003 11:19 AM > > To: Dan Greene; MySQL Mailing List > > Subject: RE: maintaining size of a db > > > > > > Yes sir, exactly. It's just that's what I'm > > looking for, and can't figure out. I can set > up > > a cron job, but what exactly would the SQL > delete > > statement be that would allow me to delete > old > > records in such a way that the db maintains > an > > approximately constant size on disk? > (Failing > > that perhaps a delete statement that would > just > > have it maintain a constant # of records? > > ...maybe this would be much simpler?) > > > > --- Dan Greene wrote: > > > cronjob a sql script that runs a delete > > > statement for old jobs daily > > > > > > > --- Egor Egorov wrote: > > > > > Scott H wrote: > > > > >> Can't seem to find this one in the > manual > > > or > > > > >> archives - how do I control a db to > > > maintain > > > > >> its size to an arbitrary value, say 20 > GB? > > > I > > > > >> want to just rotate records, deleting > > > those > > > > >> that are oldest. > > > > > > > > > > You can't restrict size of the database > > > only > > > > > with MySQL, use disk quotas. > > > > > > > > No! That would just stop mysql right in > its > > > > tracks (so to speak...) when it got too > > > large. > > > > But I want old records sloughed off and > the > > > db to > > > > continue running. (This is for a central > > > syslog > > > > box.) > > > > > > > > . > > > > __________________________________ > > Do you Yahoo!? > > Protect your identity with Yahoo! Mail > AddressGuard > > http://antispam.yahoo.com/whatsnewfree > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > ===== -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
