Paul,
Paul J Stevens schrieb:
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.
I do not have performance problems. Only, I saw it on my mysql log with
"log-queries-not-using-indexes" enabled. So, I investigated it.
Uwe
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
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
