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
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
