Hi, We build a clustered mail system with postfix, amavisd, clamd, SA, dovecot, mysql, etc. 2 Servers are handling incoming mail (MX records pointing to us) and 2 are handling outgoing mail (relaying). mysql database is stored on 2 master-master replicated servers.
so far, everything is working well. but we have problems with the sql_lookup from amavisd-new that we need to lookup per recipient whitelisting. $sql_select_policy = 'select amavis_policy.*,amavis_recipients.id as id '. 'FROM (hostings,domains,amavis_recipients,products,amavis_policy) LEFT JOIN (users,accounts) '. 'ON accounts.hosting_id=hostings.id AND users.account_id=accounts.id '. 'WHERE (amavis_recipients.hosting_id = hostings.id OR amavis_recipients.account_id = accounts.id) '. 'AND domains.hosting_id=hostings.id '. 'AND hostings.product_id = products.id '. 'AND hostings.amavis_policy_id = amavis_policy.id '. 'AND ('. ' (is_mailbox=1 AND concat(username,\'@\',domainname) IN (%k)) '. ' OR ((is_transport=1 OR is_mxbackup=1) AND concat(\'@\',domainname) IN (%k))'. ')'; $sql_select_white_black_list = 'SELECT wb '. 'FROM amavis_wblist '. 'WHERE recipient_id=? '. 'AND addr IN (%k)'; The DB layout is like this: one account can have multiple usernames (aliases), one hosting can have multiple domains, one hosting can have multiple accounts. that means, that we don't have a email address field. so we have to merge (concat) domainname and username field to check the %k strings. we also need per recipient email address and domain lookup, not just email address. because we also do something like email proxy, where we don't know the usernames, just the domains for a customer. the query takes about 8 seconds with 3500 usernames and 400 domains. is there a way to change the query like this (separate variables in the query for domain and username)? select amavis_policy.*,amavis_recipients.id as id FROM (hostings,domains,amavis_recipients,products,amavis_policy) LEFT JOIN (users,accounts) ON accounts.hosting_id=hostings.id AND users.account_id=accounts.id WHERE (amavis_recipients.hosting_id = hostings.id OR amavis_recipients.account_id = accounts.id) AND domains.hosting_id=hostings.id AND hostings.product_id = products.id AND hostings.amavis_policy_id = amavis_policy.id AND ( (is_mailbox=1 AND username='<<<<username>>>>' and domainname='<<<<domain>>>>') OR ((is_transport=1 OR is_mxbackup=1) AND domainname='<<<<domain>>>>')); this query is executed in ~0.04 sec... what would be okay... thanks for any ideas... thanks and best regards, Marco ------------------------------------------------------------------------------ This SF.net Dev2Dev email is sponsored by: Show off your parallel programming skills. Enter the Intel(R) Threading Challenge 2010. http://p.sf.net/sfu/intel-thread-sfd _______________________________________________ AMaViS-user mailing list AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user Please visit http://www.ijs.si/software/amavisd/ regularly For administrativa requests please send email to rainer at openantivirus dot org