1

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.

2

Re: Inefficient query on Domains and Account page

Committed into both open source edition and Pro edition, thanks very much. smile