patch to significantly speed up /admin/?page=messages

Technical discussion for developers and service providers

patch to significantly speed up /admin/?page=messages

Postby thomashallock » 12:40am, Sat 07 Nov, 2009

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.
thomashallock
phpList newbie
 
Posts: 3
Joined: 11:36pm, Fri 06 Nov, 2009

Re: patch to significantly speed up /admin/?page=messages

Postby thomashallock » 12:43am, Sat 07 Nov, 2009

Code: Select all
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")."<br />\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>
thomashallock
phpList newbie
 
Posts: 3
Joined: 11:36pm, Fri 06 Nov, 2009

Re: patch to significantly speed up /admin/?page=messages

Postby thomashallock » 2:10am, Sat 07 Nov, 2009

Just a note, I extracted these changes from my instance of PHPList and was not able to test the upgrade and install use cases.
thomashallock
phpList newbie
 
Posts: 3
Joined: 11:36pm, Fri 06 Nov, 2009

Re: patch to significantly speed up /admin/?page=messages

Postby michiel » 2:19pm, Wed 31 Oct, 2012

michiel
Admin
 
Posts: 1022
Joined: 10:18pm, Fri 11 Apr, 2003
Location: Buenos Aires, Argentina


Return to Developers Space

Who is online

Users browsing this forum: No registered users and 1 guest

cron