Optimierung MySQL-Query

[bobby]

Moin,

Frage an die Profis: kann man das noch optimieren? Grundproblem, ich hab eine Tabelle entries und ich möchte einen Eintrag ausgeben und auch den Vorgänger und den Nachfolger in einer sortierten Liste bestimmen (hier anhand price sortiert). Die Abfrage funktioniert. Allerdings ist sie recht komplex. geht es vielleicht noch eleganter?

SELECT k.*, @c l 
  FROM 
    (SELECT * FROM entries ORDER BY price DESC) k, 
    (SELECT @c := 0 AS u FROM DUAL) p, 
    (select d.pos FROM 
      (SELECT @k := @k+1 AS pos, IF(f.id = 1286, 1, 0) AS test FROM 
        (SELECT * from entries ORDER BY price DESC) f, 
        (SELECT @k := 0 FROM DUAL) AS c 
      GROUP BY test ORDER BY test DESC LIMIT 0,1) d
    ) u 
WHERE (@c := @c+1) BETWEEN u.pos -1 AND u.pos +1

Gruß Bobby

[ursus contionabundo]

geht es vielleicht noch eleganter?

Ja. Hast Du in den ersten Jahren des letzten Jahrzehnts des vorigen Jahrtausends mal Basic programmiert? Da gab's nur kurze Variablen. Heute nimmt man gerne längere, sprechende Namen.

[bobby]

Moin,

Ok... Namen könnten sprechender sein... Hinweis ist angekommen... ich meinte aber eher eleganter in Richtung Performance oder Codekürzung.

Gruß Bobby

[Rolf B]

Hallo bobby,

1. welche MYSQL-Funktion verwendest Du?
2. kannst Du MYSQL Routinen (Stored Procedures) verwenden? Sowohl das Knowhow als auch von die DB-Rechte betreffend?
3. muss es unbedingt die Query sein oder hast Du ein nachgelagertes Stück Programm, dass die Daten weiterverarbeitet?

Rolf

[bobby]

Moin,

welche MYSQL-Funktion verwendest Du?

Die Frage versteh ich nicht. Ich geh jetzt davon aus, dass du die Version wissen wolltest: 5.6

kannst Du MYSQL Routinen (Stored Procedures) verwenden? Sowohl das Knowhow als auch von die DB-Rechte betreffend?

Könnte ich. Inwieweit würde dies den query beschleunigen?

muss es unbedingt die Query sein oder hast Du ein nachgelagertes Stück Programm, dass die Daten weiterverarbeitet?

Ja, es muss ein query sein. Möchte gern die Daten qualifiziert haben. Im Moment hab ich eine recht komplexe PHP - Anwendung auf eine Antwortzeit um die 200ms beschleunigt. Von daher möchte ich da nicht allzuviel Code dazu packen. Mein oben genannter query wird übrigens in 0,04s ausgeführt.

Gruß Bobby

[Rolf B]

Hallo bobby,

welche MYSQL-Funktion verwendest Du?

Die Frage versteh ich nicht. Ich geh jetzt davon aus, dass du die Version wissen wolltest: 5.6

Äh ja... 😳 - viele Grüße, Siegmund Freud. Ich wollte die Version wissen, um im Fall der Version 8 die RANK-Funktion vorschlagen zu können. In 5.6 gibt's die noch nicht.

kannst Du MYSQL Routinen (Stored Procedures) verwenden? Sowohl das Knowhow als auch von die DB-Rechte betreffend?

Könnte ich. Inwieweit würde dies den query beschleunigen?

Meine Idee wäre, diese komplexe Query aufzuteilen und das Herumrechnen mit Variablen in der Query zu entfernen. Aus meiner Sicht ist das Updaten von Variablen in einer Query fragwürdig, weil Du Dich damit an einen bestimmten Execution Plan bindest. Ändert der sich, ändert sich ggf. auch die Art wie die Variablen aktualisiert werden. Ohne Routinen bedeutet das mehrere Server-Roundtrips, mit Routinen bleibt es im Server.

muss es unbedingt die Query sein oder hast Du ein nachgelagertes Stück Programm, dass die Daten weiterverarbeitet?

Ja, es muss ein query sein. Möchte gern die Daten qualifiziert haben. Im Moment hab ich eine recht komplexe PHP - Anwendung auf eine Antwortzeit um die 200ms beschleunigt. Von daher möchte ich da nicht allzuviel Code dazu packen. Mein oben genannter query wird übrigens in 0,04s ausgeführt.

40ms für dieses Monster ist schnell - dann hast Du entweder eine kleine Table oder die DB hat wenige Nutzer und kann alles cachen. Wenn Du viele Daten hast, musst Du diese Query definitiv unter Last beobachten. Wenn Du auf dem Preis keinen Sekundärindex hast, solltest Du überlegen, ob man einen anlegt - dann aber den möglichen Einfluss auf Insert/Update/Delete-Statements beachten.

Ich hatte gestern auf meiner lokalen MySQL 5.6 Installation etwas zum Thema rumgespielt - ohne wirklichen Erfolg - und dabei hat sich mir die Frage gestellt, was Du bei Sätzen machst die den gleichen Preis haben. Deine existierende Vorgänger/Nachfolger Logik würde da undefiniert arbeiten; je nach dem, ob die gesuchte ID in einer Gruppe gleicher Preise die erste, letzte oder eine mittlere ist, würden Vorgänger und Nachfolger einen gleichen oder anderen Preis haben. Ob das für Dich ein Problem darstellt, hängt natürlich von deinen Daten ab, das kannst Du nur selbst wissen.

Wenn man die Aufgabe auf mehrere Queries verteilt, würde ich das so machen:

select @preis := preis from entries where id=@id;
select @low_id  := id, preis from entries where preis < @preis order by preis desc limit 1;
select @high_id := id, preis from entries where preis > @preis order by preis      limit 1;

select * from entries where id in (@low_id, @id, @high_id);

Die Queries 2 und 3 könnte man auch als Subselect in Query 4 integrieren. Query 1 nicht (oder du müsstest sie doppeln). Das bringt - meiner Meinung nach - aber nur dann wirklich Nutzen, wenn man damit Server-Anfragen spart; deswegen die Frage nach der Routine.

Kannst ja mal gucken ob deine Abfrage auf diesem Weg schneller wird. Auf jeden Fall ist sie lesbarer!

Rolf

[bobby]

Moin,

Ich muss ja aber irgendwie hochzählen. und das 2 mal. 1 mal um zu ermitteln an welcher Position das gewählte Element sitzt und das 2. Mal um die Elemente an den angrenzenden Positionen bestimmen zu können. Ich wüsste nicht wie ich dies sonst bewerkstelligen soll. Die Zählvariablen sind die einzigen Variablen

40 ms für ca 2000 Datensätze. Ich hab dies jetzt mal mit 25TSD Datensätzen getestet. Da ist die Abfragezeit bei 250 ms. Also durchaus noch Luft nach oben.

Da die beiden Querys für die Liste gleich sind, ist auch die Reihenfolge gleich.

Ich werde noch ein wenig rum probieren.

Aber Danke für die Hinweise

Gruß Bobby

[Rolf B]

Hallo bobby,

nein, zählen ist nicht zwingend nötig. Das ist prozedurale Denke - SQL kann auch anders. Guck dir meine 4-Query Lösung mal genauer an:

1. Preis des Referenz-Eintrags aus der ID bestimmen (bei Dir die 1286)

select @preis := preis from entries where id=@id;`

2. IDs der Einträge bestimmen, die dem Referenzeintrag preislich am nächsten kommen

select @low_id  := id, preis from entries where preis < @preis order by preis desc limit 1;
select @high_id := id, preis from entries where preis > @preis order by preis      limit 1;

3. Die Rows der gefundenen IDs zurückgeben (habe noch ein ORDER BY hinzugefügt):

select * from entries where id in (@low_id, @id, @high_id) order by preis;

Ob es unter MYSQL effizient ist, die Queries 2 und 3 als Subselect in Query 4 einzubetten, kann ich nicht sagen. Unter MS SQL Server geht die Query damit komplett in die Knie (es sei denn ich lege einen Index auf den Preis).

Ein Index auf den Preis senkt in meiner Testtabelle (MS SQL Server, Spalten ID, Preis und 600 Zeichen Dummydaten, Clustered Index auf ID, 50000 Zeilen mit zufälligen Preisen) die Ausführungszeit von 30ms auf 5ms (erster Call) und danach 1ms.

Rolf

[bobby]

Moin,

OK... 2 Probleme:

1. ich vergaß zu erwähnen dass auch andere Sortierungen vorkommen können
2. ich wollt nicht mit mehreren selects arbeiten.

übrigens hab ich nen Index auf Preis.

Ich habe gerade etwas rum probiert und dabei festgestellt, dass das äußere Query das teure ist, weil da die Liste komplett hochgezählt wird. das ist selbstverständlich teuer. Ich glaube ich habe eine IDee die ich noch probieren möchte. Ansonsten muss ich mit den 200 ms leben.

Gruß Bobby

[Rolf B]

Hallo bobby,

ich vergaß zu erwähnen dass auch andere Sortierungen vorkommen können

Dann hilft ein Index auf den Preis allein natürlich nichts...

ich wollt nicht mit mehreren selects arbeiten.

Deswegen ja der Vorschlag mit der Routine; aus PHP heraus ist es dann ein Aufruf. Was damit natürlich nicht geht ist ein freies Zusammenbauen der Query aus PHP heraus; du musst dann für jede Sortierung eine eigene Routine vorhalten (oder eine Routine mit einer Weiche drin für die richtigen Queries). Performance ist keine Frage von "ich will aber nicht", sondern von "was muss sein".

Ansonsten muss ich mit den 200 ms leben.

Du nicht. Deine Anwender. Wieviele Anwender verwenden diese Query wie oft? Wenn wir hier von einer Belastung reden, die auf einen Webrequest pro Sekunde hinausläuft, ist es egal. Hast Du tausende von Anwendern und einen Hagel von 100 Requests pro Sekunde oder mehr, kann der Unterschied zwischen 20 und 200 Millisekunden den Unterschied zwischen "schicke Seite, kann man empfehlen" und "grottenlahmes Ding, geh woandershin" bedeuten. Bzw. den Unterschied zwischen "ich brauche einen kleinen VServer" und "ich brauche eine fette Serverfarm".

Es ist natürlich deine Entscheidung, wie Du dein System baust. Vielleicht hat jemand anderes für Dich Hinweise, die eine bessere Ein-Select Abfrage für die Aufgabe ermöglichen.

Rolf

[bobby]

Moin,

So, ich möchte eine komfortablere und elegantere sowie performantere Lösung nicht vorenthalten.

SELECT * FROM entries WHERE id IN  (
    SELECT d.id FROM 
      ( SELECT f.id, @k := @k+1 AS pos, if(id = 88900 , @y := @k, @y:=@y ) FROM 
         (SELECT id FROM entries ORDER BY price DESC) f, 
         (SELECT @k := 0 FROM DUAL) AS c 
      ) d 
    WHERE d.pos BETWEEN @y-1 AND @y+1
) LIMIT 0,3

Ausführungszeit bei 25 TSD Datensätzen: 40 - 60ms

Gruß Bobby

[Rolf B]

Hallo bobby,

jau, in der Richtung hatte ich experimentiert, es aber nicht hinbekommen (ich wollte den @y Wert in der Where-Bedingung zum Abbrechen der Suche verwenden).

Möchtest Du spaßeshalber mal die Laufzeit meiner 4 Selects messen?

Rolf

[bobby]

Moin,

Sorry, das wäre mir jetzt ein wenig zu aufwändig, da ich dazu mein eigentliches ORM-Konstrukt verlassen müsste. Ich hoffe du verstehst das.

Ich danke dir auf alle Fälle für deine Hilfe.

Gruß Bobby

[bobby]

Moin,

So, hab nun spaßeshalber doch nochmal getestet, direkt auf dem MySQL-Server. Mit den 4 Abfragen sind wir bei 80ms.

Also keine weitere Zeitersparnis. sondern im Gegenteil.

Gruß Bobby