On Sun, May 06, 2012 at 05:12:48PM +1000, Russell Coker wrote:
SELECT type,email FROM lists,aliases WHERE lists.uid='user@example.com';
SELECT type,email FROM lists,aliases WHERE aliases.alias='user@example.com' and lists.uid=aliases.uid;
The above MySQL queries return results almost instantly. The below query takes ages but it should just give the union of the results of both the above queries. How can I get the result of the above two queries with a single query that doesn't take ages? I guess that the problem is that it's looking up the aliases table for every row in the lists table. How can I stop it doing that?
use a join? maybe add indexes?
SELECT type,email FROM lists,aliases WHERE (lists.uid = 'user@example.com' or (lists.uid=aliases.uid and aliases.alias='user@example.com'));
how does a join perform, something like: SELECT type,email FROM lists JOIN aliases ON lists.uid=aliases.uid WHERE lists.uid = 'user@example.com' ; (you might need to specify the table name(s) for type and email depending on whether they are ambiguous (the fields appear in both tables) or not. craig -- craig sanders <cas@taz.net.au> BOFH excuse #444: overflow error in /dev/null