[ postfixadmin-Feature Requests-1973673 ] merge SQL queries for alias domains

[SourceForge.net <no...@so...>]

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