Well, it sort of helps. But that section is about future enhancements intended for mysql. I need to set something up now, with the current stable version. One thing I read (can't find it now) indicated that the current version (I'm actually running 4.0.15a) has "limited" support for subqueries - but I don't know exactly how far that goes.
So let me set the stage a bit more - I'll assume for now there is no reasonably simple way to work with the actual size of the database on disk, and instead will go with the idea that I can expect the size of any one record to be of some average. So, according to Dan's suggestion, if I do a little math, and control the number of records, I can control the size of the db -- approximately. That's fine. Thus if have "seq" as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq < @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of "low_seq" into an environment variable and use it in a script file to run the 2nd statement? Other ideas? thanks, scott --- Michael McTernan wrote: > >From the manual: > > 1.8.4.1 Subqueries > > Subqueries are supported in MySQL version 4.1. > See section 1.6.1 Features > Available in MySQL 4.1. > > Hope that helps, > > Mike > > > From: Scott H > > 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] > > > > > > > > > > __________________________________ 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]
