If you’ve ever used Prosper202, you will know that it can get slow the bigger your database gets.  One way to improve the speed once your database gets large is to archive old data that you don’t need.  This is a modified version of this post.

Here is how to archive old click/campaign data (still making them accessible online):

Step1: Login to your server mysql and do a database backup of your current Prosper202 database:

-Login via SSH by typing (without quotes, replacing your user, domain, and port):

ssh [email protected] -p2200

-Then type “mysql” to login to mysql.

-Then type:

mysqldump -p p202 > p202backup.sql;

Step2: Create a new database, you can simply name it the date you’ve archived your data

create database p202march2011;

Step3: Restore your current database you’ve backed up in step 1 to this new table

mysql -p p202march2011 < p202backup.sql

Step4: Delete clicks from your current Prosper202 installation to free up space:

TRUNCATE TABLE 202_clicks;
TRUNCATE 202_clicks_spy;

Your database on your installation should be much smaller now, and should load faster.

Step5 (optional): Making your archive data available online.
Say your main tracking domain is www.domain.com. Create a subdomain test.domain.com. copy your entire Prosper202 folder to this new site, and in the 202-config.php file, update the $dbname variable to your archived database (in our example, p202march2011). Now to see your archived data, simply log into test.domain.com.