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
> [email protected]
> 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/
signature.asc
Description: OpenPGP digital signature
_______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
