Mysql Nested Subqueries Problem » History » Revision 1
Revision 1/5
| Next »
Amber Herold, 03/04/2010 01:28 PM
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.
//
// NOTE: shareexperiments.experimentId and projectexperiments.projectexperimentId match but are not
// the same as DB_LEGINON.SessionData.DEF_id. For finding the ProjectIds that correspond to a session,
// we need to use the SessionData id.
// $sharedProjectsQuery = "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." "
// ." ) "
// ." )";
$sharedProjectQuery = " 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:http://forums.mysql.com/read.php?24,54721,54721#msg-54721
Updated by Amber Herold over 14 years ago · 1 revisions