SQL MySQL: Verschachteltes Select auflösen und beschleunigen

[Physikbuddha]

Hallo zusammen,

ich habe hier eine etwas größere DB-Abfrage für eine interaktive Website, für die ich etwas Hilfe von einem Datenbank-Crack gebrauchen könnte. (Das System basiert auf Typo3, falls es jemandem hilft).

Als kurze Erklärung was ich machen will:
Eine Webseite selber besteht aus mehreren Inhaltselementen wie Text und Bildern, die T3 in der Tabelle tt_content speichert. Desweiteren gebe ich Aufgaben (Tabelle aufgaben) mit mehreren Antwortmöglichkeiten (Tabelle antworten) aus.
Ein angemeldeter Frontend-Benutzer dieser Website (Tabelle feuser) kann nun seine Antwort auf diese Frage abgeben (Tabelle feuserantwort).
Abhängig von der letzten Feuserantwort möchte ich manche dieser Inhaltselemente verstecken.


Ein Beispiel:
Nehmen wir an, das erste Inhaltselement ist eine Aufgabe und fragt den Nutzer "Wie geht es dir heute?". An dieser Aufgabe hängen drei Antworten, die als Radiobuttons gerendert werden: Sehr gut, mittelmäßig, schlecht.

Für jede dieser Antwortmöglichkeiten habe ich darunter ein weiteres Inhaltselement mit einer Reaktion angelegt. Wählt der FE-User beispielsweise "sehr gut", möchte ich ausgeben "Das freut uns, bla bla bla".

Also habe ich alle drei dieser Reaktions-Inhaltselemente mit einem Zugriffsschutz versehen und zeige diese nur an, wenn die abgegebene Feuserantwort mit einer Bedingung übereinstimmt (tt_content.antwortbedingung und tt_content.antwortbedingungswert).


Damit ich nicht sämtliche Objekte aus der DB holen und mit PHP iterieren muss, habe ich dafür eine Abfrage geschrieben.
Die hat während der Entwicklung mit maximal 20 Testusern auch super funktioniert.
Jetzt ist das System live und mit ca. 2500 Usern geht die Performance in die Knie.
Lokal sind es noch 0,3 Sekunden (warum auch immer), aber live hab ich ne Ausführungsdauer von über 100 Sekunden. Liegt vielleicht an der neueren MySQL-Version lokal. Aber ich möchte das Problem bei der Wurzel packen.

SELECT tt_content.* FROM tt_content
LEFT OUTER JOIN antwort a ON a.uid = tt_content.antwort 
LEFT OUTER JOIN feuserantwort ua ON ua.antwort = a.uid

#ich möchte nur Inhaltselemente von einer bestimmten Seite (Page) kriegen
WHERE tt_content.pid = [PID] AND  
        
      #wenn an dem Element gar keine Aufgabe hängt, dann soll es immer angezeigt werden
      a.uid IS NULL OR 
      (
          #Hier liegt das Performanceproblem - da ein User mehrmals Antworten abgeben kann, dann wollen wir NUR die neueste Antwort auswerten
          ua.uid = (SELECT MAX(uid) FROM feuserantwort WHERE feuser = [FEUSERID] AND antwort = a.uid) AND
          
          #Ich kann bestimmte Vergleichsoperatoren wählen, mit denen die Feuserantwort verglichen werden soll
          #0 gleich - 1 größer - 2 kleiner - 3 enthält - 4 zwischen - 5 ungleich - 6 nicht leer - 7 Checkboxen in MultiText - 8 leer
          CASE tt_content.antwortbedingung  
              WHEN 0 THEN ua.freitext = tt_content.antwortbedingungswert
              WHEN 1 THEN CONVERT(ua.freitext, SIGNED INTEGER) > CONVERT(tt_content.antwortbedingungswert, SIGNED INTEGER)
              WHEN 2 THEN CONVERT(ua.freitext, SIGNED INTEGER) < CONVERT(tt_content.antwortbedingungswert, SIGNED INTEGER)
              WHEN 3 THEN ua.freitext LIKE CONCAT("%", tt_content.antwortbedingungswert, "%")
              WHEN 4 THEN ua.freitext BETWEEN
                  CONVERT(
                      SUBSTR(tt_content.antwortbedingungswert, 1, LOCATE("|", tt_content.antwortbedingungswert) - 1) ,
                      SIGNED INTEGER
                  )
                  AND 
                  CONVERT(
                      SUBSTR(tt_content.antwortbedingungswert, LOCATE("|", tt_content.antwortbedingungswert) + 1, LENGTH(tt_content.antwortbedingungswert) - 1),
                      SIGNED INTEGER
                  )
              WHEN 5 THEN ua.freitext != tt_content.antwortbedingungswert
              WHEN 6 THEN CHAR_LENGTH(ua.freitext) > 0
              WHEN 7 THEN ua.freitext LIKE "%\"true\"%"
              WHEN 8 THEN ua.freitext IS NULL OR ua.freitext = "" OR ua.freitext = "[]"
          END
       )

Hier nochmal in hübsch auf Pastebin: https://pastebin.com/DGndXLzx

Die Abfrage hat zwei Parameter, die ich euch mit eckigen Klammern gekennzeichnet habe:
Zeile 6 die PID (Page-ID), weil ich möchte ja nur Inhaltselemente dieser bestimmten Seite ausgeben.
Zeile 12 die FE-User-ID des aktuell angemeldeten Nutzers.

In Zeile 12 liegt auch das Performanceproblem, weil das Sub-Select natürlich tausend Male ausgeführt wird. Executionplan sagt im Moment 3847 Mal.
Ich muss aber unbedingt nach der letzten abgegebenen Antwort filtern.
Ein User kann mehrfach Antworten abgeben, weil es eine grafische Auswertung mit Zeitstempel in einem Diagramm geben soll.


Habt ihr hier bitte Anregungen für mich, wie ich auf dieses Sub-Select verzichten kann?


Es grüßt,
der Physikbuddha

 

[Hancock]

Kannst du den subselect nicht in die joins am Anfang reinpacken? A la left outer join feuserantwort on ua.antwort=max(a.uid).

​Eigentlich sollte, wenn du die joins sauber hast, der subselect nur so oft ausgeführt werden, wie du Felder auf einer Seite hast (also wohl < 10), das bedeutet, dass deine joins nicht restriktiv genug sind.

​Das ganze case geswitche würde ich ehrlich gesagt in PHP auslagern, dafür sind DBs nicht gemacht.

 

[itsy]

Eventuell ist es sinnvoller, die Selektion nach der neusten User-Antwort nicht per Subselect im WHERE zu machen, sondern per INNER JOIN (und den existierenden LEFT JOIN ersetzen):

LEFT JOIN (SELECT MAX(uid) maxuid, feuser, antwort FROM feuserantwort GROUP BY feuser, antwort) AS ma ON ma.antwort = a.id
INNER JOIN feuserantwort ua ON ua.uid= ma.maxuid

Ich hole also erst die höchste uid für einen user und eine antwort, und selektiere dann per INNER JOIN nur die antworten mit der maximalen uid für den respektiven Datensatz.

Das müsste der Query Optimizer eigentlich besser optimieren können als einzelne SELECTs...

Probier mal aus ob das hilft

 

[bog]

Lokal sind es noch 0,3 Sekunden (warum auch immer), aber live hab ich ne Ausführungsdauer von über 100 Sekunden.

Nen Index übersehen? Klingt ja nach einem perfekten Einsatzzweck.