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
0006473phplist applicationStatisticspublic24-05-06 15:0931-05-12 03:31
PlatformOSOS Version
Product Version2.10.2 
Target Version4.0.xFixed in Version2.11.3 
Summary0006473: clicktracking causes excessive database load
DescriptionEnabling click tracking causes database tables registering click tracks to become very huge.

After 1 mailing to 10.000 recipients with a mail with about 70 links, the database table phplist_linktrack grew to 150mb with hundreds of thousands of records.

Causing the server to crash.

Underlying, it appears that every link in a message, for every user, receives a record. It might be better to encode links in linktracked messages like: link=link_id,user_id (encrypted perhaps) so that not for every sent out each message, each link for each user has to be registered, but for each message only each link.

This can really block link tracking feature if the current system is maintained.
TagsNo tags attached.
Attached Files? file icon mclicks.php [^] (6,908 bytes) 23-11-08 09:37

- Relationships Relation Graph ] Dependency Graph ]
related to 0002705closed PHPList v2.11 release 
related to 0006481closed Database size due to clickstats 
related to 0009264closed linktrack remove message option 
related to 0009229resolvedmichiel With Link tracking on, links are broken in messages sent through list and db errors reported on GUI screens 
related to 0005410acknowledged Delete old linktrack / clicktrack records 

-  Notes
michiel (manager)
24-05-06 15:17

the links ARE encrypted with message, user and even text/html. The reason that all links are added to the database is to calculate who did NOT click the link. However, I agree that it's a bit of a problem that the table grows so much and I'm still tweaking it. Suggestions are welcome. I think the best option is to purge old data at intervals. Alternatively to make it configurable to have the statistics for who did not click.

I have several systems with over 15m entries, but it's never caused it to crash (just yet) for me.
mike (reporter)
24-05-06 15:38

the crashing of the server was caused by the sudden extra megs of db-tables, causing the partition to become full.
hola (reporter)
03-06-06 00:03

Hello - what about adding all the link track data on the url for example [^]

The example url seems to have the distination url as well as user data.

For the other stats like number of links in email and other overall stats they could be added into a table after finishing the mail out.
23-11-08 10:15

1. Problem: php_linktrack table is more than 500mb

2. Goal: try to use "php_linktrack_userclick" table (< 3mb) to create the same mclick.php statistics instead of "php_linktrack" (> 500mb)
  message statistic picture link: [^]

3. Change: I just upload a modified version of mclick.php file (message click statistics page), please check the file I uploaded. The main change is the following sql script ---

    $req = Sql_Query(sprintf('
        select messageid,
                if (ul.hasLinkTrack is null, 0, 1) hasLinkTrack,
                um.views views,
        from %s m
        Left join (
            SELECT messageid, count(distinct userid) users, count(distinct date) totalclicks, count(distinct linkid) linkcount
            FROM %s
            group by messageid) uc on uc.messageid =
        Left join (SELECT messageid, 1 as hasLinkTrack
            from %s
            group by messageid) ul on ul.messageid =
        Left join (SELECT messageid,
            count(viewed) as views,
            count(status) as total
            from %s
            group by messageid) um on um.messageid =
        Where 1 = 1 %s
        order by desc limit 20

4. Result: the mclick.php (message click statistics page) load much fast than before and I can even truncate or delete records from phplist_linktrack. For example: run delete sql script in phpmyadmin or mysql console

delete from `phplist_linktrack` where messageid < 60;
OPTIMIZE TABLE phplist_linktrack;

in my case, remove 350mb data from phplist_linktrack
and I will still be able to see "message click statistics page" mclick.php
michiel (manager)
23-11-08 23:08

version 2.11.3 is quite stable and has clicktracking completely rewritten to avoid big tables. I use it with several clients, and without any problems.

Copyright © 2000 - 2017 MantisBT Team
Powered by Mantis Bugtracker