James Nobis <[EMAIL PROTECTED]> wrote on 04/19/2005
04:04:24 PM:
> The link below essentially means subquery support is useless for many
> traditional purposes. You are stuck in a correlated subquery thus
performance
> is the query being run out-to-in (subquery run for every row?) instead
of
> in-to-out as desired. Does anyone know of a work around for this?
Apparantly
> the MySQL team refuses to even recognize this as a bug.
>
> http://bugs.mysql.com/bug.php?id=9090
>
> James Nobis
> Web Developer
> Academic Superstore
> 223 W. Anderson Ln. Suite A110, Austin, TX 78752
> Voice: (512) 450-1199 x453 Fax: (512) 450-0263
> http://www.academicsuperstore.com
>
No, it just means that you are going to have to do some of the work by
creating and destroying your own temp table. Here is a query similar to
the one in the bug report that doesn't use any subqueries.
CREATE TEMPORARY TABLE tmpIDs
SELECT werber, count(1) freq
FROM table_3
GROUP BY werber
HAVING freq > 20;
SELECT *
FROM table_2 t2
INNER JOIN tmpIDs tmp
ON tmp.id = t2.id
LEFT JOIN table_1 t1
on t1.id = t2.id;
DROP TEMPORARY TABLE tmpIDs;
Sure, subqueries may be sub-optimal ways of forming certain queries in
MySQL. I expect that from newer features. That doesn't mean they are
broken (bugged). It just means that more work needs to happen to make them
perform better. I agree with the analysis that this is a feature request
("please optimize subquery evaluation...here is a test case ....") rather
than a bug. Until they get better, you may have to use the "old" way of
computing those results (you know, the way we did it BEFORE subqueries
were ever supported) and go through temp tables in order to get the
performance you desire.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
