>-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf Of Johan De >Meersman >Sent: Thursday, June 03, 2010 6:52 AM >To: [email protected] >Cc: [email protected] >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 >addresses all this and more, as does Baron & C° 's excellent MySQL book. > [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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[email protected]
