List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:June 3 2010 1:41pm
Subject:RE: Slow when using sub-query
View as plain text  
>-----Original Message-----
>From: vegivamp@stripped [mailto:vegivamp@stripped] On Behalf Of Johan De
>Meersman
>Sent: Thursday, June 03, 2010 6:52 AM
>To: jerry@stripped
>Cc: mysql@stripped
>Subject: Re: Slow when using sub-query
>
>The short answer is that the optimizer is amazingly stupid about subqueries,
>and it assumes that they are dependent even when they're not - that subquery
>gets executed for every row in your main query.
>
[JS] That's what I figured was happening -- either that, or my CPU was on 
strike for higher voltages.

>The fastest way to do this, would probably be to run your subquery, have
>your code assemble the appropriate IN clause, and then run your main query.
>
[JS] A lot of what I do is one-off things, and I usually wind up using the 
CLI. Rewriting the whole business as a JOIN to a derived table worked very 
well.

>The long answer is that there's a rather good Advanced Tuning course that
>
[JS] No doubt. Our database is so small, and my duties are so diverse, that 
tuning only gets my attention when a problem interferes with other people's 
productivity.

Thanks.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com




Thread
Slow when using sub-queryJerry Schwartz2 Jun
  • Re: Slow when using sub-queryJohan De Meersman3 Jun
    • RE: Slow when using sub-queryJerry Schwartz3 Jun