affiliate click through table extremely large

[kenduret]

After running the this plug-in for a couple of years the xxx_affiliates_clickthroughs_tbl has become a beast. Here’s a copy of the slow SQL queries log off my server:

# Tue Jan 15 20:38:00 2013

# Query_time: 3.401686 Lock_time: 0.010033 Rows_sent: 0 Rows_examined: 240146

use xxxxxxxxxxxx;

SET timestamp=1358307480;

SELECT * FROM xxx_affiliates_clickthroughs_tbl WHERE time between ’03:37:52′ and ’03:37:57′ and ipaddress=’xxxxxxxxxxxxxxxx’

We are having about 20,000 page views per day so the query executes every 5 to 10 seconds and has pretty much shut down the site.

The click through records go back to early 2010 and I can’t imagine needing two years of click through data.

Any suggestions on how to remediate the situation quickly? Right now I think I’m going to export the last two months of click through data, delete the click through table, then import the last two months of data.

[admin]

Simply delete all records in that table that are older than two months. You can do that from your PHPMyAdmin. Alternatively, what you suggested should work fine too.

The clickthrough table only keeps record of the affiliate referral clicks. Everytime you receive an affiliate referral, the data of that click goes into that table. This table does not get queried on the font end of your site (when your pages are loading). This table queried only when the affiliate want to see their referral click history or you (the admin) want to see it from the admin dashboard. Also, the data that is stored in that table is not used anywhere else so you can safely delete it.

The following tutorial should come in handy:

http://www.tipsandtricks-hq.com/how-to-easily-import-or-export-any-of-your-wordpress-database-content-using-phpmyadmin-3415

[Author]

Posts