[Vmail-discuss] SQL speed-ups

[Paul Makepeace]

I've been poking about with the vmail package this evening and noticed a
few things that I think could be sped up quite a bit.

For queries like,

    select mbox_name from popbox left join domain_alias
        on domain_alias.domain_name = popbox.domain_name
     where local_part = '${quote_mysql:$local_part}'
       and (popbox.domain_name = '${quote_mysql:$domain}'
                      or alias = '${quote_mysql:$domain}')

As far as I can tell from playing with indexes and MySQL's EXPLAIN these
are, as it stands, full table scans. In other words it's looking at as
many rows as there are in popbox * domain_alias. This will really blow
up for heavy users.

By creating an index on popbox.local_part and domain_alias.domain_name
MySQL's optimiser seems to be able to figure out a much better plan.
I think the primary key consisting of a combination of a bit of the
local_part and domain_name isn't used, although I didn't check
every query.

**

I think each of the queries could use a LIMIT 1 - an identical result is
returned for each domain alias.

**

If I was implementing a system like this I would be inclined to shorten
the local part and domains to make the indexing a bit more manageable.
Note that if domain_name is shortened in one table it needs to be the
same type&length in the others too (this is a restriction wrought by the
optimiser, afaict). I wonder what the effect of normalising the domains
by using a domain id would be.

Disclaimer: I am no MySQL expert and most of what I've figured out has
been from scratch tonight (give/take some Oracle in another life) so
independent teams of open source adjudicators should be sought :-)

HTH anyway,
Paul


-- 
Paul Makepeace ....................................... http://paulm.com/

"If don't have to be heartless, then the moon will glow brightly."
   -- http://paulm.com/toys/surrealism/