When it comes to databases, less is more.
You are doing the right thing by archiving off old data as this will speed up the performance and reduce the maintenance time of the active database.
The archived data needs to be considered. Keeping it in a separate database instance will be beneficial because backups/restoration and other maintenance activities will be completed separately - reducing the effect on the live data.
You should consider keeping the archived data on a physically different disk to the live data - this will avoid conflicts if both are being used at the same time. Also consider the type of disk this the archive is on as this has cost implications - does it really need to be on a raid type array or is a single disk and backup tape/dvd all that is needed? Storage may be relatively cheap, but it is still a cost in $, IO, time and network.
Next take a look at the archived data - do you really need to keep all the records? do you really need to keep all the columns of data? Would changing the structure of the data result in a smaller disk footprint/faster read query time? Can you summarize the data? Archived data for data mining does not always need to have the same structure as live data.
Keeping separate months data in separate databases may be a good way to go, but there other options that may make the analysis easier/quicker. Your options here include keeping the data in one database instance, but with table per month. Another is to have one set of archive tables and use table partitioning (read the manuals).
There are two options
INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;
REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;
It mechanically performs a DELETE and an INSERT.
CAVEAT
Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.
You should also add a UNIQUE INDEX on the new table as follows:
ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);
UPDATE 2013-04-08 17:13 EDT
If you are doing updates, there are two ways to do this:
1. UPDATE JOIN
UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;
2. UPDATE JOIN with GROUP BY
UPDATE
(
SELECT BB.id,BB.phone,BB.email FROM
(SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;
Give it a Try !!!
Both options will take full advantage of the UNIQUE index on name.
Best Answer
You can write a simple script externally or use the MySQL event scheduler. Both will work just fine. The benefit of the MySQL event scheduler is that it will be part of your database backups.