>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 > -----Original Message----- > From: Scott H [mailto:[EMAIL PROTECTED] > Sent: 12 November 2003 17:45 > To: Dan Greene; MySQL Mailing List > Subject: RE: maintaining size of a db > > > 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] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
