mysql - SQL查询执行缓慢

Question

我的 sql 查询有问题，它给了我想要的输出，并且在我的计算机中工作正常，但由于我必须在学校的 PC 上运行我的数据库，所以我遇到了问题。查询需要34!执行秒数，而在我的电脑上需要大约 6 秒。

这是我的数据库:

查询是:您必须为每个成为健身房成员(member)的用户找到“Panca Orizzontale”、“Squat”、“Estensioni Bilanciere”、“Squat”练习的 1 次 max( Max(Carico) )超过5年。

这是我正在使用的查询:

SELECT U.Nome
     , U.Cognome
     , MAX(P1.Carico) AS MaxPanca_Orizzontale
     , MAX(P2.Carico) AS MaxSquat
     , MAX(P3.Carico) AS MaxEstensioni_Bilanciere
     , MAX(P4.Carico) AS MaxLento_Avanti
  FROM utente AS U 
  left 
  join scheda AS S1 
    on U.CF=S1.ID_Utente 
  left 
  join programma AS P1 
    on S1.ID_Scheda = P1.ID_Scheda 
   AND P1.nRipetizioni = 1 
   AND P1.Esercizio = "Panca Orizzontale"
  left 
  join scheda AS S2 
    on U.CF=S2.ID_Utente 
  left 
  join programma AS P2 
    on S2.ID_Scheda = P2.ID_Scheda 
   AND P2.nRipetizioni = 1 
   AND P2.Esercizio = "Squat"
  left 
  join scheda AS S3 
    on U.CF = S3.ID_Utente 
  left 
  join programma AS P3 
    on S3.ID_Scheda = P3.ID_Scheda 
   AND P3.nRipetizioni = 1 
   AND P3.Esercizio = "Estensioni Bilanciere"
  left 
  join scheda AS S4 
    on U.CF = S4.ID_Utente 
  left 
  join programma AS P4 
    on S4.ID_Scheda = P4.ID_Scheda 
   AND P4.nRipetizioni = 1 
   AND P4.Esercizio = "Lento Avanti"
 WHERE U.CF IN(SELECT U.CF 
                 FROM utente U 
                WHERE Data_Iscrizione < date_sub(curdate(), interval 5 year)
              )
 GROUP 
    BY U.Nome
     , U.Cognome;

这就是我们想要的结果:

可能所有这些连接都是问题，有没有办法让它执行得更快？感谢您的宝贵时间

Answer 1

我想说你最好将programma.Esercizio 添加到分组依据中。像这样的东西:

SELECT U.Nome, U.Cognome, P.Esercizio, MAX(Carico) AS MaxCarico
FROM utente AS U left join scheda AS S1 on U.CF=S1.ID_Utente 
left join programma AS P1 on S1.ID_Scheda=P1.ID_Scheda AND P1.nRipetizioni=1
WHERE U.CF IN(SELECT U.CF FROM utente U WHERE Data_Iscrizione < date_sub(curdate(), interval 5 year))
GROUP BY U.Nome, U.Cognome, P.Esercizio;

输出会略有不同(额外的列和 4 行而不是 ne)，但它对于数据库服务器来说要好得多。您可以测试一下它是否仍然满足您的需求吗？