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
