View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0015641||phpList 3 application||Statistics||public||05-10-11 18:49||06-10-11 20:34|
|Fixed in Version||2.11.6|
|Summary||0015641: phplist_clicktrack table taking up a large volume of space|
|Description||The table 'phplist_clicktrack' is taking up a huge volume of space, currently 10GB, and I believe it is because an entry is being created for every link for every user that the campaign is sent to, so in my database of 131,146 users, if a campaign contains 20 links, then 131,146 * 20 = 2,622,920 rows are created in the database every mailing, with a majority of the rows recording 0 clicks.|
|Steps To Reproduce||To see how many MB the phplist_linktrack table is using, you can run this MySQL command:|
round(((data_length + index_length) / (1024*1024)),2) as 'Size (in megabytes)'
where table_name = 'phplist_linktrack';
You can find what percentage of the table is recording 0 clicks with this MySQL command:
(count(*) / (select count(*) from phplist_linktrack) * 100)
as 'Percentage of database that is 0 clicks'
where clicked = 0;
The above command returned 99.9621 for me, and took 5 minutes to run.
|Additional Information||From my examination of the source, I believe the reason the table is designed that way is so that the table can pull double duty in recording link clicks, as well as proving the redirect URL. The issue I believe can be resolved by moving the storage of the redirect URL to it's own table, and making phplist_linktrack.linkid a foreign key of that table.|
|Tags||No tags attached.|