View Issue Details

IDProjectCategoryView StatusLast Update
0015452phpList 3 applicationMessage Managementpublic14-11-12 18:37
Reporterh2b2 
PrioritynormalSeverityminorReproducibilityhave not tried
Status newResolutionopen 
Product Version2.11.6 
Target VersionFuture developmentsFixed in Version 
Summary0015452: patch to significantly speed up admin/?page=messages
DescriptionPosted by thomashallock in the forum: http://forums.phplist.com/viewtopic.php?f=18&t=29050
==== Start Quote ====
When link tracking is enabled, /admin/?page=messages takes a long time to render if you've sent messages to over several hundred thousand people.

I tracked this down to queries that look like this being executed from admin/messages.php:

select sum(clicked) from phplist_linktrack where messageid = {$message_id};

Since MySQL cannot index the results of functions ,i.e. sum(), the sum has to be re-counted on every page load.

I solved this using MySQL triggers to effectively index the link counts in a separate table.

I have attached the patch to this message if you're interested in trying it out. If you want to apply this patch to an existing PHPList instance, be sure to run the upgrade script to create and the the linktrack_sums table.

    Index: phplist/admin/upgrade.php
    ===================================================================
    --- phplist/admin/upgrade.php (revision 2)
    +++ phplist/admin/upgrade.php (working copy)
    @@ -248,6 +248,11 @@
         case "2.8.4": case "2.8.5": case "2.8.6":
           Sql_Query("alter table {$tables["user"]} add index index_uniqid (uniqid)");
         case "whatever versions we will get later":
    +
    + Sql_Verbose_Query("DROP TABLE IF EXISTS `{$tables["linktrack_sums"]}`;");
    + Sql_Create_Table($tables["linktrack_sums"],$DBstruct["linktrack_sums"]);
    + Sql_Verbose_Query("insert into {$tables["linktrack_sums"]} select messageid, sum(clicked) as sum_clicked from {$tables["linktrack"]} group by messageid;");
    +
           #Sql_Query("alter table table that altered");
           break;
         default:
    Index: phplist/admin/connect.php
    ===================================================================
    --- phplist/admin/connect.php (revision 2)
    +++ phplist/admin/connect.php (working copy)
    @@ -91,7 +91,7 @@
       'userstats' => $table_prefix . 'userstats',
       'bounceregex' => $table_prefix . 'bounceregex',
       'bounceregex_bounce' => $table_prefix . 'bounceregex_bounce',
    -
    + 'linktrack_sums' => $table_prefix . 'linktrack_sums',
    );
    $domain = getConfig("domain");
    $website = getConfig("website");
    Index: phplist/admin/initialise.php
    ===================================================================
    --- phplist/admin/initialise.php (revision 2)
    +++ phplist/admin/initialise.php (working copy)
    @@ -58,6 +58,11 @@
           echo "... ".$GLOBALS['I18N']->get("failed")."
\n";
       }
    }
    +
    +if(!$res = Sql_Query($trigger_sql,1)) {
    + die("error creating triggers with this code $trigger_sql");
    +}
    +
    #

    if ($success) {
    Index: phplist/admin/structure.php
    ===================================================================
    --- phplist/admin/structure.php (revision 2)
    +++ phplist/admin/structure.php (working copy)
    @@ -357,6 +357,10 @@
             "index_5" => array("miduidurlindex (messageid,userid,url)",""),
             "unique_1" => array("(messageid,userid,url)","")
         ),
    + "linktrack_sums" => array(
    + "messageid" => array("integer not null unique primary key", "message ID"),
    + "sum_clicked" => array("integer", "total number of links clicked for this message"),
    + ),
         "linktrack_userclick" => array (
             "linkid" => array("integer not null",""),
             "userid" => array("integer not null",""),
    @@ -419,4 +423,22 @@
    */
    );

    +$linktrack_table = $usertable_prefix."_linktrack";
    +
    +$trigger_sql = <<<SQL
    +
    + CREATE TRIGGER `insert_sum_message_clicks` BEFORE INSERT ON `$linktrack_table` FOR EACH ROW begin
    + INSERT INTO phplist_linktrack_sums (messageid, sum_clicked) values (NEW.messageid, NEW.clicked)
    + ON DUPLICATE KEY UPDATE
    + sum_clicked = sum_clicked + NEW.clicked;
    + END;
    +
    + CREATE TRIGGER `update_sum_message_clicks` BEFORE UPDATE ON `$linktrack_table` FOR EACH ROW begin
    + INSERT INTO phplist_linktrack_sums (messageid, sum_clicked) values (NEW.messageid, NEW.clicked)
    + ON DUPLICATE KEY UPDATE
    + sum_clicked = sum_clicked + (NEW.clicked - OLD.clicked);
    + END;
    +SQL;
    +
    +
    ?>
    Index: phplist/admin/messages.php
    ===================================================================
    --- phplist/admin/messages.php (revision 2)
    +++ phplist/admin/messages.php (working copy)
    @@ -186,7 +186,8 @@
       $result = Sql_query("SELECT * FROM ".$tables["message"]." $subselect order by status,entered desc $limit");
       while ($msg = Sql_fetch_array($result)) {
         $uniqueviews = Sql_Fetch_Row_Query("select count(userid) from {$tables["usermessage"]} where viewed is not null and messageid = ".$msg["id"]);
    - $clicks = Sql_Fetch_Row_Query("select sum(clicked) from {$tables["linktrack"]} where messageid = ".$msg["id"]);
    +// $clicks = Sql_Fetch_Row_Query("select sum(clicked) from {$tables["linktrack"]} where messageid = ".$msg["id"]);
    + $clicks = Sql_Fetch_Row_Query("select sum_clicked from {$tables["linktrack_sums"]} where messageid = ".$msg["id"]);
         $messagedata = loadMessageData($msg['id']);
         printf ('<tr><td valign="top"><table>
           <tr><td valign="top">'.$GLOBALS['I18N']->get("From:").'</td><td valign="top">%s</td></tr>

Just a note, I extracted these changes from my instance of PHPList and was not able to test the upgrade and install use cases.
==== End Quote ====
TagsNo tags attached.

Activities

michiel

14-11-12 18:37

manager   ~0051903


The click tracking has changed quite a bit and when I updated the query to

select sum(clicked) from {$tables["linktrack_ml"]} where messageid =

it was fast enough. So the changes proposed here are not necessary any longer. However, it's still interesting as a method to speed up places in the application, which is why I'll keep this open for further reference.