View Fußball Tabelle

[neipsi]

Hallo!

Für unsere Fußball-Website haben wir die Daten für die Liga-Tabelle bis jetzt immer in einer mysql-Tabelle gespeichert. Dazu wird nach Eintragen des Spielergebnis per php-Script die neue Tabelle berechnet und abgespeichert. Ich möchte das aber jetzt in einer View erledigen, damit ich dieses php-script nicht mehr brauche.

Als Berechnungs-Basis habe ich 2 Tabellen. Einerseits eine Tabelle "spiel" wo die Ergebnisse drinstehen, und dann eine Tabelle "mannschaft" mit allen Mannschaften der jeweiligen Liga. Hier die Struktur der beiden Tabellen:

Tabelle spiel:
- liga_id
- saison_id
- spielwoche
- mannschaft_id_heim
- mannschaft_id_gast
- tore_heim
- tore_gast
- freigabe -> wird auf '1' gesetzt, sobald ein Ergebnis gültig ist

Tabelle mannschaft:
- mannschaft_id
- verein_id
- liga_id
- saison_id

Weiters hab ich mir bereits 2 Views gemacht. "vw_spielrunden" liefert mir alle Runden pro Liga. "vw_spielergebnis" liefert mir das Ergebnis pro Spiel, also ob es ein Heimsieg, ein Unentschieden, die Tore der Heimmannschft usw. Hier die Definitionen:

View vw_spielrunden

select distinct spielwoche, liga_id, saison_id
from spiel

View vw_spielergebnis

select liga_id, saison_id, spielwoche, mannschaft_id_heim, mannschaft_id_gast, tore_heim, tore_gast,
if((tore_heim > tore_gast),1,0) AS sieg_heim,
if((tore_heim = tore_gast),1,0) AS remis,
if((tore_heim < tore_gast),1,0) AS sieg_gast
from spiel
where freigabe = '1'

Ich habe jetzt folgende View gebastelt:

select m.mannschaft_id, m.liga_id, m.saison_id, r.spielwoche, m.verein_id,
sum(heim.tore_heim) as tore_plus_heim,
sum(heim.tore_gast) as tore_minus_heim,
sum(heim.sieg_heim) as siege_heim,
sum(heim.remis) as remis_heim,
sum(heim.sieg_gast) as niederlagen_gast,
sum(ausw.tore_gast) as tore_plus_ausw,
sum(ausw.tore_heim) as tore_minus_ausw,
sum(ausw.sieg_gast) as siege_ausw,
sum(ausw.remis) as remis_ausw,
sum(ausw.sieg_heim) as niederlagen_ausw

from mannschaft m

join vw_spielrunden r on r.liga_id = m.liga_id and r.saison_id = m.saison_id

left join vw_spielergebnis heim on heim.liga_id = m.liga_id
and heim.saison_id = m.saison_id
and heim.mannschaft_id_heim = m.mannschaft_id
and heim.spielwoche <= r.spielwoche

left join vw_spielergebnis ausw on ausw.liga_id = m.liga_id
and ausw.saison_id = m.saison_id
and ausw.mannschaft_id_gast = m.mannschaft_id
and ausw.spielwoche <= r.spielwoche

group by m.liga_id, m.mannschaft_id, m.saison_id, r.spielwoche

Diese View liefert mir jetzt pro Liga, Mannschaft, Saison und Spielwoche die geschossenen Tore, Anzahl der Heimsiege, Auswärtssiege usw., genau wie ich das haben will. Das Problem ist aber dass ein select darauf ca. 60 Sekunden dauert!

Ich weiß jetzt nicht, was da so lange braucht. Die View liefert mir ca. 5000 Einträge. Das sollte ja eigentlich noch keine Probleme machen. Gibt es hier noch Möglichkeiten an den Indizes was zu verändern, oder sollte ich das ganz anders angehen?

[Falk Prüfer]

Hallo,

...Gibt es hier noch Möglichkeiten an den Indizes was zu verändern...

Woher soll das jemand wissen? Du hast nicht angegeben welche Indizes bereits existieren!
Auch ist die Abfrage syntaktisch falsch. In der Group By-Klausel sind andere Felder aufgeführt als die nicht aggregierten im Select-Teil. (Im Group By zweimal m.liga_id, dafür fehlt m.verein_id)

Gruß Falk

[neipsi]

Ok, ich hab jetzt das doppelte "m.liga_id" (hab ich wohl falsch rüberkopiert) rausgenommen. Aber "m.verein_id" muss ich in die group by - Klausel nicht mitreinnehmen. Die Mannschaft_ID liefert mir ja ohnehin den eindeutigen Datensatz.

Der Index in der Spieltabelle ist auf den Spalten "mannschaft_id_heim, mannschaft_id_gast, spielwoche, runde, liga_id, saison_id". Bei der Tabelle "mannschaft" ist der Trigger auf "mannschaft_id" und "saison_id". Da könnt ich noch probieren, die "Liga-ID" mit reinnehmen.

[Falk Prüfer]

Hallo,

...Aber "m.verein_id" muss ich in die group by - Klausel nicht mitreinnehmen. Die Mannschaft_ID liefert mir ja ohnehin den eindeutigen Datensatz.

Nein! In der Group By-Klausel MÜSSEN alle Felder aus dem Select-Teil aufgeführt werden, die keine Aggregate sind! Alles andere ist Müll und wird so nur von MySQL toleriert. Wenn du das nicht beachtest, dann brauchst du dich nicht zu wundern wenn du "komische" Ergebnisse bekommst.

Zu den Indizes: Schau dir mal mit einem EXPLAIN an, ob die Indizes auch verwendet werden oder ob noch irgendwo ein Tablesacnn drin ist.

...Bei der Tabelle "mannschaft" ist der Trigger auf "mannschaft_id" und "saison_id". Da könnt ich noch probieren, die "Liga-ID" mit reinnehmen.

Trigger spielen für die Abfrage keine Rolle, die sind eher beim DML von Interesse.

Gruß Falk

[neipsi]

Die Ergebnisse sind eh in Ordnung. Nur die Laufzeit ist halt unter aller Sau. Ich werd aber bei Gelegenheit probieren die Verein_ID aus dem select rausnehmen. Ich hätte das auch schon mit einem Select probiert wo ich die Tore pro Runde in einem Subselect berechne. Das hat auch funktioniert, und war pfeilschnell. Nur konnte ich daraus keine View machen, weil es eben ein Subselect in der where-Klausel enthaltet.

[neipsi]

OK, ich hab jetzt mal die Verein_ID rausgenommen, ändert am Ergebnis aber nix. Ich hab aber jetzt mal folgendes Select gemacht:

select m.liga_id, m.saison_id, m.mannschaft_id, r.spielwoche,
  ( select concat(repeat(' ',4-(length(sum(sieg_heim)))),sum(sieg_heim),
                  repeat(' ',4-(length(sum(remis)))),sum(remis),
                  repeat(' ',4-(length(sum(sieg_gast)))),sum(sieg_gast),
                  repeat(' ',4-(length(sum(tore_heim)))),sum(tore_heim),
                  repeat(' ',4-(length(sum(tore_gast)))),sum(tore_gast))
    from vw_spielergebnis where mannschaft_id_heim = m.mannschaft_id and liga_id = m.liga_id
    and saison_id = m.saison_id and spielwoche <= r.spielwoche
  ) as heim,
  ( select concat(repeat(' ',4-(length(sum(sieg_gast)))),sum(sieg_gast),
                  repeat(' ',4-(length(sum(remis)))),sum(remis),
                  repeat(' ',4-(length(sum(sieg_heim)))),sum(sieg_heim),
                  repeat(' ',4-(length(sum(tore_gast)))),sum(tore_gast),
                  repeat(' ',4-(length(sum(tore_heim)))),sum(tore_heim))
    from vw_spielergebnis where mannschaft_id_gast = m.mannschaft_id and liga_id = m.liga_id
    and saison_id = m.saison_id and spielwoche <= r.spielwoche
  ) as ausw
  
  from mannschaft m
  join vw_spielrunden r on r.liga_id = m.liga_id and r.saison_id = m.saison_id

Die Ausführung dieser Query dauert 0.14 Sekunden, also optimal. Sobald ich aber eine View draus mache, und danach ein select * from vw_tabelle dauert das 70 Sekunden. Ich bin jetzt kurz vor der Kapitulation.