Version 1 - History - Mysql Nested Subqueries Problem - Appion - Electron Microscopy Group

1

Amber Herold

h1. Mysql Nested Subqueries Problem

2

3

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

4

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

5

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

6

			// but gets around this problem.

7

//

8

			// NOTE: shareexperiments.experimentId and projectexperiments.projectexperimentId match but are not

9

			// the same as DB_LEGINON.SessionData.DEF_id. For finding the ProjectIds that correspond to a session,

10

			// we need to use the SessionData id.

11

12

//			$sharedProjectsQuery = "SELECT ".DB_PROJECT.".projectexperiments.projectId "

13

// 				." FROM ".DB_PROJECT.".projectexperiments "

14

//				." WHERE ".DB_PROJECT.".projectexperiments.name IN "

15

//				." ( "

16

//					." SELECT ".DB_LEGINON.".SessionData.name "

17

//					." FROM ".DB_LEGINON.".SessionData "

18

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

19

//					." ( "

20

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

21

//						." FROM ".DB_PROJECT.".shareexperiments "

22

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

23

//					." ) "

24

//				." )";

25

26

			$sharedProjectQuery = " SELECT amberproject.projectexperiments.projectId "

27

				." FROM amberproject.projectexperiments "

28

				." INNER JOIN ( "

29

					." SELECT amberdbemdata.SessionData.name AS SessionName "

30

					." FROM amberdbemdata.SessionData "

31

					." INNER JOIN ( "

32

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

33

						." FROM amberproject.shareexperiments "

34

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

35

					." ) AS SessionIds ON amberdbemdata.SessionData.`DEF_id` = SessionIds.SessionId "

36

				." ) AS SessionNames ON amberproject.projectexperiments.name = SessionNames.SessionName ";

37

38

39

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

Appion