Appion

h1. Mysql Nested Subqueries Problem

This is the query that I wanted to do because it is easy to understand (= easier to maintain).

Unfortunatly there is a bug in mysql (http://bugs.mysql.com/bug.php?id=10312) which makes this 

so slow that no one has seen it complete. The subsequent query is a bit more difficult to follow

but gets around this problem. 

<pre>

SELECT ".DB_PROJECT.".projectexperiments.projectId 

FROM ".DB_PROJECT.".projectexperiments 

WHERE ".DB_PROJECT.".projectexperiments.name IN 

( 

    SELECT ".DB_LEGINON.".SessionData.name 

    FROM ".DB_LEGINON.".SessionData 

    WHERE ".DB_LEGINON.".SessionData.`DEF_id` IN 

    ( 

        SELECT ".DB_PROJECT.".shareexperiments.`REF|leginondata|SessionData|experiment` 

        FROM ".DB_PROJECT.".shareexperiments 

        WHERE ".DB_PROJECT.".shareexperiments.`REF|leginondata|UserData|user` = ".$userId." 

    )

);

</pre>

<pre>

SELECT amberproject.projectexperiments.projectId 

FROM amberproject.projectexperiments 

INNER JOIN 

(

    SELECT amberdbemdata.SessionData.name AS SessionName 

    FROM amberdbemdata.SessionData 

    INNER JOIN 

    ( 		

        SELECT amberproject.shareexperiments.`REF|leginondata|SessionData|experiment` AS SessionId 

        FROM amberproject.shareexperiments 

        WHERE amberproject.shareexperiments.`REF|leginondata|UserData|user` = ".$userId." 

    ) AS SessionIds 

    ON amberdbemdata.SessionData.`DEF_id` = SessionIds.SessionId 

) AS SessionNames 

ON amberproject.projectexperiments.name = SessionNames.SessionName;

</pre>

See another example "here":http://forums.mysql.com/read.php?24,54721,54721#msg-54721.