Re: [Exim] slow mysql query in exim

| 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

you have right. the second query runs quicker... but not enough:

select distinct domain from mail where domain="xy.com"
1st run: 1 row in set (15.37 sec)
2nd run: 1 row in set (3.13 sec)
3rd run: 1 row in set (7.16 sec)

and: select domain from mail where domain="xy.com" limit 1;
everytimes: <0.5 sec

| alternatively, what's the load on the mysql db (and the machine on which
| it resides)? that could have an effect.

high... >40% (checked with "top")
1000-2000 queries/sec

| 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).

yes. distinct will group all records, but the result of the
query is only the domain self.
i think this query only checks for that the domain exist or not.
so with the first result can i do the same.

because the query is: get me the domain_field where domain_field="xy.com"
if the domain exists then i get the domain (why not the first what i find)

(i have indexes on domain, user, and alias fields.)

|
| 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.

cheers,

ati