[fix] Problems with Click Tracking size

[Guest]

Hi everybody!

Sorry for my english, I'm from Brazil.

I use the phplist version 2.10.5 with more than 500.000 users, I sent
something like 100.000 messages daily.

I customize all my phplist, thats includes some files like
/admin/index.php, /admin/sendlib.php, /admin/processqueue.php,
/admin/processbounces.php, among others importants files.

Today, after my 3º message by phplist, I see my database almost crashing,
my table linktrack has more than 3.000.000 lines, my server is working only
to process the requests of the linktrack feature.

I read on forum that the new version, 2.11.3, work fine with larges
databases and linktrack feature on, and I try to find a documentation of
the changes of this new feature, because, as I said, I customize importants
files and if I just upload the new version I will lose all my work.

There is some documentation on how to upload the new feature of linktrack
just changing some codes and adding new tables, without rewriting any
files?

Can anyone say me what files has changed in the version 2.11.3?

Thank you very much!

[Guest]

http://mantis.phplist.com/view.php?id=6473

1. Problem: php_linktrack table is more than 500mb

2. Goal: try to use "php_linktrack_userclick" table (<3mb> 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 ---

Code: Select all

    $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

PS. delete records from phplist_linktrack will cause you to lose some "url click statistics" data. Make sure you don't want the OLD "url click statistics" data

[FabioLima]

Hei "Guests" from Brazil.
If you see it e-mail me:

fabio.lima.corujao@gmail.com

I'm from Brazil too, and need more contacts about PHPList in my neighborhood.