At 02:45 PM 1/23/2002 -0800, Eric Mayers wrote: >I'm designing a system that will parse and store log files in a >MySQL-Max-2.23 database (the log table will be innodb). I have a >requirement that as the database fills towards maximum capacity that it >writes over the oldest records rather than dropping new records. > >I'd like to know if this is something that can be handled by the >database itself? Otherwise it will be up to the application. Has
Yes, the database can handle this itself. That is, you can structure your UPDATE transaction such that it deletes old data... the question is whether you really want to do that... >anyone written code to do this? Is it best to delete a chunk of old >records when its full then continue inserting, or would something like: > > UPDATE logs SET col=data, date=newdate ORDER BY date DESC LIMIT 1 > >be better once the table was at capacity? (would this work at all?) If you want my advice, I'd structure it similarly to this: Maximum number of rows in the table is, say, 10000. Do updates normally. Once every 30 minutes (or however often), prune that table such that only the top 10000 entries stick around. Alternatively, you could: Do updates normally but keep track of count in a separate table. Note that you'd just increment this table, you wouldn't do a SELECT COUNT(*) each time. During the update, if the count is > 11000, prune the table and readjust the count similarly. There are other options. I think these are the best. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
