View Issue Details

IDProjectCategoryView StatusLast Update
0006473phplist applicationStatisticspublic31-05-12 02:31
Reportermike 
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
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.

Relationships

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

Activities

michiel

24-05-06 14:17

manager   ~0013919

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

24-05-06 14:38

reporter   ~0013922

the crashing of the server was caused by the sudden extra megs of db-tables, causing the partition to become full.

hola

02-06-06 23:03

reporter   ~0014143

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

http://rs6.net/tn.jsp?t=zcsi6vbab.0.dyergvbab.hdpwv7s7.469719&p=http%3A%2F%2Fwww.energyimages.com

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 09:37

 

mclicks.php (6,908 bytes)

user4354

23-11-08 10:15

  ~0050244

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: http://www.2and2.net/files/492922741888a.jpg

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 m.id messageid,
                subject,
                uc.users,
                uc.linkcount,
                uc.totalclicks,
                if (ul.hasLinkTrack is null, 0, 1) hasLinkTrack,
                um.views views,
                um.total total
        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 = m.id
        Left join (SELECT messageid, 1 as hasLinkTrack
            from %s
            group by messageid) ul on ul.messageid = m.id
        Left join (SELECT messageid,
            count(viewed) as views,
            count(status) as total
            from %s
            group by messageid) um on um.messageid = m.id
        Where 1 = 1 %s
        order by m.id desc limit 20
    ',
    $GLOBALS['tables']['message'],
    $GLOBALS['tables']['linktrack_userclick'],
    $GLOBALS['tables']['linktrack'],
    $GLOBALS['tables']['usermessage'],
    $subselect));

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

23-11-08 23:08

manager   ~0050247

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.