Actions
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 DB_PROJECT.projectexperiments.projectId FROM DB_PROJECT.projectexperiments INNER JOIN ( SELECT DB_LEGINON.SessionData.name AS SessionName FROM DB_LEGINON.SessionData INNER JOIN ( SELECT DB_PROJECT.shareexperiments.`REF|leginondata|SessionData|experiment` AS SessionId FROM DB_PROJECT.shareexperiments WHERE DB_PROJECT.shareexperiments.`REF|leginondata|UserData|user` = ".$userId." ) AS SessionIds ON DB_LEGINON.SessionData.`DEF_id` = SessionIds.SessionId ) AS SessionNames ON DB_PROJECT.projectexperiments.name = SessionNames.SessionName;
See another example here.
Updated by Amber Herold over 14 years ago · 5 revisions