|
From: SourceForge.net <no...@so...> - 2008-06-10 00:24:22
|
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-10 02:24 Message: Logged In: YES user_id=142401 Originator: NO hi christian, the following modified query would ensure that we always get the desired row: SELECT alias.goto FROM alias LEFT JOIN alias_domain ON alias_domain.alias_domain = '%d' AND alias.domain = alias_domain.target_domain WHERE alias.active = 1 AND ( alias.address='%s' OR (alias_domain.alias_domain='%d' AND alias.address=CONCAT('%u','@',alias_domain.target_domain)) ) ORDER BY alias_domain.alias_domain ASC LIMIT 1 extending the join-condition with "AND alias.domain = alias_domain.target_domain" will return a NULL-value for the "alias_domain.alias_domain" column in a row matched by the condition "alias.address='%s'". to explain this with your example (us...@fo... -> use...@ex...): there would be one row with alias.address='us...@fo...', alias.domain='foo.de' alias_domain.alias_domain=NULL alias_domain.target_domain=NULL for this row alias_domain.alias_domain='foo.de' would be matched by the first ON-condition, but alias_domain.target_domain='bar.de' is != alias.domain, so no join and therefor NULL-values in alias_domain.* next to this there would be another row with alias.address='us...@ba...' alias.domain='bar.de' alias_domain.alias_domain='foo.de' alias_domain.target_domain='bar.de' since the NULL-value orders in front of 'foo.de', "LIMIT 1" will return only the first row, which is the one we're looking for. i already studied the feature-request you mentioned, but as long as wietse/ the postfix-docs don't recommend to return multiple rows for alias-lookups i would personally advice against doing so. of course you're right that this query wouldn't work with such a configuration. i'll have a look at "virtual_mailbox_maps" and do some testing inside postfix the next days :-) sorry for posting the last message twice btw. // lenix ---------------------------------------------------------------------- Comment By: Christian Boltz (christian_boltz) Date: 2008-06-10 00:40 Message: Logged In: YES user_id=593261 Originator: YES I'd say I know MySQL quite good, but don't consider myself to be an expert ;-) I would not rely on the sort order and do a LIMIT 1. Unless we specify an ORDER BY, the results are probably sorted by creation time, which is as acceptable as ORDER BY random()... In theory we could use something like SELECT goto, 1 as rank FROM alias UNION SELECT ... AS goto, 2 as rank ORDER BY rank LIMIT 1 but it has some disadvantage: * It will return an additional column to postfix, and I have no idea how postfix behaves in this case. (Additionally the query above is untested, but I hope it should work this way.) * We have a feature request to split the goto field to one address per row, which means there could be multiple results (I hope the requester tested the postfix behaviour, I didn't ;-) - LIMIT 1 would be deadly after this change. I'm afraid the only clean solution is doing this inside MySQL. This reduces the traffic between Postfix and MySQL to a single query. For some example SQL code, see https://listi.jpberlin.de/pipermail/postfixbuch-users/2007-November/039627.html People who use older MySQL versions (MySQL 4 and older) could still use 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 |
