Clearing old clicks from a Prosper202 Database

If you need to clear click data from a Prosper202 Database, and you have an extremly large database, it is better to do it from the command line instead of from a graphical interface such as from Prosper, or phpMyAdmin.

Here are the steps to do this.

1.  SSH to the server
2. Connect to the MySQL database
   1. mysql -A $dbname -u'$username' -p'$password'   (replace the $dbname $username, and $password with the proper info for the database)
3. Run this command to get a unix timestamp
   1. SELECT UNIX_TIMESTAMP(DATE_ADD(now(), INTERVAL -1 month));
   2. The above command will give you a number like 1486665293  (this is unix timecode for one month prior to the time you ran this command)
4. After you have the timestamp code, you need to run the following commands.
   1. DELETE FROM 202_clicks WHERE click_time < 1486665293 ;  (In this line, replace the number 1486665293   with whatever number you got from the command above.)
   2. DELETE FROM 202_clicks_advance WHERE click_id NOT IN (SELECT click_id FROM 202_clicks);
   3. DELETE FROM 202_clicks_counter WHERE click_id NOT IN (SELECT click_id FROM 202_clicks);
   4. DELETE FROM 202_clicks_record WHERE click_id NOT IN (SELECT click_id FROM 202_clicks);
   5. DELETE FROM 202_clicks_site WHERE click_id NOT IN (SELECT click_id FROM 202_clicks);
   6. DELETE FROM 202_clicks_spy WHERE click_id NOT IN (SELECT click_id FROM 202_clicks);
   7. DELETE FROM 202_clicks_tracking WHERE click_id NOT IN (SELECT click_id FROM 202_clicks);
   8. DELETE FROM 202_ips WHERE ip_id NOT IN (SELECT ip_id FROM 202_clicks_advance);
   9. DELETE FROM 202_last_ips WHERE ip_id NOT IN (SELECT ip_id FROM 202_ips);
   10. DELETE FROM 202_sort_ips WHERE ip_id NOT IN (SELECT ip_id FROM 202_ips);
5. Once you have executed all 10 command, you should optimize the database.  You can copy and paste this whole line.  It will do each table one by one.

   1. OPTIMIZE TABLE `202_clicks`; OPTIMIZE TABLE `202_clicks_advance`; OPTIMIZE TABLE `202_clicks_counter`;  OPTIMIZE TABLE `202_clicks_record`; OPTIMIZE TABLE `202_clicks_site`;  OPTIMIZE TABLE `202_clicks_spy`; OPTIMIZE TABLE `202_clicks_tracking`; OPTIMIZE TABLE `202_ips`; OPTIMIZE TABLE `202_last_ips`; OPTIMIZE TABLE `202_site_urls`;