Abfrage Wert unter Meßkurve

[Stephan Huber]

Hallo,

folgendes Problem: ich habe eine Datenbank (MySQL) mit Meßergebnissen für verschiedene Motoren, die die Leistung bei einer bestimmten Drehzahl enthält, und nun will ich für eine vorgegebene Drehzahl alle Motoren finden, die eine bestimmte Leistung liefern können, also unter der Meßlinie liegen.
Die Tabelle, auf die es ankommt, sieht ungefähr so aus:

|parentid|drehzahl_min|leistung_min|drehzahl_max|leistung_max|
|    1   |     300    |    1,5     |     400    |      2     |
|    1   |     400    |     2      |     450    |      3     |

parentid ist die ID der Messung. Ich will genau selektieren, d.h. wenn nach der Drehzahl 366 gesucht wird, soll die Abfrage "interpolieren", was die Leistung an diesem Punkt ist, und nur die Motoren liefern, die an diesem Punkt drunterliegen. Das habe ich auch hinbekommen, in der Abfrage stehen dann im where-clause halt Ausdrücke wie:

(leistung_min+(leistung_max-leistung_min)/(drehzahl-drehzahl_min)*(100-drehzahl_min))>3

...und das ist das Problem: damit kann die Datenbank keinen Index mehr verwenden, und das macht das ganze potentiell sehr langsam.

Hat jemand eine Idee, wie man das mit der Verwendung eines Index hinbekommen könnte?

Viele Grüße
Stephan

[Philipp Hasenfratz]

Halihallo Stephan

...und das ist das Problem: damit kann die Datenbank keinen Index mehr verwenden, und das macht das ganze potentiell sehr langsam.
Hat jemand eine Idee, wie man das mit der Verwendung eines Index hinbekommen könnte?

Ja, aber wohl nur über eine interpolierte Spalte, oder die Verwendung einer anderen
Tabelle, die die Messungen geeignet(er) abbildet.

Erstelle eine neue Tabelle, die bereits vorberechnete Daten enthält und zwar so, dass
du über die wichtigen Spalten einen Index legen kannst und dieser dann auch benutzt wird.
http://www.mysql.com/doc/en/INSERT_SELECT.html
wird dir beim erstellen und abbilden der neuen Tabelle helfen.
Kleiner Tipp: Wenn du so viele Daten hast: Erst die Tabelle _ohne_ Index erstellen, dann
die Daten berechnen und einfügen und am _Ende_ den Index hinzufügen. So geht a) das
Einfügen wesentlich schneller und b) der Index ist schön ausgeglichen (=> performanter).

Vorschlagen würde ich etwa folgendes (aber ich hab deine Aufgabe nicht gross studiert):

parentid | drehzahl_avg | leistung_avg | leistungs_koeffizient

wobei du einen Index (nicht unique) auf drehzahl_avg, leistung_avg und koeff hast.

Viele Grüsse

Philipp

[Stephan Huber]

Hallo Philipp,

Ja, aber wohl nur über eine interpolierte Spalte, oder die Verwendung einer anderen
Tabelle, die die Messungen geeignet(er) abbildet.

Das hatte ich mich nach dem Posten auch schon mal angedacht, aber wenn ich eine vernünftige interpolierte Tabelle erstelle, habe ich statt 10.000 Datensätzen 250.000 (der Verlauf der Meßkurven ist in einigen Bereichen relativ stetig, aber mit großer Steigung, so daß im Moment 1 Datenpunkt ausreicht, bei der Interpolation bräuchte ich 20-50). Das macht dann wohl den Vorteil eines Index wieder zunichte :-(.

Viele Grüße
Stephan

[Philipp Hasenfratz]

Halihallo Stephan

Ja, aber wohl nur über eine interpolierte Spalte, oder die Verwendung einer anderen
Tabelle, die die Messungen geeignet(er) abbildet.

Das hatte ich mich nach dem Posten auch schon mal angedacht, aber wenn ich eine vernünftige interpolierte Tabelle erstelle, habe ich statt 10.000 Datensätzen 250.000 (der Verlauf der Meßkurven ist in einigen Bereichen relativ stetig, aber mit großer Steigung, so daß im Moment 1 Datenpunkt ausreicht, bei der Interpolation bräuchte ich 20-50). Das macht dann wohl den Vorteil eines Index wieder zunichte :-(.

Dann interpoliere gar nicht. Verwende die vorgeschlagene Abbildung der Messdaten und
selektiere über zwei Queries; und zwar so, dass die möglichst nächste obere und untere
Schranke findest (über ORDER BY mit LIMIT 1,0 kann man das nächst passende "objekt"
selektieren), du erhälst dann zwei Werte und _diese_ kannst du dann interpolieren, dass
sie der Drehzahl angepasst sind. So kannst du den Index verwenden, hast keinen Datenmüll
und hast dennoch akzeptable Ergebnisse.
Ein Performancegewinn geht hier wohl leider etwas auf Kosten der Messgenauigkeit, aber
das ist allgemein ein Problem mit dem alle zu kämpfen haben.

Ach ja, mit der ORDER BY und LIMIT "Lösung" kannst du natürlich beliebig viele (also
nicht nur 1 Record) selektieren, je mehr, desto genauer dass Messergebnis, aber desto
langsamer die SQL-Abfrage.

Viele Grüsse

Philipp

[Stephan Huber]

Hallo Philipp,

selektiere über zwei Queries; und zwar so, dass die möglichst nächste obere und untere
Schranke findest (über ORDER BY mit LIMIT 1,0 kann man das nächst passende "objekt"
selektieren

Ich glaube Du hast da was mißverstanden: In der Tabelle stehen alle Meßdaten aller Motoren, und ich brauche in einer Abfrage die entsprechende Leistung für jeden Motor, der im gesuchten Drehzahlbereich die gesuchte Leistung liefern kann.
Das geht mit Deiner Lösung nicht, wenn ich sie richtig verstande habe.

Viele Grüße
Stephan

[Philipp Hasenfratz]

Halihallo Stephan

selektiere über zwei Queries; und zwar so, dass die möglichst nächste obere und untere
Schranke findest (über ORDER BY mit LIMIT 1,0 kann man das nächst passende "objekt"
selektieren

Ich glaube Du hast da was mißverstanden: In der Tabelle stehen alle Meßdaten aller Motoren, und ich brauche in einer Abfrage die entsprechende Leistung für jeden Motor, der im gesuchten Drehzahlbereich die gesuchte Leistung liefern kann.
Das geht mit Deiner Lösung nicht, wenn ich sie richtig verstande habe.

Nun, du möchtest die Daten interpolieren. Der Weg über eine WHERE-Klausel ist ungenügend,
wenn du statistische Daten aufbereiten möchtest. Mathematisch korrekter wäre eine
Durchschnittsberechnung der nächstliegenden Daten und diese bekommst du über die genannte
ORDER BY und LIMIT Lösung. Aber vielleicht habe ich dich missverstanden und du hast
bereits eine sortierte Liste an Daten vorliegen (ich ging davon aus, dass du mehrere
"redundante" Messergebnisse hast und daraus eine Messkurve berechnen möchtest, nicht,
dass du dich auf die Daten in der Datenbank "verlässt").

Gehen wir von deiner ursprünglichen Kalkulation aus:

(leistung_min+(leistung_max-leistung_min)/(drehzahl-drehzahl_min)*(100-drehzahl_min))>3

leistung_min+(leistung_max-leistung_min)

ich schätze du meintest: (leistung_min+leistung_max) / 2 ? - Durchschnitt der Leistung?

diesen Wert lässt sich vorberechnen und in einer Spalte abbilden, auf die du einen Index
legen kannst. Somit kannst du sehr schnell die gesuchte Leistung finden.

was willst du eigentlich mit (...)/(drehzahl-drehzahl_min)*(100-drehzahl_min))>3
bezwecken? - Diese WHERE-Klausel macht IMHO keinen Sinn. Leistung/Drehzahl ist kein
mir bekannter Kennwert eines Motors. (korrigier mich, wenn ich irre!)

---

Nochmals von vorne: ;)
Du suchst eine Möglichkeit Indizies zu verwenden, um schnell zu Resultaten zu kommen.
Was spricht dagegen, die durchschnittliche Leistung und Drehzahl zu indizieren und
dann über eine WHERE-Klausel die gesuchte Drehzahl einzuschränken (+-100 z. B.)? -
Damit verkleinerst du die Datenmenge beträchtlich und kannst dann die verbleibenden
Daten ohne Probleme interpolieren (ohne einen Tag auf Resultate warten zu müssen).
Ich möchte jedoch bezweifeln, dass du über eine einfache WHERE-Klausel bzw. einfache
Berechnungen im SELECT-Stetement zu korrekten, vollständigen Daten kommen wirst.
Datenbanken dienen nicht umbedingt dazu, Daten zu interpolieren und mit nicht existenten
Daten zu hantieren (Datenbanken sind _Mengenorientiert_, sie basieren nicht auf
Funktionen, mitdenen du Daten hochrechnest).

Nun ja, irgendwie habe ich dein Problem wohl noch immer nicht ganz verstanden, deshalb
einige grundlegende Tipps, die dir vielleicht helfen:

Grundsatz lautet: Möglichst schnell unnötige Daten "rausschmeissen", d. h. über Indizies
und geeignete Abfragen den Datenbestand auf unmittelbar nützliche Daten zu reduzieren.
z. B. der Tipp mit dem Drehzahl-Bereich (wenn du die Leistung zu Drehzahl 366
interpolieren möchtest, brauchst du keine Werte über Leistung bei Drehzahl 1025).

Über Berechnete Spalten kann man nicht direkt einen Index legen. Wenn dieser Index
nützlich ist, muss die Abfrage in eine andere Tabelle abgebildet werden und _dort_ kann
man einen Index auf die transformierte (berechnete) Spalte legen.

Dein Problem ist mehrschichtig: Du versuchst a) relevante Daten zu selektieren und
b) diese nützlichen Daten hochzurechnen (interpolieren). Das sind zwei Paar Schuhe und
erzwingen meiner Ansicht nach auch _mindestens_ zwei SQL-Queries und eine Hilfstabelle.

Wenn du eine Script-Spache (Perl/PHP/...) verwendest, würde ich vorschlagen, dass du
die unmittelbar relevanten Daten aus der Datenbank holst und im Script diese reduzierten
Daten interpolierst. Ich würde desweiteren Vorschlagen, dass du eine neue Tabelle
anlegst, wo du die Leistungssteigerung (tangential zur Messkurve => leistung/drehzahl od.
umgekehrt) zu jeder Leistung oder Drehzahl (je nach dem, wonach du suchst) aufträgst.
Aufgrund dieser Kennzahl kannst du die Leistung zu einer Drehzahl einfach interpolieren.
Finden den nächst unteren/höheren Drehzahlbereich, lese den Leistungssteigerung/Drehzahl
Koeffizienten und berechne daraus die Leistung zur gesuchten Drehzahl. Damit kannst du
den Index über Drehzahl verwenden (schnelles auffinden des Koeffizienten) und über den
Koeffizienten kannst du schnell die interpolierte Leisung ermitteln.

Bist du eigentlich in der Zwischenzeit schon weitergekommen? - Was hast du gemacht?

Viele Grüsse

Philipp

[Stephan Huber]

Hallo Philipp,

Nun, du möchtest die Daten interpolieren.

Ja, aber nur linear zwischen jeweils zwei Meßpunkten. Auf die Meßdaten selbst verlasse ich mich. Es geht nur darum, daß ein Punkt die Leistung bei 300 U/min ist, der nächste bei 400 U/min, und wenn nun jemand nach allen Motoren sucht, die bei 366 U/min eine bestimmte Leistung haben, dann muß ich zwischen den beiden linear interpolieren - in der where-Bedingung, nicht nachher. Deswegen habe ich in jeder Tabellenzeile jeweils zwei aufeinanderfolgende Meßwerte, drehzahl_min/leistung_min und drehzahl_max/leistung_max:

(leistung_min+(leistung_max-leistung_min)/(drehzahl_max-drehzahl_min)*(100-drehzahl_min))>3

Das ist die lineare Interpolation zwischen zwei Meßpunkten, 100 ist der Drehzahlwert, den der Benutzer eingegeben hat.

Nun ja, irgendwie habe ich dein Problem wohl noch immer nicht ganz verstanden,

das befürchte ich auch ;-)

aber das ist das grafische Ergebnis der Abfrage, vielleicht macht es das klarer, die roten Linien sind die vom User eingebene Drehzahl und die minimale Leistung, die drei Linien sind die Meßdaten verschiedener Motoren (wahrscheinlich war auch die Bezeichnung "Leistung" irreführend, eigentlich ist es das Drehmoment, so steht es auch in den Abfragen, aber ich dachte mir, "Leistung" ist verständlicher, Zahl ist Zahl, war wohl ein Fehler):

<img src="http://analogon.com/private/kennlinien_beispiel.png" border="0" alt="">

Bist du eigentlich in der Zwischenzeit schon weitergekommen? - Was hast du gemacht?

Im Prinzip: festgestellt, daß es sowieso nicht geht, daß MySQL für die komplizierteren Abfragen auch Indizes verwendet, selbst wenn ich Indizes über mehrere Spalten verwende, und die Abfragen vereinfache, weil zu dieser Teilabfrage immer noch ein paar joins mit anderen Tabellen kommen (die joins gehen natürlich über indizes).
Ich mache jetzt bei ein paar komplizierteren Abfragen, wo ich noch mehrere große Tabellen dazujoine, vorher eine einfachere Abfrage, die die genaue Abfrage ausreichend limitiert, das reicht von der Performace dann aus.

Viele Grüße
Stephan

[Philipp Hasenfratz]

Halihallo Stephan

Nun, du möchtest die Daten interpolieren.

Ja, aber nur linear zwischen jeweils zwei Meßpunkten. Auf die Meßdaten selbst verlasse ich mich. Es geht nur darum, daß ein Punkt die Leistung bei 300 U/min ist, der nächste bei 400 U/min, und wenn nun jemand nach allen Motoren sucht, die bei 366 U/min eine bestimmte Leistung haben, dann muß ich zwischen den beiden linear interpolieren - in der where-Bedingung, nicht nachher. Deswegen habe ich in jeder Tabellenzeile jeweils zwei aufeinanderfolgende Meßwerte, drehzahl_min/leistung_min und drehzahl_max/leistung_max:

OK, _jetzt_ verstehe ich (zumindest das?) ;)

(leistung_min+(leistung_max-leistung_min)/(drehzahl_max-drehzahl_min)*(100-drehzahl_min))>3

Das ist die lineare Interpolation zwischen zwei Meßpunkten, 100 ist der Drehzahlwert, den der Benutzer eingegeben hat.

Nur aus interesse: Woher kommt die Drei? - Ist das eine von dir festgelegte Schwelle?

Ich mache jetzt bei ein paar komplizierteren Abfragen, wo ich noch mehrere große Tabellen dazujoine, vorher eine einfachere Abfrage, die die genaue Abfrage ausreichend limitiert, das reicht von der Performace dann aus.

Das war u.a. mein Vorschlag. Vielleicht hast du es bereits auch schon:
(leistung_min+(leistung_max-leistung_min)/(drehzahl_max-drehzahl_min)*(100-drehzahl_min))>3

ist eine "komplexe" Berechnung, die natürlich nicht über den Index verarbeitet werden
kann. Jedoch, wenn du eine einfache Abfrage, wie z. B. WHERE
drehzahl_min<=drehzahl_gewünscht-50 und drehzahl_max>=drehzahl_gewünscht+50
vorschaltest, wird der QueryOptimizer evtl. den Index verwenden können und so die
relevanten Daten zuerst auslesen, bevor komplexere Berechungen durchgeführt werden. Wie
bereits gesagt: Das Ziel ist, möglichst viel bereits vorher rauszuschmeissen.

Viele Grüsse

Philipp

[Stephan Huber]

Hallo Philipp,

Nur aus interesse: Woher kommt die Drei? - Ist das eine von dir festgelegte Schwelle?

Nein, die gibt auch der User ein.

Jedoch, wenn du eine einfache Abfrage, wie z. B. WHERE
drehzahl_min<=drehzahl_gewünscht-50 und drehzahl_max>=drehzahl_gewünscht+50
vorschaltest, wird der QueryOptimizer evtl. den Index verwenden können und so die
relevanten Daten zuerst auslesen, bevor komplexere Berechungen durchgeführt werden.

So ungefähr dachte ich mir das auch, und habe es über das Wochenende ausprobiert. Das Problem dabei ist, daß das nur über einen mehrspaltigen Index funktionieren würde (über drehzahl_min, drehzahl_max, drehmoment_min, drehmoment_max). Der geht aber, soweit ich die MySQL-Dokumentation verstande habe (ausprobiert habe ich es auch), nur, wenn der Query nur aus AND-Bedingungen besteht, wo in jedem einzelnen Teil dann immer ein Teil des Index vorkommen muß. In dem Query ist aber immer notwendig ein OR, weil die Kurve ja steigend oder fallend sein kann, d.h. der erste oder der zweite Punkt kann unter dem gesuchten Grenzwert liegen, deswegen bin ich mir relativ sicher, daß ich keinen Index verwenden kann. (d.h. wenn ich nur Intervalle suche, wo beide drüber liegen, was mit einem mehrspaltigen Index gehen würde, lasse ich notwendig einige Möglichkeiten aus)
D.h. dieser Teil es Query ist geblieben wie er ist, das geht halt nicht anders, und die Performance ist auch ausreichend, was mir eigentlich Sorgen gemacht hat, war die Performance, wenn ich die anderen Tabellen joine, z.B. mögliche Getriebe, mit denen man die nötige Leistung erreichen könnte, die schränke ich jetzt vorher entsprechend ein.

Viele Grüße
Stephan

[Philipp Hasenfratz]

Halihallo Stephan

Jedoch, wenn du eine einfache Abfrage, wie z. B. WHERE
drehzahl_min<=drehzahl_gewünscht-50 und drehzahl_max>=drehzahl_gewünscht+50
vorschaltest, wird der QueryOptimizer evtl. den Index verwenden können und so die
relevanten Daten zuerst auslesen, bevor komplexere Berechungen durchgeführt werden.

So ungefähr dachte ich mir das auch, und habe es über das Wochenende ausprobiert. Das Problem dabei ist, daß das nur über einen mehrspaltigen Index funktionieren würde (über drehzahl_min, drehzahl_max, drehmoment_min, drehmoment_max).

Wie kommst du zu dieser Schlussfolgerung? - In deinem Falle sind Indizies für jede
Spalte sinnvoller.

Der geht aber, soweit ich die MySQL-Dokumentation verstande habe (ausprobiert habe ich es auch), nur, wenn der Query nur aus AND-Bedingungen besteht, wo in jedem einzelnen Teil dann immer ein Teil des Index vorkommen muß.

http://www.mysql.com/doc/de/MySQL_indexes.html

In dem Query ist aber immer notwendig ein OR, weil die Kurve ja steigend oder fallend sein kann, d.h. der erste oder der zweite Punkt kann unter dem gesuchten Grenzwert liegen, deswegen bin ich mir relativ sicher, daß ich keinen Index verwenden kann. (d.h. wenn ich nur Intervalle suche, wo beide drüber liegen, was mit einem mehrspaltigen Index gehen würde, lasse ich notwendig einige Möglichkeiten aus)

Was geschieht, wenn du für jede Spalte einen Index anlegst?
Versucht einmal ein

EXPLAIN SELECT ...

der gibt Auskunft, ob der Index dann verwendet werden kann. IMHO sollte das auch bei
OR-Verknüpfungen keine Rolle spielen, ist einfach eine Vereinigung der Teilmenge der
passenden Index-Einträge deiner Abfrage, das sollte der QueryOptimizer von MySQL
IMHO verstehen. Aber vielleicht unterlässt er es, da er a) schon davon ausgeht, dass
die Vereinigungsmenge keinen Performancegewinn mehr bringt (full table scan schneller)
oder b) dass er merkt, dass mehr als 30% der Daten selektiert werden würden.

Vielleicht hast du noch Zeit und Lust dies auszuprobieren, ein Ergebnis würde mich auf
jeden Fall interessieren, ansonsten...

... Viele Grüsse

Philipp

[Stephan Huber]

Hallo Philipp,

Was geschieht, wenn du für jede Spalte einen Index anlegst?
Versucht einmal ein

EXPLAIN SELECT ...

der gibt Auskunft, ob der Index dann verwendet werden kann.

Das hatte ich gemacht, in so ziemlich allen Kombination, und er hat den Index nie verwendet, außer ich habe den Query soweit vereinfacht, daß er nutzlos für mich war. (sowohl mit Mehrspalten-Index als auch mit Indizes auf alle Spalten).

Viele Grüße & trotzdem Danke für Deine Bemühungen
Stephan