-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
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?
|
Uwe,
adding this index changes a scan of every row of the table into a scan
~ of every row of the index, so although IO will be a bit faster it will
not really improve things by an order of magnitude.
Removing all the "lower" functions would be a performance improvement
since it would allow an exact match on the index. However, in this
~ case there is an existing index (alias) which would be almost as
good as yours. Not sure it would be worthwhile to make another
index since "<>" will not benefit from the index, even though IO
will be just slightly faster since the table will not have to be accessed.
I don't know why the lower function has been used. In mysql columns
~ are usually case insensitive unless you define them not to be or use a
case sensitive colation, so the query would work equally well without
lower(). Maybe in the other supported databases it is different
so they are there for compatibility.
John
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4-svn0 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org
iD8DBQFH76Ned4I3jTtt9EIRAkpyAKDDMceDQR/Mgwy6JZxCF9q4y83EuQCeLBBw
wh0I/HHcGhPrbJnemUD50ig=
=NjdV
-----END PGP SIGNATURE-----
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
