An die Mysql Experten

[steff]

Hallo Leute.

Ich habe ein "etwas" leistungshungriges SELECT und wollte einfach mal fragen ob jemand eine Idee hat wie ich das schneller bekomme.


SELECT id,ort, ( Komplexe Formel ) AS entfernung WHERE ( Komplexe Formel ) < 20 ORDER BY entfernung

Die Formel errechnet die Entfernung zwischen 2 Geo Koordinaten.
Sie beinhaltet sowohl feste werte wie auch datenbank Felder.
Einmal brauch ich sie um alle Einträge die sich unter 20 km vom Ausganspunkt befinden zu selecten.

Dann brauch ich sie nochmal um danach zu sortieren.

Leider kann man ja die Select werte nicht im WHERE verwenden sonst könnte ich ja sagen:

WHERE entfernung < 20

Hat jemand eine Idee wie ich den SELECT Formulieren kann ohnen die Formel 2 mal darin zu haben?

Danke im voraus.


MySql: 4.0.22

 

[XChris]

Hallo Steff,

1. Ne Aussagekräftige Überschrift zum Problem wäre für zukünftige Probleme gut.

2. GeoDaten? Es gibt (glaub ich) eine Neuerung für diese Problematik. Ein eigene Datentyp und zugehörige Funktionen die vieles einfacher machen sollen - aber mit gearbeitet hab ich nicht

3. Schnelllösung könnte sein, den Wert Deiner Berechnung in eine MySQL Variable zu speichern. Also ungefähr so:

SET @wert= (Komplexe Formel)
select ...@wert from ... where ...  @wert

Falls Du NICHT sicher gehen kannst, dass alle Deine Aktionen atomar ausgeführt werden, solltest Du ne Transaktion setzen. Sonst kann in Deiner Variable auch mal falsches drinne stehen ...

MySQL Doku Benutzer Variablen


Chris

 

[Mik3e]

Hi,

Ja, ne bessere Überschrift wär gut..

Ich hätte mehrere Ideen zur Performancesteigerung:

1. Kontrolliere deine Indexierung (sofern in der "komplexen Formel" Joins enthalten sind
2. Vielleicht kannst Du die Berechnung aus dem SQL Statement auslagern (und schon davor berechnen)
3. Du kannst das Ergebnis der Berechnung im Select Teil in einer Variable speichern (z.B.: .... AS ergebnis) und am Ende verwendest du HAVING (also HAVING ergebnis<20).

Letzte Variante:
Du kaufst Dir um mehrere tausend Euro ne SUN Hochleistungmaschine mit UNIX

LG
Mike

 

[Mik3e]

Noch was (weil ich gerade "atomar" gelesen habe):
Du kannst mit gezieltem Locking die Performance ebenfalls steigern.
Weiters solltest Du jedenfalls das Query-Caching aktivieren (bringt in den meisten Fällen eine ordentliche Steigerung, da Resultate identer Queries gecached werden).

 

[steff]

Ja stimmt schon das mit der Überschrift..... aber.... so schnell hatte ich noch nie so viele sinvolle Antworten

@XChris :

Zu 2: Ich werde mal schauen ob ich dazu was finde. Falls du einen Link parat hast würde mich freuen.

Zu 3: Das Problem mit der Variable sehe ich darin das sich das Ergebnis der Formel bei jedem Datensatz ändert da die Formel ja Felder aus der Datenbank enthält.
In der DB stehen Länge und Breite des aller Orte und ich übergebe Länge und breite eines festen Orts. Die Formel errechnet daraus die Entfernung zwischen allen orten in der DB und dem Übergebenem Ort. Und gibt nur die zurück die X vom Ausgangsort entfernt sind.
Werde mir die Doku zu den benutzervariablen aber noch zu gemüht fuhren.

@Mik3e :

Zu 1 Damit habe ich die Performance schon mal erheblich gesteigert. Irgendwie ist mir ein Index für ein Postleitzahlenfeld verloren gegangen.

Zu 2 siehe zu 3 bei XChris

Zu 3 Das habe ich versucht. Komischerweise verdoppelt sich dadurch die Select zeit und

Original: Zeige Datensätze 0 - 29 (1155 insgesamt, die Abfrage dauerte 0.0742 sek)
Mit Having: Zeige Datensätze 0 - 29 (1155 insgesamt, die Abfrage dauerte 0.1466 sek)

Zur letzten Variante: Hast nicht zufällig noch so ein Ding irgendwo rum stehen das du nicht brauchst?

Deinen letzten Post habe ich nicht verstanden

 

[Mik3e]

Guten Morgähn...

Zu Having:
Das ist spannend.. Normalerweise habe ich damit immer eine Steigerung erreicht und es war oftmals auch die einzige Möglichkeit. Logisch erscheint mir das nicht (da er ja ohne HAVING die Berechnung doppelt ausführen muss)...

Was mir dazu noch einfällt:
Lege auf jeden Fall einen Index über das Vergleichsfeld (außer du hast fix "<20" im SQL Query und diese Daten werden nicht aus der Tabelle gelesen).

Zu Locking:
Wenn Du Tabellen gezielt sperrst (also für den Zeitraum der Berechnung andere Anfragen auf die betroffenen Tabellen zurückhältst), kannst Du auch noch einiges an Performance rausholen. Abgesehen davon ist es natürlich wichtig, um die Konstistenz der Daten zu wahren (wie schon erwähnt wurde).

Schau mal in der MySQL Doku unter "LOCK TABLES" nach, da findest Du alle Infos...
WICHTIG: Doku bis zum Ende lesen, sonst übersiehst Du vielleicht das "UNLOCKING" und du baust Dir nen schönen Deadlock (ist mir vor langer Zeit mal passiert, als ich mit Locking begonnen habe )

Ansonsten kann ich nur mal im Keller nachsehen, wo ich meine 40.000 Euro SUN Kiste hingeräumt hab und sie bei eBay reinstellen...

Aber ich kann mir das nicht erklären. Ich mache auch komplexe Berechnungen und teilweise Joins über 10 oder mehr Tabellen, und hatte noch nie Probleme mit der Performance (trotz mehrere Millionen Datensätze).

Bisher waren eigentlich immer "schlechte" Queries oder fehlerhafte Indexierung der Grund für Performanceeinbrüche.
Vielleicht kannst Du ja mal den ganzen SQL Query posten (mit Berechnung)...
Würd mich interessieren.

LG
Mike

 

[steff]

Wie schon geschrieben habe ich durch den Index auf die PLZ die Geschwindigkeit ver 13 facht.

Die Performance ist jetzt gut, Trotzdem kann ein bissel mehr Geschwindigkeit nie schaden

Danke an alle für eure Hilfe.

 

[XChris]

Also ich bin sehr neugierig, kannste nicht mal nen Ausschnitt des ER Diagrammes posten und dazu Deine SQL Statement. Das klingt alles sehr spannend.

Chris

 

[Mik3e]

Ja, ERD + Query wären spannend.... Würd mich auch interessieren.. Weil ich verwende häufig komplexe String Operationen (die eigentlich enorme Performance Fresser sind), und dennoch macht die eigentliche Berechnung nur ca. 1% in Relation zum eigentlichen Auslesen der Daten aus...