Erreur Mysql : trop de jointures ?

**xxkirastarothxx** · 19/03/2010, 18h31

Bonjours à tous,

Et bien voila, j'ai une requête mysql énorme avec environ 70 jointures, le problème c'est que mysql me dit:

MySQL error : Too many tables; MySQL can only use 61 tables in a join

y a-t-il un moyen de faire sauter cette restriction ?

D'avance, pour ceux qui voudrai me dire "une requête avec 70 jointures, t'es dingue [blabla] planter mysql [blabla] serveur down en 10min [blabla]...
je leur réponds: il s'agit d'une seule et unique requête, utilisé uniquement par l'admin, et mis en cache pour la journée, donc, franchement osef ^^

merci beaucoup

Pour ce que ça interesse tout de même, voici la proc:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
 
CREATE  PROCEDURE `UserCount`(
)
BEGIN
    SELECT
    SQL_CACHE
        COUNT(u.UserID) as NbTotal,
        COUNT(uj.UserID) as Confirm,
 
        COUNT(ua.UserID) as age24,
        COUNT(ub.UserID) as age2534,
        COUNT(uc.UserID) as age3544,
        COUNT(ud.UserID) as age4554,
        COUNT(ue.UserID) as age55,
        COUNT(ua2.UserID) as cage24,
        COUNT(ub2.UserID) as cage2534,
        COUNT(uc2.UserID) as cage3544,
        COUNT(ud2.UserID) as cage4554,
        COUNT(ue2.UserID) as cage55,
 
 
        COUNT(uf.UserID) as IsOptin,
        COUNT(ug.UserID) as IsNotOptin,
        COUNT(uh.UserID) as AcceptNewsletter,
        COUNT(ui.UserID) as NotAcceptNewsletter,
        COUNT(uf2.UserID) as cIsOptin,
        COUNT(ug2.UserID) as cIsNotOptin,
        COUNT(uh2.UserID) as cAcceptNewsletter,
        COUNT(ui2.UserID) as cNotAcceptNewsletter,
 
 
        COUNT(ul.UserID) as Male,
        COUNT(um.UserID) as Female,
        COUNT(un.UserID) as Unknow,
        COUNT(ul2.UserID) as cMale,
        COUNT(umm.UserID) as cFemale,
        COUNT(un2.UserID) as cUnknow,
 
        COUNT(up1.UserID) as IsOwner,
        COUNT(up12.UserID) as cIsOwner,
        COUNT(up2.UserID) as IsNotOwner,
        COUNT(up22.UserID) as cIsNotOwner,
        COUNT(up3.UserID) as celib,
        COUNT(up32.UserID) as ccelib,
        COUNT(up4.UserID) as maried,
        COUNT(up42.UserID) as cmaried,
        COUNT(up5.UserID) as divorced,
        COUNT(up52.UserID) as cdivorced,
        COUNT(up6.UserID) as pacsed,
        COUNT(up62.UserID) as cpacsed,
        COUNT(up7.UserID) as veuf,
        COUNT(up72.UserID) as cveuf,
 
        COUNT(um1.UserID) as IsEmploye,
        COUNT(um12.UserID) as cIsEmploye,
        COUNT(um2.UserID) as IsFonct,
        COUNT(um22.UserID) as cIsFonct,
        COUNT(um3.UserID) as IsCadre,
        COUNT(um32.UserID) as cIsCadre,
        COUNT(um4.UserID) as IsCadreDir,
        COUNT(um42.UserID) as cIsCadreDir,
        COUNT(um5.UserID) as IsChef,
        COUNT(um52.UserID) as cIsChef,
        COUNT(um6.UserID) as IsProfLib,
        COUNT(um62.UserID) as cIsProfLib,
        COUNT(um7.UserID) as IsEtudiant,
        COUNT(um72.UserID) as cIsEtudiant,
        COUNT(um8.UserID) as IsRetraite,
        COUNT(um82.UserID) as cIsRetraite,
        COUNT(um9.UserID) as IsOtherJob,
        COUNT(um92.UserID) as cIsOtherJob,
 
        COUNT(uo1.UserID) as interest1,
        COUNT(uo12.UserID) as cinterest1,
        COUNT(uo2.UserID) as interest2,
        COUNT(uo22.UserID) as cinterest2,
        COUNT(uo3.UserID) as interest3,
        COUNT(uo32.UserID) as cinterest3,
        COUNT(uo4.UserID) as interest4,
        COUNT(uo42.UserID) as cinterest4,
        COUNT(uo5.UserID) as interest5,
        COUNT(uo52.UserID) as cinterest5,
        COUNT(uo6.UserID) as interest6,
        COUNT(uo62.UserID) as cinterest6,
        COUNT(uo7.UserID) as interest7,
        COUNT(uo72.UserID) as cinterest7
 
    FROM `User` u
 
        LEFT OUTER JOIN `User` ua ON ua.BirthDate > DATE_SUB(NOW(), INTERVAL '25' YEAR) AND ua.UserID = u.UserID
        LEFT OUTER JOIN `User` ub ON ub.BirthDate > DATE_SUB(NOW(), INTERVAL '35' YEAR) AND ub.BirthDate <= DATE_SUB(NOW(), INTERVAL '25' YEAR) AND ub.UserID = u.UserID
        LEFT OUTER JOIN `User` uc ON uc.BirthDate > DATE_SUB(NOW(), INTERVAL '45' YEAR) AND uc.BirthDate <= DATE_SUB(NOW(), INTERVAL '35' YEAR) AND uc.UserID = u.UserID
        LEFT OUTER JOIN `User` ud ON ud.BirthDate > DATE_SUB(NOW(), INTERVAL '55' YEAR) AND ud.BirthDate <= DATE_SUB(NOW(), INTERVAL '45' YEAR) AND ud.UserID = u.UserID
        LEFT OUTER JOIN `User` ue ON ue.BirthDate <= DATE_SUB(NOW(), INTERVAL '55' YEAR) AND ue.UserID = u.UserID
        LEFT OUTER JOIN `User` ua2 ON ua.BirthDate > DATE_SUB(NOW(), INTERVAL '25' YEAR) AND ua2.UserID = u.UserID AND ua2.Status = 2
        LEFT OUTER JOIN `User` ub2 ON ub.BirthDate > DATE_SUB(NOW(), INTERVAL '35' YEAR) AND ub2.BirthDate <= DATE_SUB(NOW(), INTERVAL '25' YEAR) AND ub2.UserID = u.UserID AND ub2.Status = 2
        LEFT OUTER JOIN `User` uc2 ON uc.BirthDate > DATE_SUB(NOW(), INTERVAL '45' YEAR) AND uc2.BirthDate <= DATE_SUB(NOW(), INTERVAL '35' YEAR) AND uc2.UserID = u.UserID AND uc2.Status = 2
        LEFT OUTER JOIN `User` ud2 ON ud.BirthDate > DATE_SUB(NOW(), INTERVAL '55' YEAR) AND ud2.BirthDate <= DATE_SUB(NOW(), INTERVAL '45' YEAR) AND ud2.UserID = u.UserID AND ud2.Status = 2
        LEFT OUTER JOIN `User` ue2 ON ue.BirthDate <= DATE_SUB(NOW(), INTERVAL '55' YEAR) AND ue2.UserID = u.UserID AND ue2.Status = 2
 
        LEFT OUTER JOIN `User` uf ON uf.IsOptin = 1 AND uf.UserID = u.UserID
        LEFT OUTER JOIN `User` ug ON ug.IsOptin = 0 AND ug.UserID = u.UserID
        LEFT OUTER JOIN `User` uh ON uh.AcceptNewsletter = 1 AND uh.UserID = u.UserID
        LEFT OUTER JOIN `User` ui ON ui.AcceptNewsletter = 0 AND ui.UserID = u.UserID
 
        LEFT OUTER JOIN `User` uf2 ON uf2.IsOptin = 1 AND uf2.UserID = u.UserID AND uf2.Status = 2
        LEFT OUTER JOIN `User` ug2 ON ug2.IsOptin = 0 AND ug2.UserID = u.UserID AND ug2.Status = 2
        LEFT OUTER JOIN `User` uh2 ON uh2.AcceptNewsletter = 1 AND uh2.UserID = u.UserID AND uh2.Status = 2
        LEFT OUTER JOIN `User` ui2 ON ui2.AcceptNewsletter = 0 AND ui2.UserID = u.UserID AND ui2.Status = 2
 
        LEFT OUTER JOIN `User` uj ON uj.Status = 2 AND uj.UserID = u.UserID
 
        LEFT OUTER JOIN `User` ul ON ul.Civility = "M." AND ul.UserID = u.UserID
        LEFT OUTER JOIN `User` um ON (um.Civility = "Mme" OR um.Civility = "Mlle") AND um.UserID = u.UserID
        LEFT OUTER JOIN `User` un ON un.Civility <> "M." AND un.Civility <> "Mme" AND un.Civility <> "Mlle" AND un.UserID = u.UserID
        LEFT OUTER JOIN `User` ul2 ON ul2.Civility = "M." AND ul2.UserID = u.UserID AND ul2.Status = 2
        LEFT OUTER JOIN `User` umm ON (umm.Civility = "Mme" OR umm.Civility = "Mlle") AND umm.UserID = u.UserID AND umm.Status = 2
        LEFT OUTER JOIN `User` un2 ON un2.Civility <> "M." AND un2.Civility <> "Mme" AND un2.Civility <> "Mlle" AND un2.UserID = u.UserID AND un2.Status = 2
 
        LEFT OUTER JOIN `UserProfiling` up1 ON up1.UserID = u.userID AND up1.IsOwner = 1
        LEFT OUTER JOIN `UserProfiling` up12 ON up12.UserID = uj.userID AND up12.IsOwner = 1
        LEFT OUTER JOIN `UserProfiling` up2 ON up2.UserID = u.userID AND up2.IsOwner = 0
        LEFT OUTER JOIN `UserProfiling` up22 ON up22.UserID = uj.userID AND up22.IsOwner = 0
 
        LEFT OUTER JOIN `UserProfiling` up3 ON up3.UserID = u.userID AND up3.MaritalStatusID = 1
        LEFT OUTER JOIN `UserProfiling` up32 ON up32.UserID = uj.userID AND up32.MaritalStatusID = 1
        LEFT OUTER JOIN `UserProfiling` up4 ON up4.UserID = u.userID AND up4.MaritalStatusID = 2
        LEFT OUTER JOIN `UserProfiling` up42 ON up42.UserID = uj.userID AND up42.MaritalStatusID = 2
        LEFT OUTER JOIN `UserProfiling` up5 ON up5.UserID = u.userID AND up5.MaritalStatusID = 3
        LEFT OUTER JOIN `UserProfiling` up52 ON up52.UserID = uj.userID AND up52.MaritalStatusID = 3
        LEFT OUTER JOIN `UserProfiling` up6 ON up6.UserID = u.userID AND up6.MaritalStatusID = 4
        LEFT OUTER JOIN `UserProfiling` up62 ON up62.UserID = uj.userID AND up62.MaritalStatusID = 4
        LEFT OUTER JOIN `UserProfiling` up7 ON up7.UserID = u.userID AND up7.MaritalStatusID = 5
        LEFT OUTER JOIN `UserProfiling` up72 ON up72.UserID = uj.userID AND up72.MaritalStatusID = 5
 
        LEFT OUTER JOIN `UserProfiling` um1 ON um1.UserID = u.userID AND um1.EmploymentID = 1
        LEFT OUTER JOIN `UserProfiling` um12 ON um12.UserID = uj.userID AND um12.EmploymentID = 1
        LEFT OUTER JOIN `UserProfiling` um2 ON um2.UserID = u.userID AND um2.EmploymentID = 2
        LEFT OUTER JOIN `UserProfiling` um22 ON um22.UserID = uj.userID AND um22.EmploymentID = 2
        LEFT OUTER JOIN `UserProfiling` um3 ON um3.UserID = u.userID AND um3.EmploymentID = 3
        LEFT OUTER JOIN `UserProfiling` um32 ON um32.UserID = uj.userID AND um32.EmploymentID = 3
        LEFT OUTER JOIN `UserProfiling` um4 ON um4.UserID = u.userID AND um4.EmploymentID = 4
        LEFT OUTER JOIN `UserProfiling` um42 ON um42.UserID = uj.userID AND um42.EmploymentID = 4
        LEFT OUTER JOIN `UserProfiling` um5 ON um5.UserID = u.userID AND um5.EmploymentID = 5
        LEFT OUTER JOIN `UserProfiling` um52 ON um52.UserID = uj.userID AND um52.EmploymentID = 5
        LEFT OUTER JOIN `UserProfiling` um6 ON um6.UserID = u.userID AND um6.EmploymentID = 6
        LEFT OUTER JOIN `UserProfiling` um62 ON um62.UserID = uj.userID AND um62.EmploymentID = 6
        LEFT OUTER JOIN `UserProfiling` um7 ON um7.UserID = u.userID AND um7.EmploymentID = 7
        LEFT OUTER JOIN `UserProfiling` um72 ON um72.UserID = uj.userID AND um72.EmploymentID = 7
        LEFT OUTER JOIN `UserProfiling` um8 ON um8.UserID = u.userID AND um8.EmploymentID = 8
        LEFT OUTER JOIN `UserProfiling` um82 ON um82.UserID = uj.userID AND um82.EmploymentID = 8
        LEFT OUTER JOIN `UserProfiling` um9 ON um9.UserID = u.userID AND um9.EmploymentID = 9
        LEFT OUTER JOIN `UserProfiling` um92 ON um92.UserID = uj.userID AND um92.EmploymentID = 9
 
        LEFT OUTER JOIN `LinkUserEnumInterest` uo1 ON uo1.UserID = u.userID AND uo1.InterestID = 1
        LEFT OUTER JOIN `LinkUserEnumInterest` uo12 ON uo12.UserID = uj.userID AND uo12.InterestID = 1
        LEFT OUTER JOIN `LinkUserEnumInterest` uo2 ON uo2.UserID = u.userID AND uo2.InterestID = 2
        LEFT OUTER JOIN `LinkUserEnumInterest` uo22 ON uo22.UserID = uj.userID AND uo22.InterestID = 2
        LEFT OUTER JOIN `LinkUserEnumInterest` uo3 ON uo3.UserID = u.userID AND uo3.InterestID = 3
        LEFT OUTER JOIN `LinkUserEnumInterest` uo32 ON uo32.UserID = uj.userID AND uo32.InterestID = 3
        LEFT OUTER JOIN `LinkUserEnumInterest` uo4 ON uo4.UserID = u.userID AND uo4.InterestID = 4
        LEFT OUTER JOIN `LinkUserEnumInterest` uo42 ON uo42.UserID = uj.userID AND uo42.InterestID = 4
        LEFT OUTER JOIN `LinkUserEnumInterest` uo5 ON uo5.UserID = u.userID AND uo5.InterestID = 5
        LEFT OUTER JOIN `LinkUserEnumInterest` uo52 ON uo52.UserID = uj.userID AND uo52.InterestID = 5
        LEFT OUTER JOIN `LinkUserEnumInterest` uo6 ON uo6.UserID = u.userID AND uo6.InterestID = 6
        LEFT OUTER JOIN `LinkUserEnumInterest` uo62 ON uo62.UserID = uj.userID AND uo62.InterestID = 6
        LEFT OUTER JOIN `LinkUserEnumInterest` uo7 ON uo7.UserID = u.userID AND uo7.InterestID = 7
        LEFT OUTER JOIN `LinkUserEnumInterest` uo72 ON uo72.UserID = uj.userID AND uo72.InterestID = 7
 
    WHERE u.BirthDate IS NOT NULL;
END

**Antoun** · 20/03/2010, 23h28

t'es dingue, non pas pour de vulgaires raisons de perf, mais parce que ça révèle une modélisation, comment dire... déficiente.

Pour en revenir à ton cas, tu crées une table temporaire avec les 10 premières jointures, et tu la reprends pour les 60 suivantes...

**xxkirastarothxx** · 22/03/2010, 10h47

Okay je vais essayé ça.
Merci

par contre:

mais parce que ça révèle une modélisation, comment dire... déficiente.

Je comprends pas :p

**ced** · 22/03/2010, 11h13

Bonjour,

A bien y regarder, je ne crois pas que la modélisation soit forcément déficiente. C'est plutôt que tu essaies d'organiser les résultats de ta requête sous la forme d'un tableau avec un grand nombre de colonnes.
Et l'erreur est là. Le SQL n'est pas fait pour ça... C'est de la cosmétique.
Voir cet article : http://sqlpro.developpez.com/cours/sqlaz/erreurs/#L9

ced

Erreur Mysql : trop de jointures ?

MySQL

Discussions similaires

Partager

Partager