Mailing List Archive: DBMail: users

SQL query for DBMail 2.2.x status reports

Index | Next | Previous | View Threaded

rabbit+list at rabbit

Feb 9, 2010, 3:37 AM

Post #1 of 2 (424 views)
Permalink

SQL query for DBMail 2.2.x status reports

I needed an efficient way to present an overview of the current state of
the database. I came up with these 2 queries, figured they may be useful
for someone else too. Performance is rather good, it takes me 1.3 secs
for the outer query on ~1mil phys messages with ~40 users. Formatting in
the select list is mysql-specific and can of course be changed/removed:

The first query is where the actual aggregation takes place (and the one
that takes the 1.3 secs):

SELECT
b.owner_idnr AS `user_idnr`,
COUNT(m.message_idnr) AS `msg_count`,
MAX(m.message_idnr) AS `last_msg_id`,
COUNT(m2.message_idnr) AS `unread_count`,
MIN(m2.message_idnr) AS `first_unread_id`
FROM dbmail_mailboxes b
JOIN dbmail_messages m
ON ( m.mailbox_idnr = b.mailbox_idnr AND m.status <= 1 )
LEFT JOIN dbmail_messages m2
ON ( m.message_idnr = m2.message_idnr AND m2.seen_flag = 0 )
GROUP BY b.owner_idnr

This is the reporting query which relies on the aggregator
(for readability assume that the aggregator is saved as a view STATS):

SELECT
RIGHT( a.alias, LENGTH(a.alias) - LOCATE('@',a.alias) ) AS `Domain`,
a.alias AS `Address`,
IFNULL( u.userid, a.deliver_to ) AS `Deliver To (username or forward)`,
IFNULL( DATE(u.last_login),'N/A' ) AS `User Last Login`,
LPAD( IFNULL( FORMAT(s.msg_count,0),'N/A' ), 14, ' ') AS `Total Messages`,
IFNULL( DATE(p_last.internal_date),'N/A' ) AS `Most Recent Message`,
LPAD( IFNULL( FORMAT(s.unread_count,0), 'N/A' ), 14, ' ') AS `Unread Messages`,
IFNULL( DATE(p_unread.internal_date), 'N/A' ) AS `Oldest Unread Message`
FROM dbmail_aliases a
LEFT JOIN dbmail_users u
ON a.deliver_to = u.user_idnr
LEFT JOIN STATS s
ON u.user_idnr = s.user_idnr
LEFT JOIN dbmail_messages m_last
ON s.last_msg_id = m_last.message_idnr
LEFT JOIN dbmail_physmessage p_last
ON p_last.id = m_last.physmessage_id
LEFT JOIN dbmail_messages m_unread
ON s.first_unread_id = m_unread.message_idnr
LEFT JOIN dbmail_physmessage p_unread
ON p_unread.id = m_unread.physmessage_id
ORDER BY Domain, ISNULL(u.user_idnr),a.alias

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 9, 2010, 3:54 AM

Post #2 of 2 (404 views)
Permalink

Re: SQL query for DBMail 2.2.x status reports [In reply to]

Pretty cool!

385 rows in set (0.23 sec)
917 rows in set (0.25 sec)

Here are 800 aliases and 90.000 messages in 2.847.697 records

Am 09.02.2010 12:37, schrieb Peter Rabbitson:
> I needed an efficient way to present an overview of the current state of
> the database. I came up with these 2 queries, figured they may be useful
> for someone else too. Performance is rather good, it takes me 1.3 secs
> for the outer query on ~1mil phys messages with ~40 users. Formatting in
> the select list is mysql-specific and can of course be changed/removed:
>
> The first query is where the actual aggregation takes place (and the one
> that takes the 1.3 secs):
>
> SELECT
> b.owner_idnr AS `user_idnr`,
> COUNT(m.message_idnr) AS `msg_count`,
> MAX(m.message_idnr) AS `last_msg_id`,
> COUNT(m2.message_idnr) AS `unread_count`,
> MIN(m2.message_idnr) AS `first_unread_id`
> FROM dbmail_mailboxes b
> JOIN dbmail_messages m
> ON ( m.mailbox_idnr = b.mailbox_idnr AND m.status <= 1 )
> LEFT JOIN dbmail_messages m2
> ON ( m.message_idnr = m2.message_idnr AND m2.seen_flag = 0 )
> GROUP BY b.owner_idnr
>
> This is the reporting query which relies on the aggregator
> (for readability assume that the aggregator is saved as a view STATS):
>
> SELECT
> RIGHT( a.alias, LENGTH(a.alias) - LOCATE('@',a.alias) ) AS `Domain`,
> a.alias AS `Address`,
> IFNULL( u.userid, a.deliver_to ) AS `Deliver To (username or forward)`,
> IFNULL( DATE(u.last_login),'N/A' ) AS `User Last Login`,
> LPAD( IFNULL( FORMAT(s.msg_count,0),'N/A' ), 14, ' ') AS `Total Messages`,
> IFNULL( DATE(p_last.internal_date),'N/A' ) AS `Most Recent Message`,
> LPAD( IFNULL( FORMAT(s.unread_count,0), 'N/A' ), 14, ' ') AS `Unread Messages`,
> IFNULL( DATE(p_unread.internal_date), 'N/A' ) AS `Oldest Unread Message`
> FROM dbmail_aliases a
> LEFT JOIN dbmail_users u
> ON a.deliver_to = u.user_idnr
> LEFT JOIN STATS s
> ON u.user_idnr = s.user_idnr
> LEFT JOIN dbmail_messages m_last
> ON s.last_msg_id = m_last.message_idnr
> LEFT JOIN dbmail_physmessage p_last
> ON p_last.id = m_last.physmessage_id
> LEFT JOIN dbmail_messages m_unread
> ON s.first_unread_id = m_unread.message_idnr
> LEFT JOIN dbmail_physmessage p_unread
> ON p_unread.id = m_unread.physmessage_id
> ORDER BY Domain, ISNULL(u.user_idnr),a.alias
>
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

--

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

Attachments:

signature.asc (0.25 KB)

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads