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? SELECT type,email FROM lists,aliases WHERE (lists.uid = 'user@example.com' or (lists.uid=aliases.uid and aliases.alias='user@example.com')); -- My Main Blog http://etbe.coker.com.au/ My Documents Blog http://doc.coker.com.au/
Have you tried using a union like: SELECT type,email FROM lists,aliases WHERE lists.uid='user@example.com' UNION SELECT type,email FROM lists,aliases WHERE aliases.alias='user@example.com' and lists.uid=aliases.uid; Or (unless I have the wrong join) SELECT type,email FROM lists LEFT JOIN aliases ON lists.uid=aliases.uid WHERE lists.uid='user@example.com' OR aliases.alias='user@example.com'; Sent from my iPad On 06/05/2012, at 5:12 PM, Russell Coker <russell@coker.com.au> 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;
On Sun, 6 May 2012, mark Johnson <mark@chronopia.net> wrote:
Have you tried using a union like:
SELECT type,email FROM lists,aliases WHERE lists.uid='user@example.com' UNION SELECT type,email FROM lists,aliases WHERE aliases.alias='user@example.com' and lists.uid=aliases.uid;
Or (unless I have the wrong join) SELECT type,email FROM lists LEFT JOIN aliases ON lists.uid=aliases.uid WHERE lists.uid='user@example.com' OR aliases.alias='user@example.com';
Thanks for the suggestions. The union works, but I'd prefer to have a join if possible as the SQL statement I provided to the list was a simplified version of the real thing. The real statement is long enough that putting it in twice would be bad for code maintenance. The JOIN that you suggested unfortunately has the performance problem. -- My Main Blog http://etbe.coker.com.au/ My Documents Blog http://doc.coker.com.au/
On Sun, 6 May 2012 05:49:37 pm Russell Coker wrote:
Thanks for the suggestions. The union works, but I'd prefer to have a join if possible as the SQL statement I provided to the list was a simplified version of the real thing. The real statement is long enough that putting it in twice would be bad for code maintenance.
The JOIN that you suggested unfortunately has the performance problem.
These sorts of performance issues are a bit of a black art. You need to match the structure of the indexes with the query to coax the query planner to do what you expect. If this were Postgres there would be an EXPLAIN statement to explain what the query planner is thinking. Look for similar tools with MySQL. -- Anthony Shipman Mamas don't let your babies als@iinet.net.au grow up to be outsourced.
Hi Anthony ----- Original Message -----
On Sun, 6 May 2012 05:49:37 pm Russell Coker wrote:
Thanks for the suggestions. The union works, but I'd prefer to have a join if possible as the SQL statement I provided to the list was a simplified version of the real thing. The real statement is long enough that putting it in twice would be bad for code maintenance.
The JOIN that you suggested unfortunately has the performance problem.
These sorts of performance issues are a bit of a black art. You need to match the structure of the indexes with the query to coax the query planner to do what you expect. If this were Postgres there would be an EXPLAIN statement to explain what the query planner is thinking. Look for similar tools with MySQL.
Ya, you use the EXPLAIN statement ;-) 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/
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
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/
On Tue, 8 May 2012, Arjen Lentz <arjen@lentz.com.au> wrote:
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.
Thanks for the suggestion, I tried a join and it didn't do much good. I've attached the result of the explain commands. A default join sorts through 18M rows. A right join is better and only sorts through 1M rows but gets the data four times (that account has four aliases) which adds to processing for the program that receives it. I think that perhaps I should coalesce the results from the aliases table before checking against lists, but I can't figure out how to do that. Any further suggestions? -- My Main Blog http://etbe.coker.com.au/ My Documents Blog http://doc.coker.com.au/
Hi Russell
On Tue, 8 May 2012, Arjen Lentz <arjen@lentz.com.au> wrote:
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.
Thanks for the suggestion, I tried a join and it didn't do much good. I've attached the result of the explain commands. A default join sorts through 18M rows. A right join is better and only sorts through 1M rows but gets the data four times (that account has four aliases) which adds to processing for the program that receives it.
I think that perhaps I should coalesce the results from the aliases table before checking against lists, but I can't figure out how to do that.
Any further suggestions?
The EXPLAINs indicate the 'index" access type which is an index scan. Not what you want. But it's a consequence of doing what you do, which is ignoring the advice that you were given: use a UNION to do this construct. Regards, 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/
On Tue, 8 May 2012 12:10:15 pm Russell Coker wrote:
Thanks for the suggestion, I tried a join and it didn't do much good. I've attached the result of the explain commands. A default join sorts through 18M rows. A right join is better and only sorts through 1M rows but gets the data four times (that account has four aliases) which adds to processing for the program that receives it.
Going on a slight tangent, if a query could result in the same record multiple times you can use the DISTINCT keyword (ie "SELECT DISTINCT ... FROM ..). Of course this will only work if you're only returning data from the main record not different join data in the SELECT field set.
Hi Mark
Going on a slight tangent, if a query could result in the same record multiple times you can use the DISTINCT keyword (ie "SELECT DISTINCT ... FROM ..). Of course this will only work if you're only returning data from the main record not different join data in the SELECT field set.
Commonly, perceived need for DISTINCT tends to indicate a flawed query construct. 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/
On Tue, 8 May 2012 12:35:39 pm Arjen Lentz wrote:
Commonly, perceived need for DISTINCT tends to indicate a flawed query construct.
Agreed, but there are exceptions to every rule! While I do use this myself I do try to resolve the cause first - often it's possible, but sometimes there's no simple (or efficient) solution. Plus you can always implement DISTINCT but put it on a todo list to analyze and rework the query later if you're really time constrained.
participants (5)
-
Anthony Shipman -
Arjen Lentz -
Craig Sanders -
mark Johnson -
Russell Coker