Extreme Performance-Unterschiede zwischen MariaDB und MySQL?

[Felix Riesterer]

Lieber Rolf,

ganz herzlichen Dank für Deine ausführliche Beschäftigung mit meinem Problem!

Du nanntest die Idee der Denormalisierung. Da kann ich aus meiner Überzeugung tatsächlich ein Stück von annehmen, nämlich eine Art Caching der Kursbezeichnungen. Die werden für jeden Kurs pro Stundenplan als Eigenschaft display_name in die Tabelle courses eingetragen werden (muss ich noch implementieren). Dann kann ich alles diesbezügliche aus der Query weg lassen. Das hilft mir auch an anderen Stellen in meinem Projekt wieder weiter.

so langsam wird das Thema unübersichtlich...

Hehe, welcome to my world. Schule ist im Prinzip schon recht unübersichtlich. Aber schön, dass Du da schon so weit durchsteigst, dass Du mir sagen kannst, was ich verbessern kann!

Soweit in der Query erkennbar, hast Du also einmal die users mit der Spezialisierung staff, und zum anderen die people mit Spezialisierung students, parents und guardians - aber auch die Lehrer. Ist also users eine Spezialisierung von people?

So habe ich noch nie darüber nachgedacht, aber ja, Deine Formulierung stimmt. Die Tabellen people und users haben eine Spalte person gemeinsam.

Demnach ist also "student", "parent", "guardian" oder "teacher" eine Rolle, die ein Mensch aus people haben kann, und Rollen finden sind typischerweise in m:n Beziehungstabellen wieder. Dein Modell sieht da richtig aus.

Juhu! Ein Lob! Für einen DB-Noob! 8-]

Was mir quer sitzt, ist users. Zur Zeit sind nur Lehrer User, aber Du willst ja erweitern können. Demnach ist potenziell jeder in people ein User, und der Login gehört dorthin. Es sei denn, du definierst "user" als weitere Rolle, die ein Mensch haben kann - aber da wäre ich pragmatisch.

Ich habe in einer anderen Tabelle user_privileges definiert, was ein bestimmter User tun darf und was nicht. Hier haben die Tabellen die Spalte login gemeinsam. Natürlich könnte die auch eine Eigenschaft von people sein, aber dort stehen im Grunde die Dinge wie Name, Nachname, Anschrift, E-Mail usw.

Anderer Punkt. Ich verstehe Dich so, dass Deine Exists Abfrage die Aufgabe hat, bei Nichtadmins die Berechtigung zu begrenzen. Lehrer sehen die Nachschreibeliste nur für die Kurse, die sie unterrichten. Und Admins sehen alles.

Korrekt.

In dem Fall ist es aber doch so, dass Du den ganzen Exists-Rattenschwanz nur für eine Lehrer-Query brauchst, und für eine Admin-Query gar nicht (weil es doch zu erwarten ist, dass für jeden Kurs, in dem nachgeschrieben wird, einen Lehrer gibt).

Du meinst, ich sollte den SQL-Code dynamischer zusammenstellen?

Wenn also mit 1=1 tricksen, dann so (direkte Werteinjektion ins SQL angenommen):

AND (1=$admin OR EXISTS(SELECT ....))

Ist meine aktuelle Schreibweise so, dass damit unnötige Datenermittlung ausgelöst wird? Das wäre eine echte Performance-Bremse.

Die Klammern sind wichtig. Siehe nächsten Absatz. Effizienter sollte es aber sein, den AND EXISTS Rattenschwanz nur für Lehrer-Queries ans SQL zu hängen und für Admins komplett wegzulassen. Den 1=1 Trick macht man eigentlich nur, wenn man auf statisches SQL angewiesen ist. Das ist bei PHP nicht der Fall.

OK. Also kein Monster-SQL, um es bequem mit passenden Parametern zu füllen, sondern direkt zwei maßgeschneiderte SQL-Statements für die beiden Fälle bereithalten.

Deine Implementierung des 1=1 Tricks scheint mir ohnehin falsch. AND bindet stärker als OR, d.h. für Admins bist Du im EXISTS komplett ohne WHERE Bedingung unterwegs.

Also wäre es besser, das EXISTS würde bei Admins komplett fehlen.

Übrigens: Die Admins-Eigenschaft kann für speziell diese Aufgabe vergeben werden. Die Lehrkraft, die den Nachschreibetermin organisiert, ist für diesen Teil des Projekts mit diesem Privileg ausgerüstet, damit sie Zugriff auf alle Buchungen hat, um auch bei anderen Kursen buchen oder stornieren zu können.

Du könntest für Lehrerabfragen auch zunächst einmal den shorthand-Wert des betreffenden Lehrers ermitteln und den als Parameterwert in die Query geben, das eliminiert 2 JOINs.

Der prinzipielle Admin-Account hat kein Lehrerkürzel, weil er kein Lehrer ist. Daher könnte es einen User geben, der hier Admin-Privileg hat, aber eben kein Lehrerkürzel und damit keinen eigenen Unterricht.

Trotzdem musst Du wieder mit dem SELECT MAX ran, weil ja lessons wie courses den Schedule benutzen. Und das k*tzt mich so richtig an.

Hehe, mich auch! Aber hier habe ich schon etwas nachgedacht: Bei der Buchung werde ich jetzt den aktuell gültigen schedule mit speichern, damit klar wird, aus welchem Stundenplan die Nachschreibearbeit entstanden ist. Es könnte ja sein, dass ein Unterricht nur im ersten Halbjahr stattfindet und der Nachschreiber erst im zweiten Halbjahr einen Buchungsplatz bekommt. Bisher geht mein Programm davon aus, dass der Unterricht zur Zeit des Buchungstermins stattfindet und deshalb der aktuellst mögliche schedule angenommen werden muss. Und daher kommt das SELECT MAX. Und mit dem Speichern des relevanten schedule-Wertes könnte das entfallen.

Das Grundgerüst der Query ist doch: Ein Schüler S schreibt am Termin X (alt_exam) im Fach Y (course) eine Nachprüfung. Die Beziehung zwischen den dreien wird durch alt_bookings repräsentiert.

Genau.

Am Kurs hängt das Stundenplandatum, ab dem dieser Kurs so gehalten wird. Und das sollte genau einer sein. Das hatte ich schonmal im Zusammenhang mit DISTINCT angesprochen, und du meintest, der Kursname wäre mehrfach da - aber doch nicht zu einem schedule-Datum, oder?

Beides ist richtig. Die Spalten course_name und schedule sind zusammen der Primärschlüssel der Tabelle courses. Mit meiner Idee, den relevanten schedule-Wert in die alt_bookings-Tabelle aufzunehmen, hätten wir das Problem gelöst und die Normalisierung nicht einmal verletzt.

Deswegen sollte man mit dieser Query doch pro Nachprüfungstermin den dort nachgeprüften Kurs bekommen (einmal pro Schüler).

SELECT x.alt_exam, x.date, b.student, p.*, s.form, c.subject
FROM alt_exams X
     JOIN alt_bookings B ON X.alt_exam = B.alt_exam
     JOIN courses C ON C.course_name = B.course_name 
                   AND C.schedule = (SELECT MAX(c2.schedule) FROM courses c2 
                                     WHERE c2.schedule <= x.date)
     JOIN students s ON S.student = B.student
     JOIN people p ON ...

Das muss ich mir genauer anschauen. Das braucht aber Zeit.

Bis auf Rechteprüfung und Lehrerliste sollte das vollständig und richtig sein. Interessant ist, dass wir jetzt den schedule zum Kurs in der Ergebnismenge haben (wenn auch nicht anzeigen). Du kannst also c.schedule als Input für den Subselect mit dem GROUP_CONCAT verwenden und brauchst da keinen neuen SELECT MAX. Das gleiche gilt für die Rechteprüfung, du kannst auch dort die Lessons mit c.schedule heraussuchen, ohne einen neuen SELECT MAX zu machen.

Da habe ich also die wichtigste Optimierungsmöglichkeit! Prima! Das werde ich genauer anschauen und prüfen, wo ich noch so Mist baue.

Noch einmal ganz herzlichen Dank!

Liebe Grüße

Felix Riesterer