Archiving information

[thumbelina]

I have a problem where my table will eventually become to large to run efficently. I have come to the conclusion that the best thing to do is to archive all records over two years old. I need to know how to set up the archive and how to let someone access it if they need to look back further than two years for historical reasons. I have no idea where to start on this one. Does anyone have any ideas?

 

[vcherubini]

Well, would the archive be a flat text file or another mysql table? (I assume you are using a database, probably mysql, am I right?).

Anyway, if you are on a Linux box, you could set up a cron job to run a PHP script every morning, say at 12:00 am. The PHP script would search the database for rows with dates that are older than two years.

It would then generate a SQL query to to take those rows, insert them into the new table, and then delete them from the old table:

[tt]
INSERT INTO newtable (id,field1,field2,field3....) VALUES ($id,$field1,$field2,$field3...)

DELETE FROM oldtable WHERE id=id
[/tt]

I would set up the archive to have all of the information from the large table, along with a date that says what time the new data was added to the archive.

You could then build a simple search that would allow a user to search for a date archived, date entered, title, or whatever you want to let the user search by.

You could:

[tt]
SELECT * FROM newarchive WHERE $field LIKE '%$query%'
[/tt]

You can start with that. Writing the entire script for this would be too much right now, but if you need more help, don't hesitate to ask.

Hope this helps.

-Vic vic cherubini
krs-one@cnunited.com

http://www.cnunited.com

====
Knows: Perl, HTML, JavScript, C/C++, PHP, Flash
====

 

[thumbelina]

The seperate table for the archived information is a very good idea,thanks. I was actually thinking about having the database only archive information once a year, this way there would be at all times in the system: up to this point of the present year, and all of the previous two year. I am using mysql for the database. As I said, I'm really not sure where to start on this one, I have only been working with php and mysql since may and I'm in over my head on this problem, so I really need some serious help with this. If you have any ideas of places with this kind of information, or if you have anythoughts yourself, I would greatly apperacte it.