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.
The fastest way to do this, would probably be to run your subquery, have
your code assemble the
-Original Message-
From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De
Meersman
Sent: Thursday, June 03, 2010 6:52 AM
To: je...@gii.co.jp
Cc: mysql@lists.mysql.com
Subject: Re: Slow when using sub-query
The short answer is that the optimizer is amazingly stupid
I've heard that sub-queries aren't well-optimized, but this case seems
ridiculous.
First, a little setup:
SELECT pub_id FROM pub WHERE pub_code = 'GD' INTO @P;
=== Inner Query by Itself ===
us-gii SELECT prod_pub_prod_id FROM prod
- WHERE pub_id = @P
- AND prod_discont = 0
- GROUP
