Hi Craig, Russell ----- Original Message -----
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?
There's a join in there already, as indicated by the comma in the table list. A comma does an inner join, and the join condition then needs to be specified in the WHERE clause. It's a common/old way of phrasing joins, and I tend to advise developers to use explicit JOINs (SELECT ... a JOIN b ON (condition) ...) as they're intrinsically clearer and prevent mishaps. Functionally they're the same.
SELECT type,email FROM lists,aliases WHERE (lists.uid = 'user@example.com' or (lists.uid=aliases.uid and aliases.alias='user@example.com'));
This is fairly gross, sticking a join condition in an OR clause. Since it's an OR, the server has to try both anyway (it has to return all matching rows, not just the first). The perceived advantage very likely does not exist, if you run through the necessary relational operations logically. For this type of construct, a UNION is generally the appropriate route. You should index lists.uid, aliases.alias, and lists.uid Since the server has to match the alias field anyway, it's most likely to first do that then join on to the lists table. This does depend a bit on the number of rows in the table and the selectivity and cardinality of the fields, but as a general guide it should work. Also, if you're just building the system, some of the tables may be small and in those cases the optimiser often just scans the table rather than taking the intermediate step of using an index - so running EXPLAIN is not going to tell you now what would happen once the tables grow. Anyway, do as I described above and I reckon you'll be right. I've constructed similar things for Postfix/Dovecot with MySQL, and indeed used a UNION. Make sure you have the query cache enabled (even if the tables often change), as Postfix is hideously inefficient and often does the same query a handful of times in short succession. Cheers, Arjen. -- Exec.Director @ Open Query (http://openquery.com) MySQL services Sane business strategy explorations at http://Upstarta.biz Personal blog at http://lentz.com.au/blog/