postfixadmin-svn Mailing List for PostfixAdmin (Page 95)

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Revision: 655
          http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=655&view=rev
Author:   christian_boltz
Date:     2009-05-12 22:19:39 +0000 (Tue, 12 May 2009)

Log Message:
-----------
list-domain.php:
- replace get_domain_properties() calls with two SQL queries that fetch
  the domain data and count the number of mailboxes and aliases per domain.
  This change speeds up list-domain extremely on setups with lots of domains,
  mailboxes and aliases. The old code took several minutes on my test data
  (that's about 55000 mailboxes and their aliases), the new code only needs
  some seconds.
- $domain_properties now uses the domain name as index key instead of a
  serial number

templates/admin_list-domain.php:
- replace usage of $list_domains with $domain_properties
- change main loop to use the domain name as $domain_properties array key 
  instead of a serial number

upgrade.php:
- upgrade_655(): add index on domain column in mailbox and alias table.
  This speeds up list-domain from 14s to about 1s on my test data in MySQL
  (the 14s were _after_ the changes in list_domain.php)
- added _add_index() function to hide the database specific details in 
  upgrade functions

This commit should fix the performance problems reported on
http://sourceforge.net/forum/forum.php?thread_id=2343349&forum_id=676076

Modified Paths:
--------------
    trunk/list-domain.php
    trunk/templates/admin_list-domain.php
    trunk/upgrade.php

Modified: trunk/list-domain.php
===================================================================

--- trunk/list-domain.php	2009-05-11 19:39:28 UTC (rev 654)
+++ trunk/list-domain.php	2009-05-12 22:19:39 UTC (rev 655)
@@ -40,25 +40,60 @@
    $fUsername = "";
 }
 
+$list_all_domains = 0;
 if (isset($admin_properties) && $admin_properties['domain_count'] == 'ALL') { # list all domains for superadmins
-   $list_domains = list_domains ();
+   $list_all_domains = 1;
 } elseif (!empty($fUsername)) {
   $list_domains = list_domains_for_admin ($fUsername);
 } elseif ($is_superadmin) {
-   $list_domains = list_domains ();
+   $list_all_domains = 1;
 } else {
    $list_domains = list_domains_for_admin(authentication_get_username());
 }
 
-   if (!empty ($list_domains))
-   {
-      for ($i = 0; $i < sizeof ($list_domains); $i++)
-      {
-         $domain_properties[$i] = get_domain_properties ($list_domains[$i]);
-      }
-   }
-#}
+if ($list_all_domains == 1) {
+   $where = " WHERE domain.domain != 'ALL' "; # TODO: the ALL dummy domain is annoying...
+} else {
+   $list_domains = escape_string($list_domains);
+   $where = " WHERE domain.domain IN ('" . join("','", $list_domains) . "') ";
+}
 
+# fetch domain data and number of mailboxes
+# (PgSQL requires the extensive GROUP BY statement, https://sourceforge.net/forum/message.php?msg_id=7386240)
+$query = "
+   SELECT domain.* , COUNT( DISTINCT mailbox.username ) AS mailbox_count
+   FROM domain
+   LEFT JOIN mailbox ON domain.domain = mailbox.domain
+   $where
+   GROUP BY domain.domain, domain.description, domain.aliases, domain.mailboxes,
+   domain.maxquota, domain.quota, domain.transport, domain.backupmx, domain.created,
+   domain.modified, domain.active
+   ORDER BY domain.domain
+   ";
+$result = db_query($query);
+
+while ($row = db_array ($result['result'])) {
+   $domain_properties[$row['domain']] = $row;
+}
+
+# fetch number of aliases
+# doing this separate is much faster than doing it in one "big" query
+$query = "
+   SELECT domain.domain, COUNT( DISTINCT alias.address ) AS alias_count 
+   FROM domain
+   LEFT JOIN alias ON domain.domain = alias.domain
+   $where
+   GROUP BY domain.domain
+   ORDER BY domain.domain
+   ";
+
+$result = db_query($query);
+
+while ($row = db_array ($result['result'])) {
+   # add number of aliases to $domain_properties array. mailbox aliases do not count.
+   $domain_properties [$row['domain']] ['alias_count'] = $row['alias_count'] - $domain_properties [$row['domain']] ['mailbox_count'];
+}
+
 include ("templates/header.php");
 include ("templates/menu.php");
 

Modified: trunk/templates/admin_list-domain.php
===================================================================
--- trunk/templates/admin_list-domain.php	2009-05-11 19:39:28 UTC (rev 654)
+++ trunk/templates/admin_list-domain.php	2009-05-12 22:19:39 UTC (rev 655)
@@ -27,7 +27,7 @@
 </div>
 
 <?php 
-if (sizeof ($list_domains) > 0)
+if (sizeof ($domain_properties) > 0)
 {
    print "<table id=\"admin_table\">\n";
    print "   <tr class=\"header\">\n";
@@ -43,12 +43,13 @@
    print "      <td colspan=\"2\">&nbsp;</td>\n";
    print "   </tr>\n";
 
-   for ($i = 0; $i < sizeof ($list_domains); $i++)
+#   for ($i = 0; $i < sizeof ($domain_properties); $i++)
+   foreach(array_keys($domain_properties) as $i)
    {
-      if ((is_array ($list_domains) and sizeof ($list_domains) > 0))
+      if ((is_array ($domain_properties) and sizeof ($domain_properties) > 0))
       {
          print "   <tr class=\"hilightoff\" onMouseOver=\"className='hilighton';\" onMouseOut=\"className='hilightoff';\">\n";
-         print "<td><a href=\"list-virtual.php?domain=" . $list_domains[$i] . "\">" . $list_domains[$i] . "</a></td>";
+         print "<td><a href=\"list-virtual.php?domain=" . $domain_properties[$i]['domain'] . "\">" . $domain_properties[$i]['domain'] . "</a></td>";
          print "<td>" . $domain_properties[$i]['description'] . "</td>";
          print "<td>" . $domain_properties[$i]['alias_count'] . " / " . $domain_properties[$i]['aliases'] . "</td>";
          print "<td>" . $domain_properties[$i]['mailbox_count'] . " / " . $domain_properties[$i]['mailboxes'] . "</td>";
@@ -74,9 +75,9 @@
          print "<td>$backupmx</td>";
          print "<td>" . $domain_properties[$i]['modified'] . "</td>";
          $active = ($domain_properties[$i]['active'] == 1) ? $PALANG['YES'] : $PALANG['NO'];
-         print "<td><a href=\"edit-active-domain.php?domain=" . $list_domains[$i] . "\">" . $active . "</a></td>";
-         print "<td><a href=\"edit-domain.php?domain=" . $list_domains[$i] . "\">" . $PALANG['edit'] . "</a></td>";
-         print "<td><a href=\"delete.php?table=domain&delete=" . $list_domains[$i] . "\" onclick=\"return confirm ('" . $PALANG['confirm_domain'] . $PALANG['pAdminList_admin_domain'] . ": " . $list_domains[$i] . "')\">" . $PALANG['del'] . "</a></td>";
+         print "<td><a href=\"edit-active-domain.php?domain=" . $domain_properties[$i]['domain'] . "\">" . $active . "</a></td>";
+         print "<td><a href=\"edit-domain.php?domain=" . $domain_properties[$i]['domain'] . "\">" . $PALANG['edit'] . "</a></td>";
+         print "<td><a href=\"delete.php?table=domain&delete=" . $domain_properties[$i]['domain'] . "\" onclick=\"return confirm ('" . $PALANG['confirm_domain'] . $PALANG['pAdminList_admin_domain'] . ": " . $domain_properties[$i]['domain'] . "')\">" . $PALANG['del'] . "</a></td>";
          print "</tr>\n";
 		}
    }

Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php	2009-05-11 19:39:28 UTC (rev 654)
+++ trunk/upgrade.php	2009-05-12 22:19:39 UTC (rev 655)
@@ -222,7 +222,22 @@
     }
 }
 
+function _add_index($table, $indexname, $fieldlist) {
+    global $CONF;
+    $tabe = table_by_key ($table);
 
+    if ($CONF['database_type'] == 'mysql' || $CONF['database_type'] == 'mysqli' ) {
+        return "ALTER TABLE $table ADD INDEX `$indexname` ( `$fieldlist` )";
+    } elseif($CONF['database_type'] == 'pgsql') {
+        $pgindexname = $table . "_" . $indexname;
+        return "CREATE INDEX $pgindexname ON $table($fieldlist);"; # Index names are unique with a DB for PostgreSQL
+    } else {
+        echo "Sorry, unsupported database type " . $conf['database_type'];
+        exit;
+    }
+
+}
+
 function upgrade_1_mysql() {
     // CREATE MYSQL DATABASE TABLES.
     $admin = table_by_key('admin');
@@ -1063,3 +1078,7 @@
     db_query_parsed("ALTER TABLE `$table_mailbox` CHANGE `local_part` `local_part` VARCHAR( 255 ) {LATIN1} NOT NULL");
 }
 
+function upgrade_655() {
+    db_query_parsed(_add_index('mailbox', 'domain', 'domain'));
+    db_query_parsed(_add_index('alias',   'domain', 'domain'));
+}


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.




2007	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep (13)	Oct (63)	Nov (79)	Dec (34)
2008	Jan (15)	Feb (8)	Mar (8)	Apr (44)	May (18)	Jun (20)	Jul (35)	Aug (21)	Sep (8)	Oct (9)	Nov (21)	Dec (14)
2009	Jan (53)	Feb (13)	Mar (36)	Apr (40)	May (22)	Jun (10)	Jul (18)	Aug (18)	Sep (7)	Oct (26)	Nov (32)	Dec (13)
2010	Jan (6)	Feb (7)	Mar (12)	Apr (2)	May (12)	Jun (8)	Jul (9)	Aug (12)	Sep (12)	Oct (7)	Nov (9)	Dec (42)
2011	Jan (10)	Feb (35)	Mar (43)	Apr (39)	May (8)	Jun (8)	Jul (83)	Aug (20)	Sep (20)	Oct (63)	Nov (33)	Dec (20)
2012	Jan (28)	Feb (3)	Mar (1)	Apr (37)	May (16)	Jun (7)	Jul (3)	Aug	Sep	Oct (3)	Nov (1)	Dec (4)
2013	Jan (6)	Feb (16)	Mar (9)	Apr (11)	May (9)	Jun (30)	Jul (17)	Aug (6)	Sep (2)	Oct (30)	Nov (41)	Dec (26)
2014	Jan (16)	Feb (20)	Mar (5)	Apr (4)	May (14)	Jun (4)	Jul (1)	Aug	Sep (8)	Oct (18)	Nov (26)	Dec
2015	Jan (4)	Feb (9)	Mar (12)	Apr (26)	May (3)	Jun (3)	Jul (2)	Aug	Sep (15)	Oct (13)	Nov	Dec (1)
2016	Jan (1)	Feb (5)	Mar (4)	Apr (3)	May (19)	Jun	Jul	Aug (3)	Sep (16)	Oct (4)	Nov (5)	Dec (5)
2017	Jan (1)	Feb (8)	Mar	Apr (7)	May (3)	Jun (16)	Jul (13)	Aug (16)	Sep (6)	Oct (4)	Nov (10)	Dec (7)
2018	Jan (16)	Feb (17)	Mar (11)	Apr (13)	May (20)	Jun (3)	Jul (10)	Aug	Sep (3)	Oct (1)	Nov (6)	Dec (18)
2019	Jan (18)	Feb (31)	Mar (4)	Apr (4)	May (1)	Jun (8)	Jul (7)	Aug (10)	Sep (23)	Oct (6)	Nov (3)	Dec (5)
2020	Jan (10)	Feb (18)	Mar (34)	Apr (19)	May (6)	Jun (9)	Jul	Aug (10)	Sep (16)	Oct (6)	Nov (3)	Dec (11)
2021	Jan (118)	Feb (22)	Mar (13)	Apr (12)	May (48)	Jun (21)	Jul (17)	Aug (12)	Sep (19)	Oct (25)	Nov (16)	Dec (7)
2022	Jan (6)	Feb (7)	Mar (14)	Apr (4)	May	Jun (16)	Jul (18)	Aug (5)	Sep (12)	Oct (8)	Nov (10)	Dec (13)
2023	Jan	Feb (3)	Mar (2)	Apr (1)	May (2)	Jun (6)	Jul (3)	Aug (3)	Sep (3)	Oct (2)	Nov (7)	Dec (13)
2024	Jan (8)	Feb (6)	Mar (5)	Apr (10)	May (8)	Jun (6)	Jul (2)	Aug (1)	Sep (2)	Oct (4)	Nov (15)	Dec (10)
2025	Jan (3)	Feb (12)	Mar (3)	Apr (7)	May (18)	Jun (10)	Jul (10)	Aug (6)	Sep (16)	Oct (4)	Nov	Dec

postfixadmin-svn Mailing List for PostfixAdmin (Page 95)

postfixadmin-svn — Postfix Admin SVN commit messages - READ ONLY!