> How do you guys go about periodically rolling tables that get too big? > I could export the table, mv the .txt file, create a new .txt file > with the appropriate header, rm the .sql file, and restart IC. Does > anyone have a better method they like? I'm using mysql. > Well, it would depend upon the use to which you put your "big" tables, although I have to say that your proposed deletion mechanism is far too Heath Robinson for my taste. :-) My suggestion would be to include a date column with each row, and use that as a condition of your periodic delete. I.e. delete all rows that are over x months/years old. That'll work if the table is just used as a log, or something similar, but may be inconvenient if the table holds orders, or other information you'd prefer to keep on hand. If you want to dump the (to be deleted) rows to a file beforehand then you can easily do so. Again, using the date column as your selection criteria. You might want to save a summary of the (to be deleted) rows in another table before deletion. For instance, if you were deleting orders that are over five (or whatever) years old then you could save summary a row for every month. The monthly row could summarise values such as the number of orders, the total net/gross amounts, the average net value of the orders and the tax revenue collected etc. It would probably take a long time for that table to be considered "big", and you'd still have the requisite number of years worth of "real data" on hand to keep the tax man happy. >From your proposed solution, it seems as if you just want to clear out the table and give yourself a blank space to start over with. In that case, I wonder why you're saving rows into a table in the first place - especially as you'll lose all benefit of being able to select rows from that table once the periodic clearout kicks in. If row selection is not an issue, then wouldn't it be better to just save the rows into a text file in the first place, and then use something like logrotate to keep the file size under control? My view is usually that you either want the data or you don't. If you don't then either simply delete it when it gets old, or don't save it in the first place. If you do want to keep the data then either leave it in the database or (if appropriate) delete it and keep a summary row instead.
Hi Kevin, Thank you for the insight. The table in question actually logs traffic data from my site's visitors. The bigger the table gets, the longer it takes to run a report on it. I have a date field in there, and I'd like to backup and delete each row older than 30 days. I'd prefer those rows to be backed up to another table as opposed to a log file so I can run a report on that old data if I need to. Is there an ictag or two I can use to copy the old rows to a different table and delete them from the primary table? - Grant _______________________________________________ interchange-users mailing list suppressed http://www.icdevgroup.org/mailman/listinfo/interchange-users
Mail converted by mhonarc 2.6.15
This archive provided courtesy of JSW4.NET, Internet Hosting Services for Small Business.