-----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/Mgwy6JZxCF9q4y83EuQCe
LBBw
wh0I/HHcGhPrbJnemUD50ig=
=NjdV
-----END PGP SIGNATURE-----
_______________________________________________
DBmail mailing list
DBmail dbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
