Uwe,
I was wondering what ticks you off here. Are you seriously having
performance issues here? As far as I know this particular query is
mostly used during delivery (dbmail-lmtpd/dbmail-smtp). I don't see how
this would bite you unless you're doing very high delivery rates.
Uwe Kiewel wrote:
> Hi there,
>
> probably I found a missing index on table dbmail_aliases in dbmail-2.2.8.
>
> mysql> explain SELECT deliver_to FROM dbmail_aliases WHERE lower(alias)
> = lower('XXXXXXX') AND lower(alias) <> lower(deliver_to);
> +----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
> | 1 | SIMPLE | dbmail_aliases | ALL | NULL | NULL |
> NULL | NULL | 20 | Using where |
> +----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
>
> a "show index from dbmail_aliases;" shows, there is no index related to
> column deliver_to.
>
> After issuing "create index deliver_to_index on dbmail_aliases
> (deliver_to, alias);", mysql explain says:
>
> explain SELECT deliver_to FROM dbmail_aliases WHERE lower(alias) =
> lower('XXXXXXX') AND lower(alias) <> lower(deliver_to);
> +----+-------------+----------------+-------+---------------+------------------+---------+------+------+--------------------------+
> | id | select_type | table | type | possible_keys |
> key | key_len | ref | rows | Extra |
> +----+-------------+----------------+-------+---------------+------------------+---------+------+------+--------------------------+
> | 1 | SIMPLE | dbmail_aliases | index | NULL |
> deliver_to_index | 1054 | NULL | 20 | Using where; Using index |
> +----+-------------+----------------+-------+---------------+------------------+---------+------+------+--------------------------+
>
>
> Another way to optimize the "select deliver_to query" is to drop-off the
> "lower" function. In that case, you have to make sure, that all letters
> are lowered before using the sql statement.
>
>
> Well, my point of view is the database. Maybe there are more important
> reasons in the program code to go this way.
>
> How dou you think about?
>
>
> Regrads,
> Uwe
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> DBmail mailing list
> [email protected]
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
