Felix Riesterer: Extreme Performance-Unterschiede zwischen MariaDB und MySQL?

Liebe Mitlesende,

ich stehe vor einem Rätsel. Auf dem Produktivsystem läuft ein MySQL-Server Version 5.7 und auf dem Test-/Entwicklungssystem ein MariaDB-Server Version 10.1.44. Eine spezielle Query (siehe unten) dauert auf dem MariaDB-Server 0,05 Sekunden, auf dem MySQL-Server stolze 20,5 Sekunden. In phpMyAdmin habe ich mir den Aufruf jeweils messen und erklären lassen. Die Ausgaben sind so unterschiedlich, dass ich nicht verstehe, warum und wo genau das Problem liegt.

Wer hat Hinweise oder Ideen, worin der Performance-Unterschied bei den beiden Servern liegen könnte?

Im Folgenden nun die Auflistungen unter "Detailliertes Profil":

MariaDB

Reihenfolge Status Zeit
1 Starting 19 µs
2 Waiting For Query Cache Lock 4 µs
3 Init 3 µs
4 Checking Query Cache For Query 12 µs
5 Checking Privileges On Cached 11 µs
6 Checking Permissions 11 µs
7 Checking Permissions 3 µs
8 Checking Permissions 3 µs
9 Checking Permissions 3 µs
10 Checking Permissions 3 µs
11 Checking Permissions 3 µs
12 Checking Permissions 3 µs
13 Checking Permissions 3 µs
14 Checking Permissions 3 µs
15 Checking Permissions 3 µs
16 Checking Permissions 4 µs
17 Checking Permissions 3 µs
18 Checking Permissions 3 µs
19 Checking Permissions 3 µs
20 Sending Cached Result To Clien 11 µs
21 Updating Status 4 µs
22 Cleaning Up 4 µs

MySQL

Reihenfolge Status Zeit
1 Sending Data 1,3 ms
2 Executing 5 µs
3 Sending Data 1,3 ms
4 Executing 5 µs
5 Sending Data 1,3 ms
6 Executing 5 µs
7 Sending Data 1,3 ms
8 Executing 5 µs
9 Sending Data 1,3 ms
10 Executing 4 µs
11 Sending Data 1,3 ms
12 Executing 5 µs
13 Sending Data 1,3 ms
14 Executing 5 µs
15 Sending Data 1,3 ms
16 Executing 5 µs
17 Sending Data 1,3 ms
18 Executing 4 µs
19 Sending Data 1,3 ms
20 Executing 5 µs
21 Sending Data 1,3 ms
22 Executing 5 µs
23 Sending Data 1,3 ms
24 Executing 5 µs
25 Sending Data 1,3 ms
26 Executing 5 µs
27 Sending Data 1,3 ms
28 Executing 5 µs
29 Sending Data 1,3 ms
30 Executing 5 µs
31 Sending Data 1,3 ms
32 Executing 5 µs
33 Sending Data 1,3 ms
34 Executing 5 µs
35 Sending Data 1,3 ms
36 Executing 5 µs
37 Sending Data 1,3 ms
38 Executing 4 µs
39 Sending Data 1,3 ms
40 Executing 4 µs
41 Sending Data 1,3 ms
42 Executing 5 µs
43 Sending Data 1,3 ms
44 Executing 5 µs
45 Sending Data 1,3 ms
46 Executing 5 µs
47 Sending Data 1,3 ms
48 Executing 5 µs
49 Sending Data 1,3 ms
50 Executing 4 µs
51 Sending Data 1,3 ms
52 Executing 4 µs
53 Sending Data 1,3 ms
54 Executing 4 µs
55 Sending Data 1,3 ms
56 Executing 5 µs
57 Sending Data 41 µs
58 Executing 3 µs
59 Sending Data 1,3 ms
60 Executing 4 µs
61 Sending Data 1,3 ms
62 Executing 5 µs
63 Sending Data 1,3 ms
64 Executing 4 µs
65 Sending Data 1,3 ms
66 Executing 4 µs
67 Sending Data 1,3 ms
68 Executing 5 µs
69 Sending Data 1,3 ms
70 Executing 5 µs
71 Sending Data 1,3 ms
72 Executing 5 µs
73 Sending Data 1,3 ms
74 Executing 5 µs
75 Sending Data 1,3 ms
76 Executing 5 µs
77 Sending Data 1,4 ms
78 Executing 5 µs
79 Sending Data 1,4 ms
80 Executing 5 µs
81 Sending Data 1,4 ms
82 Executing 5 µs
83 Sending Data 1,4 ms
84 Executing 4 µs
85 Sending Data 1,4 ms
86 Executing 5 µs
87 Sending Data 1,3 ms
88 Executing 5 µs
89 Sending Data 1,4 ms
90 End 8 µs
91 Query End 7 µs
92 Removing Tmp Table 8 µs
93 Query End 64 µs
94 Removing Tmp Table 4 µs
95 Query End 7 µs
96 Removing Tmp Table 13 µs
97 Query End 5 µs
98 Closing Tables 30 µs
99 Freeing Items 34 µs
100 Cleaning Up 12 µs

Der SQL-Code der Query ist 60 Zeilen lang:

SELECT DISTINCT
 `alt_exams`.`alt_exam`,
 `alt_exams`.`date`,
 `alt_exams`.`start`,
 `alt_bookings`.`student`,
 `people`.`sex`,
 `people`.`call_name`,
 `people`.`first_name`,
 `people`.`name_affix`,
 `people`.`family_name`,
 `people`.`name_suffix`,
 `students`.`form`,
 (
  SELECT DISTINCT `courses`.`subject`
  FROM `courses`
  WHERE `courses`.`course_name`=`alt_bookings`.`course_name`
  AND `courses`.`schedule`=(
   SELECT MAX(`courses`.`schedule`)
   FROM `courses`
   WHERE `courses`.`schedule`<`alt_exams`.`date`
  )
 ) AS `subject`,
 (
  SELECT GROUP_CONCAT(DISTINCT `lesson_teachers`.`teacher` ORDER BY `lesson_teachers`.`teacher` SEPARATOR '|%|')
  FROM `lesson_teachers`
  JOIN `lessons`
   ON `lesson_teachers`.`lesson_id`=`lessons`.`lesson_id`
  WHERE `alt_bookings`.`course_name`=`lessons`.`course_name`
  AND `lessons`.`schedule`=(
   SELECT MAX(`courses`.`schedule`)
   FROM `courses`
   WHERE `courses`.`schedule`<=`alt_exams`.`date`
  )
 ) AS `teachers`
FROM `alt_bookings`
JOIN `alt_exams`
 ON `alt_bookings`.`alt_exam`=`alt_exams`.`alt_exam`
JOIN `students`
 ON `alt_bookings`.`student`=`students`.`student`
JOIN `people`
 ON `students`.`person`=`people`.`person`
WHERE `alt_exams`.`date`>='2019-09-22'
AND EXISTS (
 SELECT `lesson_teachers`.`teacher`
 FROM `lessons`
 JOIN `lesson_teachers`
  ON `lessons`.`lesson_id`=`lesson_teachers`.`lesson_id`
 JOIN `staff`
  ON `lesson_teachers`.`teacher`=`staff`.`shorthand`
 JOIN `users`
  ON `staff`.`person`=`users`.`person`
 WHERE `lessons`.`course_name`=`alt_bookings`.`course_name`
 AND `lessons`.`schedule`=(
  SELECT MAX(`courses`.`schedule`)
  FROM `courses`
  WHERE `courses`.`schedule`<=`alt_exams`.`date`
 )
 AND `users`.`login`=''
 OR 1=1 -- privileged users --
);

Liebe Grüße

Felix Riesterer

akzeptierte Antworten

  1. Hallo Felix Riesterer,

    Es steht 44 Mal "sending Data" mit 1,3 oder 1,4 ms. Liegt da nicht der Verdacht nahe, dass der Weg vom Client zum Server berücksichtigt werden muss?

    Bis demnächst
    Matthias

    --
    Du kannst das Projekt SELFHTML unterstützen,
    indem du bei Amazon-Einkäufen Amazon smile (Was ist das?) nutzt.
    1. Lieber Matthias,

      Es steht 44 Mal "sending Data" mit 1,3 oder 1,4 ms. Liegt da nicht der Verdacht nahe, dass der Weg vom Client zum Server berücksichtigt werden muss?

      es liegt vor allem der Verdacht nahe, dass meine Anfrage ungünstig formuliert ist. Momentan tendiere ich dazu, diese ganzen Zuordnungen in PHP zu lösen, anstatt sie dem SQL-Server aufzubürden.

      Liebe Grüße

      Felix Riesterer

      1. Hallo Felix,

        wenn Du die Zuordnungen in PHP löst, dann musst Du trotzdem aufpassen. Es darf nicht passieren, dass Du nachher pro Result-Row eine oder mehrere Extraqueries absetzt. Das wäre bei 500 Rows viel zu langsam.

        Die Query wird dadurch verkompliziert, dass die DB hoch normalisiert zu sein scheint. Das ist nicht immer gut. Eine Query über 9 Tabellen ist schon heftig.

        Beispiel: Welche Users gibt es, außer Staff? Was sind die Extra-Attribute in Staff? Wenn es nur wenige Attribute sind, lohnt es sich, Staff in Users zu integrieren und ein Attribut user_type einzuführen. Das ist dann nicht Normalform, aber performanter. Denormalisierung ist oft die Schmiere im DB Getriebe. Eine noch normalisierte DB braucht viel Cache, damit die Joins nicht in die Knie gehen. Ähnliches gilt für students und people. Welche Leute gibt es noch, außer Schülern? Besteht eine Relation zwischen people und users? Kann es sinnvoll sein, auch hier zusammen zu legen? Ok, es mag zu spät dafür sein, weil zuviel Legacy Code da ist.

        Es scheint auch einen Modellfehler zu geben. students.form ist ein Attribut, das nicht nur vom Schüler abhängt, sondern auch vom Datum. Oder ist das nur der Zug, in dem der Schüler ist? Aber auch der mag sich ändern; es mag Gründe geben warum ein Schüler von der 5a in die 6b wechselt (z.B. je nach Wahl der 2. Fremdsprache, oder weil die a ihn mobbt). D.h. die Angabe der Klasse (form) gehört an die Kursbuchung. Schüler Willi bucht 2019/20 den Kurs Latein-3 und gehört dann der 8c an.

        Ist form ein Attribut des Schülers, bekommst Du Probleme beim Abruf von Daten des Vorjahres. Willi ist mittlerweile in der 9c, aber wenn Du Kurse vom letzten Schuljahr abfragst, war er noch in der 8c. Du kannst auch nicht einfach 1 abziehen, weil Willi ja nicht in der 9c sein muss. Er kann auch sitzen geblieben oder gesprungen sein.

        Was soll die Query eigentlich liefern? Prüfungen ab dem 22.9.19 und die zugehörigen Schüler, und zwar für Kurse, wo ein Lehrer mit leerem user-login existiert. Für den geprüften Kurs noch das letzte Subject vor dem Prüfungsdatum - hä? Wofür steht eine Row in einem Kurs, und was für ein Datum ist schedule? Was ist das subject eines Kurses? Deutsch? Oder "Gedichtinterpretation" - das hätte ich aber eher an der Lesson vermutet. Ich bin auch nicht sicher, dass Du den DISTINCT bei dieser Subquery brauchst, du suchst doch den Kurseintrag mit dem höchsten Schedule-Wert vor der Prüfung, und da gibt's doch vermutlich nur einen, oder? Ich hab ja keine Ahnung was Du in courses genau drin hast 😉

        Und dann werden noch die Lehrer hinzugemixt, die zum Kurs eine Lektion erteilt haben, und zwar nur die, die am oder jüngstes vor dem Prüfungsdatum stattfand (hier ist <=, beim anderen Subselect ist <, ist das korrekt?)

        Das sieht nach einem SEHR ungewöhnlichen fachlichen Szenario aus. Wenn Du hier Hilfestellung bei einer besseren Query willst, müsstest Du etwas mehr davon erzählen. Das mag dann aber zu viel über eure Schulinterna enthalten.

        Es sieht jedenfalls danach aus, als ob die Lehrer ohne Login der Kern der Sache sind. Oder gehört da eigentlich eine konkrete Login-ID hin und du hast sie weganonymisiert?

        Rolf

        --
        sumpsi - posui - clusi
        1. Lieber Rolf,

          Die Query wird dadurch verkompliziert, dass die DB hoch normalisiert zu sein scheint.

          ja, die Normalisierung habe ich so hoch umgesetzt, wie es mir möglich war.

          Das ist nicht immer gut. Eine Query über 9 Tabellen ist schon heftig.

          Das mag sein, dafür sind die Datenmengen aber überschaubar.

          Beispiel: Welche Users gibt es, außer Staff?

          Momentan keine. Der eine Sonderfall läuft über Staff.

          Was sind die Extra-Attribute in Staff?

          Das, was people zu staff macht. Du hast sicher gleich verstanden, dass hier die Mitglieder einer Schulgemeinschaft abgebildet werden. Wir haben Schülerinnen und Schüler (SuS), Kolleginnen und Kollegen (KuK), Eltern und weitere Mitarbeiter. Und da ergibt es sich nun mal, dass manche Menschen mehrere Dinge auf einmal sind, wie z.B. Lehrkräfte, die auch Eltern von SuS an unserer Schule sind. Daher gibt es auch parents und guardians, um Elternschaft von Sorgeberechtigung getrennt zu verwalten, und eben auch students.

          Wenn es nur wenige Attribute sind, lohnt es sich, Staff in Users zu integrieren und ein Attribut user_type einzuführen. Das ist dann nicht Normalform, aber performanter.

          Darüber könnte man echt nachdenken, aber vielleicht wächst das Projekt irgendwann in eine Richtung, in der auch Eltern oder gar SuS zu Usern werden können. Da will ich das Datenmodell nicht schon wieder anfassen müssen.

          Denormalisierung ist oft die Schmiere im DB Getriebe.

          So wahr mir Codd helfe.

          Eine noch normalisierte DB braucht viel Cache, damit die Joins nicht in die Knie gehen. Ähnliches gilt für students und people. Welche Leute gibt es noch, außer Schülern? Besteht eine Relation zwischen people und users? Kann es sinnvoll sein, auch hier zusammen zu legen? Ok, es mag zu spät dafür sein, weil zuviel Legacy Code da ist.

          Wie oben schon beschrieben, stehen zunächst alle Mitglieder der Schulgemeinschaft (lies: alle Menschen) in der Tabelle people. Die Beziehungen mit anderen Tabellen stellen dann dar, welche Rolle eine Person an der Schule spielt.

          Was den Legacy-Code angeht, so ist das mein Projekt, das ich eh immer wieder kräftig umkremple, wenn das Datenmodell so nicht passt, damit eben so etwas wie eine Abhängigkeit von Legacy-Code nicht gegeben ist. Wenn ich falsche Design-Entscheidungen getroffen haben sollte, dann muss ich die korrigieren und nicht das Argument des Legacy-Codes anführen! Dafür ist das Projekt auch gottseidank überschaubar genug.

          Es scheint auch einen Modellfehler zu geben. students.form ist ein Attribut, das nicht nur vom Schüler abhängt, sondern auch vom Datum.

          Das ist nicht der Fall. Aber es gibt SuS, die im Verlauf des Schuljahres abgemeldet werden, weil sie die Schule wechseln. Diese müssen in bestimmten Kontexten herausgefiltert werden.

          Außerdem gibt es anlassbezogen immer wieder neue Stundenplanversionen im Schuljahr, die neue Kursbildungen notwendig machen können. Daher sind die Kurse mit einem Stundenplan verknüpft, der ein Start-Datum hat.

          Oder ist das nur der Zug, in dem der Schüler ist? Aber auch der mag sich ändern; es mag Gründe geben warum ein Schüler von der 5a in die 6b wechselt (z.B. je nach Wahl der 2. Fremdsprache, oder weil die a ihn mobbt). D.h. die Angabe der Klasse (form) gehört an die Kursbuchung. Schüler Willi bucht 2019/20 den Kurs Latein-3 und gehört dann der 8c an.

          Eine Klasse ist eine Klasse. Ein Kurs ist ein Kurs. Das eine hat mit dem anderen rein datentechnisch nichts zu tun.

          Der Mathe-Unterricht in der 6c ist ein Kurs. Die 6c ist eine Klasse. Die Teilnehmergruppe des Mathe-Kurses ist identisch mit der Klassenliste der 6c. Das gilt auch für das Fach Deutsch, aber nicht unbedingt in allen anderen Fächern. Daher unterscheide ich grundsätzlich zwischen "Klasse" (administrative Kategorie) und "Kurs" (Unterrichtsgruppe mit Fach).

          Ist form ein Attribut des Schülers, bekommst Du Probleme beim Abruf von Daten des Vorjahres.

          Nein. Die Vorjahre sind archivierte Vorversionen des Projekts, die ihren damaligen Funktionsumfang und ihre damaligen Daten haben.

          Willi ist mittlerweile in der 9c, aber wenn Du Kurse vom letzten Schuljahr abfragst, war er noch in der 8c. Du kannst auch nicht einfach 1 abziehen, weil Willi ja nicht in der 9c sein muss. Er kann auch sitzen geblieben oder gesprungen sein.

          Dafür müsste ich im Archiv das jeweilige Projekt öffnen.

          Was soll die Query eigentlich liefern?

          Sie soll alle gebuchten SuS eines bestimmten Nachschreibetermins ermitteln. Dazu muss ich wissen, in welchem Kurs (also Fach und unterrichtende Lehrkräfte) eine versäumte Klassenarbeit nachgeschrieben werden soll. Damit der Kurs einen vernünftigen Bezeichner bekommt, möchte ich nicht nur den Namen des Fachs, sondern auch die betroffenen Klassen ermitteln, sowie die tatsächlichen Unterrichtsstunden (lessons) wegen der zugeordneten KuK, damit da z.B. 7acd Rev Xyz/Abc stehen kann (lies: evangelische Religionslehre unterrichtet von Xyz und Abc).

          Prüfungen ab dem 22.9.19 und die zugehörigen Schüler, und zwar für Kurse, wo ein Lehrer mit leerem user-login existiert. Für den geprüften Kurs noch das letzte Subject vor dem Prüfungsdatum - hä?

          Die Nachschreibetermine sind über das gesamte Schuljahr verteilt. Wie schon beschrieben ist mit veränderten Stundenplänen und daher anders zusammengesetzten Kursen zu rechnen. Deshalb muss ich für das spezielle Datum des Nachschreibetermins die dafür aktuell geltenden Kurse ermitteln, um für die betroffenen KuK und SuS die richtigen Bezeichner definieren zu können.

          Der leere User hätte auch irgend einen Dummy-Wert haben können. Der Admin-Zugang ist keiner Lehrkraft zugeordnet und hat selbst deswegen keinen eigenen Unterricht. Er hat aber übergeordnete Rechte und darf daher bei allen Unterrichten lesen und buchen.

          Wofür steht eine Row in einem Kurs, und was für ein Datum ist schedule?

          Das Datum bezeichnet den Starttag, ab dem der Stundenplan (schedule) gilt. Neuere Stundenpläne ersetzen ältere.
          Eine Row enthält alle notwendigen Daten für einen Kurs, der für diesen Nachschreibetermin gilt.

          Was ist das subject eines Kurses? Deutsch? Oder "Gedichtinterpretation" - das hätte ich aber eher an der Lesson vermutet.

          Das subject ist das Fach, das im Zeugnis wieder erscheint und dem dort eine Schulnote zugeordnet ist. Also ja, so etwas wie "Deutsch". Das Thema der nachzuschreibenden Arbeit wird in den Kursdaten nicht festgehalten, auch nicht in den Buchungsdaten. Daher ist "Gedichtinterpretation" ein Unterrichtsinhalt, der in den Daten hier nicht vorkommen kann.

          In der Tabelle lessons steht, welcher Kurs zu welchem Zeitpunkt in welchen Räumen bei welchen Lehrkräften Unterricht hat. Dadurch ermittle ich, welche Lehrkräfte für die gebuchte Nachschreibearbeit infrage kommen.

          Ich bin auch nicht sicher, dass Du den DISTINCT bei dieser Subquery brauchst, du suchst doch den Kurseintrag mit dem höchsten Schedule-Wert vor der Prüfung, und da gibt's doch vermutlich nur einen, oder? Ich hab ja keine Ahnung was Du in courses genau drin hast 😉

          Viele Kursbezeichnungen bleiben über die Stundenplanversionen hin konstant. Suchte ich also nach der Kursbezeichnung, würde die Query mehrere Treffer finden. Ich überlege gerade, inwiefern Dein Einwand vielleicht meine Buchungsdaten dahingehend verändern sollte, dass ich den relevanten Stundenplan für die Buchung mit speichere und nicht schlicht darauf vertraue, dass der jeweils neueste am Tag des Nachschreibetermins gilt. Manchmal sind KuK dazu gezwungen, Nachschreibearbeiten weit in die Zukunft zu buchen, da vorher keine geeigneten Termine sind. Bis dahin kann sich der Stundenplan aber wieder geändert haben.

          Danke für diesen Denkanstoß!

          Das sieht nach einem SEHR ungewöhnlichen fachlichen Szenario aus. Wenn Du hier Hilfestellung bei einer besseren Query willst, müsstest Du etwas mehr davon erzählen. Das mag dann aber zu viel über eure Schulinterna enthalten.

          Ich kann die Funktionalität erläutern. Kein Problem. Das ist weder etwas geheimes, noch intimes. Nur echte Daten darf ich natürlich nicht preisgeben.

          Es sieht jedenfalls danach aus, als ob die Lehrer ohne Login der Kern der Sache sind.

          Das ist ein Missverständnis. Der Admin-Zugang hat Zugriff auf die kompletten Listen. Da entsteht der Performance-Verlust. Bei jeder anderen Lehrkraft ist die Ergebnismenge wesentlich kleiner (erfahrungsgemäß 0-7), so dass man dort kein Performance-Problem hat. Dort steht dann auch nicht AND 1=1 sondern AND 1=0, was einen gültigen Loginnamen zwingend notwenig macht, wenn da Ergebnisse kommen sollen.

          Oder gehört da eigentlich eine konkrete Login-ID hin und du hast sie weganonymisiert?

          Ja, so in etwa.

          Liebe Grüße

          Felix Riesterer

          1. Hallo Felix,

            so langsam wird das Thema unübersichtlich...

            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? 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. 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.

            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. 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).

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

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

            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.

            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.

            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. 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.

            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. 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?

            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 ...
            

            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.

            Rolf

            --
            sumpsi - posui - clusi
            1. 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

              1. Hallo Felix,

                eine ausführliche Antwort wird mir gerade zu viel...

                Für einen Noob machst du jedenfalls recht erfolgreich recht komplexe Dinge 😉

                Bei der Buchung werde ich jetzt den aktuell gültigen schedule mit speichern, damit klar wird, aus welchem Stundenplan die Nachschreibearbeit entstanden ist

                Dem kann ich jetzt nicht folgen, vor allem dem Nachschreiben im nächsten Halbjahr nicht. Der course_name ist doch im Schuljahr eindeutig, oder? "Deutsch 6c" oder "Ethik Q1b" (Annahme: in der Q1 gibt es 2 Ethik-Kurse). Wird Ethik nur im 1. Halbjahr unterrichtet, würde die SELECT MAX Query doch auch im 2. HJ noch den letzten gültigen Schedule des 1. HJ finden. Bucht aber Herr Müller im November einen Ethik Nachschreibetermin im März für Schüler Willi, und geht im Dezember in Rente, willst du ihn ja nicht als Aufsicht im März haben, sondern seine Nachfolgerin Frau Meier.

                Ich habe den Subselect für die Lehrerliste nicht ganz durchschaut. Würde der Herrn Müller und Frau Meier liefern, wenn du an der Buchung den Stundenplan speicherst?

                Rolf

                --
                sumpsi - posui - clusi
            2. Lieber Rolf,

              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 haben wir den Casus Knaxus. Die Query habe ich nun so gestaltet, wie Du sie beschrieben hast und die Performance hat sich gewaltig gesteigert.

              Hurra!!!

              Herzlichsten Dank und liebe Grüße

              Felix Riesterer

              1. Hallo Felix,

                na, da haben sich 35 Jahre 😲 Berufserfahrung ja doch mal gelohnt :D

                Rolf

                --
                sumpsi - posui - clusi
          2. Schade eigentlich, dass Zitat 2527 unvollständig war und nun Minuspunkte erntet. In der vollständigen Form

            Denormalisierung ist oft die Schmiere im DB Getriebe.

            So wahr mir Codd helfe.

            wäre es vermutlich besser angekommen. Ich habe herzlich gelacht.

            Rolf

            --
            sumpsi - posui - clusi
            1. Hallo Rolf,

              Denormalisierung ist oft die Schmiere im DB Getriebe.

              So wahr mir Codd helfe.

              wäre es vermutlich besser angekommen. Ich habe herzlich gelacht.

              ich nicht, weil ich keinen blassen Schimmer habe, wer oder was mit Codd gemeint ist. Die Stelle zum Lachen erschließt sich mir daher nicht.
              Ich kenne wohl cod (Kabeljau), aber der ist hier sicher nicht gemeint.

              Ciao,
               Martin

              --
              Ich stamme aus Ironien, einem Land am sarkastischen Ozean.
              1. Hallo Der Martin,

                Denormalisierung ist oft die Schmiere im DB Getriebe.

                So wahr mir Codd helfe.

                weil ich keinen blassen Schimmer habe, wer oder was mit Codd gemeint ist.

                https://de.m.wikipedia.org/wiki/Edgar_F._Codd

                Bis demnächst
                Matthias

                --
                Du kannst das Projekt SELFHTML unterstützen,
                indem du bei Amazon-Einkäufen Amazon smile (Was ist das?) nutzt.
                1. n'Abend,

                  Denormalisierung ist oft die Schmiere im DB Getriebe.

                  So wahr mir Codd helfe.

                  weil ich keinen blassen Schimmer habe, wer oder was mit Codd gemeint ist.

                  https://de.wikipedia.org/wiki/Edgar_F._Codd

                  danke, von dem habe ich tatsächlich noch nie gehört. Deswegen war ich mir auch nicht einmal sicher, ob eine Person, ein Unternehmen, ein geographischer oder psychologischer Begriff gemeint sein könnte.

                  Ciao,
                   Martin

                  --
                  Ich stamme aus Ironien, einem Land am sarkastischen Ozean.
            2. Aloha ;)

              Schade eigentlich, dass Zitat 2527 unvollständig war und nun Minuspunkte erntet.

              Ich hab mal meine Adminrechte misgebraucht um das Zitat zu vervollständigen. Vielleicht ändert ja der eine oder andere seine Bewertung dann nochmal.

              Grüße,

              RIDER

              --
              Camping_RIDER a.k.a. Riders Flame a.k.a. Janosch Zoller
              # Twitter # Steam # YouTube # Self-Wiki # Selfcode: sh:) fo:) ch:| rl:) br:^ n4:? ie:% mo:| va:) js:) de:> zu:} fl:( ss:) ls:[
          3. ja, die Normalisierung habe ich so hoch umgesetzt, wie es mir möglich war.

            Mehrere Distinct in Deinen Abfragen deuten eher darauf hin, daß Deine Tabellen nicht normalisiert sind.

            MFG

            1. Hallo pl,

              ja, die Normalisierung habe ich so hoch umgesetzt, wie es mir möglich war.

              Mehrere Distinct in Deinen Abfragen deuten eher darauf hin, daß Deine Tabellen nicht normalisiert sind.

              Begründung bitte.

              Bis demnächst
              Matthias

              --
              Du kannst das Projekt SELFHTML unterstützen,
              indem du bei Amazon-Einkäufen Amazon smile (Was ist das?) nutzt.
  2. Hi,

    ich stehe vor einem Rätsel. Auf dem Produktivsystem läuft ein MySQL-Server Version 5.7 und auf dem Test-/Entwicklungssystem ein MariaDB-Server Version 10.1.44. Eine spezielle Query (siehe unten) dauert auf dem MariaDB-Server 0,05 Sekunden, auf dem MySQL-Server stolze 20,5 Sekunden.

    Sind die Datenmengen identisch?

    Sind dieselben Indizes gesetzt?

    Was ergibt explain für das Statement auf den beiden DBs?

    cu,
    Andreas a/k/a MudGuard

    1. Lieber MudGuard,

      Sind die Datenmengen identisch?

      ja.

      Sind dieselben Indizes gesetzt?

      Ja.

      Was ergibt explain für das Statement auf den beiden DBs?

      Das habe ich in meiner Antwort an Christian gelistet. Nach dem Deaktivieren des Caches sieht das bei beiden gleich aus.

      Liebe Grüße

      Felix Riesterer

    2. Hello,

      [•••]
      sind die Server gleich oder ähnlich konfiguriert bezüglich Engines, Buffers, Memory, usw.?

      Wid ist die Prozessor- und Speicherausstattung der Systeme? Hat vielleicht das MariaDB-System eine SSD und das MySQL-System noch eine Spin-HDD?

      Um die Ursache zu finden, müsste Felix vielleicht erstmal gezielte Teststatements/-einrichtungen basteln und absetzen.

      Glück Auf
      Tom mobil

      1. Lieber TS,

        ich habe keine Ahnung, welche Seriennummern die im Server verbauten Prozessoren oder Speicherriegel haben. Das kümmert mich als Kunden auch nicht. Was mich kümmert, ist die Umständlichkeit meiner Queries. Da ist sehr viel Optimierungspotenzial, ohne Wissen um die Nennspannung auf dem LAN-Kabel, die UEFI-Versionsnummer oder die Größe des L3-Caches.

        Liebe Grüße

        Felix Riesterer

        1. Lieber Felix,

          ich habe keine Ahnung, welche Seriennummern die im Server verbauten Prozessoren oder Speicherriegel haben. Das kümmert mich als Kunden auch nicht. Was mich kümmert, ist die Umständlichkeit meiner Queries. Da ist sehr viel Optimierungspotenzial, ohne Wissen um die Nennspannung auf dem LAN-Kabel, die UEFI-Versionsnummer oder die Größe des L3-Caches.

          Denkwürdige Einstellung. Du hast nach Performance gefragt und nun bringst Du hier Dinge ins Spiel, die ich wirklich als unsachlich empfinde :-O

          Aber wenn Du weiter Äpfel mit Würstchenwasser vergleichen willst, dann passt das wohl.

          Glück Auf und gute Nacht
          Tom mobil

          1. Lieber Tom,

            Denkwürdige Einstellung. Du hast nach Performance gefragt und nun bringst Du hier Dinge ins Spiel, die ich wirklich als unsachlich empfinde :-O

            ich fand Deine Nachfrage wirklich sehr daneben. Folgende Gründe:

            1. Offensichtlich habe ich eine Erwartungshaltung, dass die Performance eigentlich ähnlich sein sollte.
            2. Meine Erwartungshaltung hat einen Grund, nämlich den, dass es in anderen Zusammenhängen eine gleiche oder ähnliche Performance gibt.
            3. Das Problem muss mit Software (allein!) zu tun haben. Offensichtlich ist meine Query optimierbar, daher habe ich sie gepostet.
            4. Du kommst mit Hardware-Fragen, die mit Software überhaupt nichts zu tun haben.

            Aber wenn Du weiter Äpfel mit Würstchenwasser vergleichen willst, dann passt das wohl.

            Genau. Und bitte das Abfalldatum auf den Äpfeln berücksichtigen! ;-)

            Liebe Grüße

            Felix Riesterer

            1. Hello,

              Lieber Tom,

              Denkwürdige Einstellung. Du hast nach Performance gefragt und nun bringst Du hier Dinge ins Spiel, die ich wirklich als unsachlich empfinde :-O

              ich fand Deine Nachfrage wirklich sehr daneben. Folgende Gründe:

              1. Offensichtlich habe ich eine Erwartungshaltung, dass die Performance eigentlich ähnlich sein sollte.
              2. Meine Erwartungshaltung hat einen Grund, nämlich den, dass es in anderen Zusammenhängen eine gleiche oder ähnliche Performance gibt.
              3. Das Problem muss mit Software (allein!) zu tun haben. Offensichtlich ist meine Query optimierbar, daher habe ich sie gepostet.
              4. Du kommst mit Hardware-Fragen, die mit Software überhaupt nichts zu tun haben.

              Aber wenn Du weiter Äpfel mit Würstchenwasser vergleichen willst, dann passt das wohl.

              Ich hatte verstanden, dass Du die gleiche Abfrage auf zwei unterschiedlichen Systemen durchführst und nun nach dem Grund für unterschiedliche Antwortzeiten suchst.

              Habe ich Dich da verkehrt verstanden?

              Glück Auf
              Tom vom Berg

              --
              Es gibt nichts Gutes, außer man tut es!
              Das Leben selbst ist der Sinn.
            2. Dieser Beitrag wurde gesperrt: Der Beitrag ist unkonstruktiv oder provokativ und trägt zu einer Verschlechterung der Stimmung bei.

              ich fand Deine Nachfrage wirklich sehr daneben.

              So ging es mir mit Deiner Antwort auf Toms Anregungen auch. Christian Kruse hat in diesem Thread ähnliche Fragen in den Raum geworfen - völlig zu Recht. Die hast Du aber nicht in der Luft zerrissen. Warum eigentlich?

              Stattdessen redest Du jetzt von Erwartungshaltungen und Software, mit der es zu tun haben muss, ohne dass Du das überhaupt fachlich greifen kannst.

              Schönen Tach!

              1. Dieser Beitrag wurde gelöscht: Der Beitrag ist unkonstruktiv oder provokativ und trägt zu einer Verschlechterung der Stimmung bei.
              2. Dieser Beitrag wurde gelöscht: Der Beitrag ist unkonstruktiv oder provokativ und trägt zu einer Verschlechterung der Stimmung bei.
              3. Lieber Mitleser,

                ich habe auf Christians Frage am 1.3. mit allen nachgefragten Details geantwortet. Seit diesem Zeitpunkt sollte in der Diskussion die Hardware als primäre Ursache sicher auszuschließen gewesen sein - insbesondere als klar werden musste, dass das Caching auf meinem Testsystem diesen enormen Performanceunterschied verursachte (auch noch am 1.3.). Und nun kommt Tom am 3.3. mit Hardware-Fragen, die diese miese Performance auf inzwischen (Stand spät am 1.3.) beiden Systemen nicht einmal im Ansatz erklären konnten.

                Da fühlte ich mich ... nunja, sagen wir mal: im falschen Film.

                Liebe Grüße

                Felix Riesterer

          2. Jetzt habe ich gerade 10 Minuten an einer Antwort auf

            https://forum.selfhtml.org/self/2020/mar/01/extreme-performance-unterschiede-zwischen-mariadb-und-mysql/1766568#m1766568

            rumgetippt... Absenden gedrückt... "Zugriff verboten".

            Frustierend. Naja. Schönen Sonntag noch.

  3. Hallo Felix,

    Wer hat Hinweise oder Ideen, worin der Performance-Unterschied bei den beiden Servern liegen könnte?

    Ein EXPLAIN sowie die Datenstrukturen sollten bei so einer Frage nicht fehlen.

    Es fällt jedoch auf:

    Reihenfolge Status Zeit
    1 Starting 19 µs
    2 Waiting For Query Cache Lock 4 µs
    3 Init 3 µs
    4 Checking Query Cache For Query 12 µs
    5 Checking Privileges On Cached 11 µs
    6 Checking Permissions 11 µs
    7 Checking Permissions 3 µs
    8 Checking Permissions 3 µs
    9 Checking Permissions 3 µs
    10 Checking Permissions 3 µs
    11 Checking Permissions 3 µs
    12 Checking Permissions 3 µs
    13 Checking Permissions 3 µs
    14 Checking Permissions 3 µs
    15 Checking Permissions 3 µs
    16 Checking Permissions 4 µs
    17 Checking Permissions 3 µs
    18 Checking Permissions 3 µs
    19 Checking Permissions 3 µs
    20 Sending Cached Result To Clien 11 µs
    21 Updating Status 4 µs
    22 Cleaning Up 4 µs

    Die Query kommt direkt aus dem Cache und wir in einem Rutsch rübergeschickt (siehe Checking Query Cache For Query und Checking Privileges On Cached). Um das realistisch zu prüfen würde ich den Cache auf der Entwicklungsmaschine mal deaktivieren (query_cache_size und query_cache_type in der my.cnf auf 0 setzen und MariaDB neu starten).

    Reihenfolge Status Zeit
    1 Sending Data 1,3 ms
    2 Executing 5 µs
    […]
    90 End 8 µs
    91 Query End 7 µs
    92 Removing Tmp Table 8 µs
    93 Query End 64 µs
    94 Removing Tmp Table 4 µs
    95 Query End 7 µs
    96 Removing Tmp Table 13 µs
    97 Query End 5 µs
    98 Closing Tables 30 µs
    99 Freeing Items 34 µs
    100 Cleaning Up 12 µs

    Hier werden drei temporäre Tabellen erstellt. Außerdem wird das Ergebnis sehr fragmentiert übertragen (siehe die vielen executing und sending-Einträge). Ist der MySQL-Server auf der gleichen Maschine? Ist Netzwerk ein Flaschenhals? Wie groß ist das Resultset? Wie sieht die Speicher-Auslastung auf der DB-Maschine aus? Prüfe auch, ob der Tablespace für temporäre Tabellen auf einer drehenden Platte ist oder auf einer SSD. Prüfe vielleicht, warum der Server überhaupt temporäre Tabellen erstellen muss: ist er vielleicht unter memory pressure? Wie sieht die IO-Auslastung aus?

    Das sind alles Allgemeinplätze, aber viel mehr kann man dazu nicht sagen ohne genauere Informationen…

    Der SQL-Code der Query ist 60 Zeilen lang:

    […]

    Drei correlating subqueries – ist vielleicht auch nicht so optimal. Das könnte schon auch ein Grund für die temporären Tabellen sein. Kannst du das vielleicht umformulieren?

    Freundliche Grüße,
    Christian Kruse

    1. Lieber Christian,

      ich habe den Cache auf 0 gesetzt und sehe, dass dann ebenfalls 100 Teilschritte entstehen. Sie dauern aber Microsekunden, anstatt Millisekunden. Das mag daran liegen, dass auf meinem Testsystem der Apache und der MariaDB-Server auf derselben Maschine liegen, auf dem Webspace aber nicht.

      Ein EXPLAIN sowie die Datenstrukturen sollten bei so einer Frage nicht fehlen.

      Das sieht bei beiden sehr ähnlich aus:

      MariaDB

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY alt_exams ALL PRIMARY NULL NULL NULL 18 Using where; Using temporary
      1 PRIMARY alt_bookings ref PRIMARY,alt_exam,student PRIMARY 4 school_2019_2020.alt_exams.alt_exam 9 Using where
      1 PRIMARY students eq_ref PRIMARY,person PRIMARY 4 school_2019_2020.alt_bookings.student 1
      1 PRIMARY people eq_ref PRIMARY PRIMARY 4 school_2019_2020.students.person 1
      6 SUBQUERY users index person person 6 NULL 78 Using index
      6 SUBQUERY staff eq_ref PRIMARY,shorthand PRIMARY 4 school_2019_2020.users.person 1 Using where
      6 SUBQUERY lesson_teachers ref PRIMARY,lesson_id,teacher teacher 62 school_2019_2020.staff.shorthand 80 Using where; Using index
      6 SUBQUERY lessons eq_ref PRIMARY PRIMARY 4 school_2019_2020.lesson_teachers.lesson_id 1
      7 DEPENDENT SUBQUERY courses index PRIMARY,schedule schedule 5 NULL 4479 Using where; Using index
      4 DEPENDENT SUBQUERY lessons ref PRIMARY,schedule,course_name course_name 602 school_2019_2020.alt_bookings.course_name 10 Using index condition; Using where
      4 DEPENDENT SUBQUERY lesson_teachers ref PRIMARY,lesson_id PRIMARY 4 school_2019_2020.lessons.lesson_id 1 Using index
      5 DEPENDENT SUBQUERY courses index PRIMARY,schedule schedule 5 NULL 4479 Using where; Using index
      2 DEPENDENT SUBQUERY courses eq_ref PRIMARY,schedule,course_name PRIMARY 605 func,school_2019_2020.alt_bookings.course_name 1 Using where; Using temporary
      3 DEPENDENT SUBQUERY courses index PRIMARY,schedule schedule 5 NULL 4479 Using where; Using index

      MySQL

      id select_type table partitions type possible_keys key key_len ref rows filtered Extra
      1 PRIMARY alt_exams NULL ALL PRIMARY NULL NULL NULL 18 33.33 Using where; Using temporary
      1 PRIMARY alt_bookings NULL ref PRIMARY,alt_exam,student PRIMARY 4 school.alt_exams.alt_exam 23 100.00 Using where
      1 PRIMARY students NULL eq_ref PRIMARY,person PRIMARY 4 school.alt_bookings.student 1 100.00 NULL
      1 PRIMARY people NULL eq_ref PRIMARY PRIMARY 4 school.students.person 1 100.00 NULL
      6 DEPENDENT SUBQUERY users NULL index person person 4 NULL 78 100.00 Using index
      6 DEPENDENT SUBQUERY staff NULL eq_ref PRIMARY,shorthand PRIMARY 4 school.users.person 1 100.00 Using where
      6 DEPENDENT SUBQUERY lesson_teachers NULL ref PRIMARY,lesson_id,teacher teacher 62 school.staff.shorthand 160 100.00 Using where; Using index
      6 DEPENDENT SUBQUERY lessons NULL eq_ref PRIMARY PRIMARY 4 school.lesson_teachers.lesson_id 1 100.00 NULL
      7 DEPENDENT SUBQUERY courses NULL ALL PRIMARY,schedule NULL NULL NULL 4479 33.33 Range checked for each record (index map: 0x5)
      4 DEPENDENT SUBQUERY lessons NULL ref PRIMARY,schedule,course_name course_name 602 school.alt_bookings.course_name 21 100.00 Using index condition; Using where
      4 DEPENDENT SUBQUERY lesson_teachers NULL ref PRIMARY,lesson_id lesson_id 4 school.lessons.lesson_id 1 100.00 Using index
      5 DEPENDENT SUBQUERY courses NULL ALL PRIMARY,schedule NULL NULL NULL 4479 33.33 Range checked for each record (index map: 0x5)
      2 DEPENDENT SUBQUERY courses NULL ref PRIMARY,subject,schedule,course_name PRIMARY 3 func 559 10.00 Using where; Using temporary
      3 DEPENDENT SUBQUERY courses NULL ALL PRIMARY,schedule NULL NULL NULL 4479 33.33 Range checked for each record (index map: 0x5)

      Hier werden drei temporäre Tabellen erstellt. Außerdem wird das Ergebnis sehr fragmentiert übertragen (siehe die vielen executing und sending-Einträge).

      Ich kann es noch nicht besser, sonst würde ich meine Anfrage sicherlich günstiger formulieren.

      Ist der MySQL-Server auf der gleichen Maschine?

      Offensichtlich nicht. Es handelt sich um ein shared webhosting Paket.

      Ist Netzwerk ein Flaschenhals?

      Das kann ich nicht beurteilen.

      Wie groß ist das Resultset?

      Weniger als 500 Zeilen.

      Wie sieht die Speicher-Auslastung auf der DB-Maschine aus? Prüfe auch, ob der Tablespace für temporäre Tabellen auf einer drehenden Platte ist oder auf einer SSD.

      Das ist in einem Rechenzentrum... k.A.

      Drei correlating subqueries – ist vielleicht auch nicht so optimal. Das könnte schon auch ein Grund für die temporären Tabellen sein. Kannst du das vielleicht umformulieren?

      Wenn ich wüsste, wie man das macht! Momentan tendiere ich dazu, die Abfrage allgemeiner zu halten und die Zuordnungen per PHP-Script zu leisten.

      Liebe Grüße

      Felix Riesterer

  4. Welchen Datentyp hat alt_exams.date? Wirklich ein String, wie die Query vermuten lässt?