phplist

NOTE:: Before reporting an issue, make sure you are running the latest version, currently 3.3.1


View Issue Details Jump to Notes ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0015641phplist applicationClick Trackingpublic05-10-11 18:4906-10-11 20:34
ReporterKevinK 
PrioritynormalSeverityminorReproducibilityalways
StatusresolvedResolutionfixed 
PlatformOSOS Version
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:
select
  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:
select
  (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.
Attached Files

- Relationships

-  Notes
(0051408)
michiel (manager)
06-10-11 20:34

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.


Copyright © 2000 - 2017 MantisBT Team
Powered by Mantis Bugtracker