|
From: SourceForge.net <no...@so...> - 2008-06-08 23:05:31
|
Feature Requests item #1973673, was opened at 2008-05-27 01:04 Message generated for change (Comment added) made by lenix You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937967&aid=1973673&group_id=191583 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: SVN (please specify revision!) Status: Open Resolution: None Priority: 5 Private: No Submitted By: Christian Boltz (christian_boltz) Assigned to: Nobody/Anonymous (nobody) Summary: merge SQL queries for alias domains Initial Comment: >From POSTFIX_CONF.txt: virtual_alias_maps = proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf, proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_maps.cf, proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_catchall_maps.cf virtual_mailbox_maps = proxy:mysql:/etc/postfix/sql/mysql_virtual_mailbox_maps.cf, proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_mailbox_maps.cf This means MySQL is called twice for mailboxes and three times for aliases (except if the first try returns a result, of course). If possible, we should merge the queries so that alias and mailbox queries always only need one map. SELECT ... UNION SELECT ... UNION SELECT ... doesn't look very nice, but it has a better performance than doing two or three separate queries. Or is there a special reason why we should do separate queries? ---------------------------------------------------------------------- Comment By: Guido Boehm (lenix) Date: 2008-06-09 01:05 Message: Logged In: YES user_id=142401 Originator: NO with the query i mentioned your testcase would result in 2 rows being returned. at least for me the first result was always the one where alias.address='%s' matched, so one could just append "LIMIT 1", but i'm not sure how consistent this is, maybe there's some mysql-professional who could tell? // lenix ---------------------------------------------------------------------- Comment By: Guido Boehm (lenix) Date: 2008-06-09 00:59 Message: Logged In: YES user_id=142401 Originator: NO with the query i mentioned your testcase would result in 2 rows being returned. at least for me the first result was always the one where alias.address='%s' matched, so one could just append "LIMIT 1", but i'm not sure how consistent this is, maybe there's some mysql-professional who could tell? // lenix ---------------------------------------------------------------------- Comment By: Christian Boltz (christian_boltz) Date: 2008-06-09 00:35 Message: Logged In: YES user_id=593261 Originator: YES To make things more interesting: We still allow to have separate aliases on a domain that is defined as alias domain. This is not really bad (and might even be useful in some cases), but it might lead to interesting ;-) setups. For example, there could be the following setup: alias domain: foo.de -> bar.de alias: us...@ba... -> use...@ex... alias: us...@fo... -> use...@ex... In this case, I would expect that mails sent to us...@fo... are forwarded to user-foo@, not user-bar@. With the separate queries, it should work - but I'm not sure about the merged one. Please add such a setup to your testcase ;-) If it turns out that merging the queries is too complicated, creating a function inside MySQL could be an option for those who want better performance. Users who don't want to do this (or can't because they still use MySQL 4) could still use the separated queries. ---------------------------------------------------------------------- Comment By: Guido Boehm (lenix) Date: 2008-06-08 20:02 Message: Logged In: YES user_id=142401 Originator: NO hi christian, after re-thinking the way postfix does lookups it appears to me it already does run 2 queries per item in 'virtual_alias_maps': - a first try for exact matches (looking up the key 'bo...@ex...') - if the first try didn't give any results a second try for catchall (looking up the key '@example.com') based on this assumption i created the following query which should be able to replace the 3 virtual_alias_maps-queries in the current setup: SELECT alias.goto FROM alias LEFT JOIN alias_domain ON alias_domain.alias_domain = '%d' WHERE alias.active = 1 AND ( alias.address='%s' OR (alias_domain.alias_domain='%d' AND alias.address=CONCAT('%u','@',alias_domain.target_domain)) ) although i did run a set of test-queries against my own database which looked promising i don't have the time to extensivly test it inside my postfix-setup right now. i'll try to do this next week and keep you updated. thanks for your patience, lenix ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=937967&aid=1973673&group_id=191583 |
