Topic: Inefficient query on Domains and Account page
I noticed that the page that displays the domain list and information such as number of accounts, aliases and quotas was too slow.
We have about 185 domains, 2000 accounts and 2000 aliases. It was taking about 2 minutes for the page to display and I have a separate database server.
I have rewritten the query so that it's efficient and runs under a second now.
The file I have modified is iredadmin/libs/mysql/domain.py
The original piece of code is as it follows:
if session.get('domainGlobalAdmin') is not True:
sql_where = ' WHERE domain_admins.username = %s' % web.sqlquote(admin)
# RAW sql command used to get records.
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)
LEFT JOIN alias_domain ON (alias_domain.target_domain = domain.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
LIMIT %d
OFFSET %d
""" % (sql_where, settings.PAGE_SIZE_LIMIT, (page-1) * settings.PAGE_SIZE_LIMIT,)I have replaced with:
if session.get('domainGlobalAdmin') is not True:
sql_where = ' WHERE d.username = %s' % web.sqlquote(admin)
# RAW sql command used to get records.
rawSQLOfRecords = """
select
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 a
left join (select domain,count(*) alias_count from alias group by domain) 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) c on a.domain=c.domain
left join domain_admins d on
d.domain=a.domain
%s
group by a.domain
order by a.domain
LIMIT %d
OFFSET %d
""" % (sql_where, settings.PAGE_SIZE_LIMIT, (page-1) * settings.PAGE_SIZE_LIMIT,)That should fix the issue.
----
Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.
