Commits

Zhang Huangbin committed fc46766

[MySQL] Better SQL performance while listing all domains. Thanks atros <christian (at) eol.co.nz>.

Comments (0)

Files changed (2)

 = 0.1.6 =
-    * Add enabledService=lda for newly created mail user. Required by
+    * [MySQL] Better SQL performance while listing all domains.
+      Thanks atros <christian (at) eol.co.nz>.
+    * [LDAP] Add enabledService=lda for newly created mail user. Required by
       Dovecot 2.x.
-    * Add an alias account for newly created mail user (address=goto). MySQL
-      backend special.
+    * [MySQL] Add an alias account for newly created mail user (address=goto).
 
 = 0.1.5 =
     * Escape untrusted variables in template files.

libs/mysql/domain.py

         # RAW sql command used to get records.
         self.rawSQLOfRecords = """
             SELECT
-                domain.domain, domain.description, domain.aliases,
-                domain.mailboxes, domain.maxquota, domain.quota,
-                domain.transport, domain.backupmx, domain.created,
-                domain.active,
-                SUM(DISTINCT mailbox.bytes) AS stored_quota,
-                SUM(DISTINCT mailbox.quota) AS quota_count,
-                COUNT(DISTINCT mailbox.username) AS mailbox_count,
-                COUNT(DISTINCT alias.address) AS alias_count
-            FROM domain
-            LEFT JOIN domain_admins ON (domain.domain = domain_admins.domain)
-            LEFT JOIN mailbox ON (domain.domain = mailbox.domain)
-            LEFT JOIN alias ON (domain.domain = alias.domain AND alias.address <> alias.goto)
+                a.domain, a.description, a.aliases, a.mailboxes, a.maxquota, a.quota,
+                a.transport, a.backupmx, a.created, a.active,
+                IFNULL(b.alias_count,0) AS alias_count,
+                IFNULL(c.mailbox_count,0) AS mailbox_count,
+                IFNULL(c.stored_quota,0) AS stored_quota,
+                IFNULL(c.quota_count,0) AS quota_count
+            FROM domain AS a
+            LEFT JOIN (SELECT domain, COUNT(*) AS alias_count FROM alias GROUP BY domain) AS b ON (a.domain=b.domain)
+            LEFT JOIN (SELECT domain, SUM(mailbox.bytes) AS stored_quota, SUM(mailbox.quota) AS quota_count, COUNT(*) AS mailbox_count FROM mailbox GROUP BY domain) AS c ON (a.domain=c.domain)
+            LEFT JOIN domain_admins ON (domain_admins.domain=a.domain)
             %s
-            GROUP BY
-                domain.domain, domain.description, domain.aliases,
-                domain.mailboxes, domain.maxquota, domain.quota,
-                domain.transport, domain.backupmx, domain.created,
-                domain.active
-            ORDER BY domain.domain
+            GROUP BY a.domain
+            ORDER BY a.domain
             LIMIT %d
             OFFSET %d
         """ % (self.sql_where, session['pageSizeLimit'],
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.