Paolo, > >> Why domain in maddr table is in 'subdomain fields in reverse' format? > > To facilitate producing a quick by-domain summary report by a single > > SELECT.
> I think this kind of query is not in the documentation, right? The schema is documented, the rest is up to your needs, imagination and documentation on SQL language. > Do you have any ready-to-use examples for this kind of reports? One general example of a sophisticated SELECT is near the end of README.sql. If you don't already have this key indexed, this might be beneficial for queries on maddr.domain: CREATE INDEX maddr_idx_domain ON maddr (domain); Here are some example on using domain-in-reverse field: # clean messages ordered by count, grouped by domain: SELECT count(*) as cnt, avg(bspam_level), sender.domain FROM msgs LEFT JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id WHERE content='C' GROUP BY sender.domain ORDER BY cnt DESC LIMIT 50; # most spamy domains, sorted by spam average, grouped by domain: SELECT count(*) as cnt, avg(bspam_level) as spam_avg, sender.domain FROM msgs LEFT JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id GROUP BY sender.domain ORDER BY spam_avg DESC LIMIT 50; # sender domains with >100 messages, sorted on sender.domain: SELECT count(*) as cnt, avg(bspam_level) as spam_avg, sender.domain FROM msgs LEFT JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id GROUP BY sender.domain HAVING cnt > 100 ORDER BY sender.domain DESC LIMIT 100; ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 _______________________________________________ AMaViS-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
