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.

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." 
    )
);

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;

See another example here.