View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0006473||phplist application||Statistics||public||24-05-06 15:09||31-05-12 03:31|
|Target Version||4.0.x||Fixed in Version||2.11.3|
|Summary||0006473: clicktracking causes excessive database load|
|Description||Enabling 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.
|Tags||No tags attached.|
|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||resolved||michiel||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|
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.
||the crashing of the server was caused by the sudden extra megs of db-tables, causing the partition to become full.|
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.
mclicks.php (6,908 bytes)
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,
if (ul.hasLinkTrack is null, 0, 1) hasLinkTrack,
from %s m
Left join (
SELECT messageid, count(distinct userid) users, count(distinct date) totalclicks, count(distinct linkid) linkcount
group by messageid) uc on uc.messageid = m.id
Left join (SELECT messageid, 1 as hasLinkTrack
group by messageid) ul on ul.messageid = m.id
Left join (SELECT messageid,
count(viewed) as views,
count(status) as total
group by messageid) um on um.messageid = m.id
Where 1 = 1 %s
order by m.id 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
||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.|