There are several possible reasons the query is performing slowly, i've
outlined my initial thoughts below. disclaimer - i'm neither an mysql or
exim backend db expert by any means!
firstly (and possibly the most likely), the first query could be
fetching the results into cache, and enabling the second to run much
quicker. If you get a similair time *every* time you run the first
query, then there's definately some mismatch going on. try different
where clauses. try running the two queries again. what sort of results
are there.
alternatively, what's the load on the mysql db (and the machine on which
it resides)? that could have an effect.
I'm not a mysql expert, but the "DISTINCT" operator will be attempting
to group all the results and then sorting out the distincts. the
optimiser might be attempting to do a tablescan (ie. read every record)
on the table, hence the time it's taking (this could be likely if
there's no index on the MYSQL_DOMAINFIELD column, so no quick way of
finding the record). the indexes on the table might not be appropriate
(ie. this field is not available for use).
Your query is simply stating "return the first result you find that
matches", which is different from DISTINCT beneath the hood (return
unique values). if that's *always* going to be ok, then fine. stick with
it (which would appear to be the case if you're selecting the only field
you're querying, with a specific where clause)
alteratively, and to improve the performance if this is a very common
query, check the indexing on MYSQL_AUTHTABLE. slap an index on the
MYSQL_DOMAINFIELD column and see how it performs. but remember, too many
indexes on a table means performance might start to suffer (another
index requires maintenance).
anyway, must dash.
regards,
richard.
Attila Soki wrote:
>hi,
>
>i use exim 3.33 with mysql and i have found a slow, unoptimized query in my
>config file.
>
>MYSQL_DOMAIN = SELECT DISTINCT MYSQL_DOMAINFIELD FROM MYSQL_AUTHTABLE WHERE
>MYSQL_DOMAINFIELD='$key'
>
>returns 1 record with $key. (12.67 sec)
>
>my query returns the same record but in "0.00 sec"
>
>MYSQL_DOMAIN = SELECT MYSQL_DOMAINFIELD FROM MYSQL_AUTHTABLE WHERE
>MYSQL_DOMAINFIELD='$key' LIMIT 1
>
>
>is my query O.K. or is there any reason why not.
>
>thanks
>
>Attila Soki
>
>
>
>
>Examples:
>mysql> select domain from mail where domain="xy.com" limit 1;
>+-----------+
>| domain |
>+-----------+
>| xy.com |
>+-----------+
>1 row in set (0.00 sec)
>
>mysql> select distinct domain from mail where domain="xy.com";
>+-----------+
>| domain |
>+-----------+
>| xy.com |
>+-----------+
>1 row in set (12.67 sec)
>
>mysql> select count(*) from mail;
>+----------+
>| count(*) |
>+----------+
>| 429657 |
>+----------+
>1 row in set (0.00 sec)
>
>
>--
>## List details at http://www.exim.org/mailman/listinfo/exim-users Exim details at http://www.exim.org/ ##
>
--
Richard Leyton - Senior Consultant, Paremus Limited
http://www.paremus.com
mailto:richard.leyton@paremus.co.uk
