View Issue Details

IDProjectCategoryView StatusLast Update
0015641phplist applicationStatisticspublic06-10-11 20:34
Status resolvedResolutionfixed 
Product Version2.10.14 
Target VersionFixed in Version2.11.6 
Summary0015641: phplist_clicktrack table taking up a large volume of space
DescriptionThe 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 ReproduceTo 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)'
  from information_schema.tables
  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'
  from phplist_linktrack
  where clicked = 0;
The above command returned 99.9621 for me, and took 5 minutes to run.
Additional InformationFrom 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.
TagsNo tags attached.



06-10-11 20:34

manager   ~0051408

clicktracking has been completely rewritten in 2.11.X and in 2.10 version using clicktracking with large amounts of subscribers and/or campaigns should be avoided

upgrade to 2.11.6 and it'll work fine.