MySQL Scipt Laufzeit
- Thread starter csigg
- Start date
amnesie
New Member
Die Anforderung, auch alle Zutaten im Text finden zu können (quasi Volltextsuche) ist nicht grade freundlich in Sachen Performance. Überleg dir mal, ob du nicht die Funktionen zur Volltextsuche nutzen magst, die MySQL für die MYISAM-Datenbanken anbietet.
M
Mercenary
Guest
Hallo.
Ich versuch mich kurz zu fassen ;>
Ich habe mir jetzt nochmal die DDL der Tabellen mit etwas mehr Ruhe angesehen und glaube zu verstehen wie das gedacht war.
Wenn ich da richtig liege, dann ist in Zutaten zu jedem Rezept je ein Eintrag pro verwendeter Zutat zu erwarten sprich,
wenn die 300 Rezepte alle mit Salz zu tun haben, dann hat Zutaten auch 300 Zeilen mit Salz?
Also wenn Du denn selbst Programm und Datenbank erstellst/programmierst solltest Du das Statement auf jeden Fall dynamisch erstellen und keine joins machen, wo nichts gesucht wird.
Ein Statement wie Du es brauchst wenn ein gesuchtes Rezept jeweils alle angegebenen Zutaten auch enthalten soll mit 5 gegebenen Zutaten sieht ungefaehr so aus
(wobei ich bitte das konkret gegen MySQL zu checken, da bin ich nicht fit mit! Also bitte nochmal verifizieren!):
Das ist ein Vorschlag fuer den IST-Zustand mit "quasi Volltextsuche" -> Ein Pfefer und ein Pfeffer in der Zutaten-Tabelle fuehrt da aber auch zu Ungenauigkeiten;> .
Ein Verbesserungsvorschlag fuer Performance und Bedienung:
Die Stammdaten der Zutaten sollten tatsaechlich (ID, Name evtl Beschreibung, das waere ein schoenes Feature fuer Texte wie "Lieber zwei kleine als einen grossen Kohlrabi nehmen, der ist holziger")
separat in einer Tabelle stehen;
Das Datum "Menge" gehoert eigentlich zu einer "Rezept-Position" die technisch eine Verbindung zwischen einem Rezept und einer Zutat abbildet.
Das kaeme in eine dritte Tabelle, in der dann die ID des Rezeptes, die ID der Zutat und die Menge in je einer Zeile stehen, Index ueber die zwei ID Spalten.
Die Rezept-ID kann dann neben der Menge auch in den Zutaten entfallen.
Im Endeffekt soll dann in der Zutaten Tabelle jede Zutat nur einmal stehen.
Im Programm / Frontend koennen dann beim Erfassen eines Rezeptes die Zutaten nacheinander aus einer Liste aller bekannten Zutaten ausgewaehlt oder bei der Gelegenheit eine neue Zutat angelegt werden.
Zum Suchen von Rezepten nach Zutaten die zu suchenden Zutaten aus einer Liste aller bekannter Zutaten auswaehlen lassen.
Ggfs kann man auch noch die Moeglichkeit einraeumen entweder die Zutaten auszuwaehlen oder einen freien Text als Name einzugeben,
dort wo der Text steht suchst Du halt zuerst die Zutaten aus den Stammdaten und kannst dann mit den ID's die Rezepte suchen, bei denen aus der Liste solltest Du die ID's schon haben.
Bei der veraenderten Datenstruktur hast Du insgesamt weniger Daten, solche Queries wie oben verarbeiten dann auch weniger Daten (wegen weniger Zeilen in Zutaten und dort folglich weniger Text in der Spalte Name)
und der Join der dann Rezepte und Zutaten zusammenbring kommt in Gaenze mit ID's aus => sehr schnell bei Vergleichsoperationen.
Ich wollte mich kurz fassen ;>
Ciao,
Mercy.
Ich versuch mich kurz zu fassen ;>
Ich habe mir jetzt nochmal die DDL der Tabellen mit etwas mehr Ruhe angesehen und glaube zu verstehen wie das gedacht war.
Wenn ich da richtig liege, dann ist in Zutaten zu jedem Rezept je ein Eintrag pro verwendeter Zutat zu erwarten sprich,
wenn die 300 Rezepte alle mit Salz zu tun haben, dann hat Zutaten auch 300 Zeilen mit Salz?
Also wenn Du denn selbst Programm und Datenbank erstellst/programmierst solltest Du das Statement auf jeden Fall dynamisch erstellen und keine joins machen, wo nichts gesucht wird.
Ein Statement wie Du es brauchst wenn ein gesuchtes Rezept jeweils alle angegebenen Zutaten auch enthalten soll mit 5 gegebenen Zutaten sieht ungefaehr so aus
(wobei ich bitte das konkret gegen MySQL zu checken, da bin ich nicht fit mit! Also bitte nochmal verifizieren!):
Code:
SELECT DISTINCT Rezepte.ID, Rezepte.Name, Rezepte.Beschreibung FROM Rezepte,
Zutaten AS Zutaten1, Zutaten AS Zutaten2, Zutaten AS Zutaten3,
Zutaten AS Zutaten4, Zutaten AS Zutaten5
WHERE Rezepte.ID = Zutaten1.Rezept_ID AND Zutaten1.Name LIKE '%$zutat1%'
AND Rezepte.ID = Zutaten2.Rezept_ID AND Zutaten2.Name LIKE '%$zutat2%'
AND Rezepte.ID = Zutaten3.Rezept_ID AND Zutaten3.Name LIKE '%$zutat3%'
AND Rezepte.ID = Zutaten4.Rezept_ID AND Zutaten4.Name LIKE '%$zutat4%'
AND Rezepte.ID = Zutaten5.Rezept_ID AND Zutaten5.Name LIKE '%$zutat5%'Ein Verbesserungsvorschlag fuer Performance und Bedienung:
Die Stammdaten der Zutaten sollten tatsaechlich (ID, Name evtl Beschreibung, das waere ein schoenes Feature fuer Texte wie "Lieber zwei kleine als einen grossen Kohlrabi nehmen, der ist holziger")
separat in einer Tabelle stehen;
Das Datum "Menge" gehoert eigentlich zu einer "Rezept-Position" die technisch eine Verbindung zwischen einem Rezept und einer Zutat abbildet.
Das kaeme in eine dritte Tabelle, in der dann die ID des Rezeptes, die ID der Zutat und die Menge in je einer Zeile stehen, Index ueber die zwei ID Spalten.
Die Rezept-ID kann dann neben der Menge auch in den Zutaten entfallen.
Im Endeffekt soll dann in der Zutaten Tabelle jede Zutat nur einmal stehen.
Im Programm / Frontend koennen dann beim Erfassen eines Rezeptes die Zutaten nacheinander aus einer Liste aller bekannten Zutaten ausgewaehlt oder bei der Gelegenheit eine neue Zutat angelegt werden.
Zum Suchen von Rezepten nach Zutaten die zu suchenden Zutaten aus einer Liste aller bekannter Zutaten auswaehlen lassen.
Ggfs kann man auch noch die Moeglichkeit einraeumen entweder die Zutaten auszuwaehlen oder einen freien Text als Name einzugeben,
dort wo der Text steht suchst Du halt zuerst die Zutaten aus den Stammdaten und kannst dann mit den ID's die Rezepte suchen, bei denen aus der Liste solltest Du die ID's schon haben.
Bei der veraenderten Datenstruktur hast Du insgesamt weniger Daten, solche Queries wie oben verarbeiten dann auch weniger Daten (wegen weniger Zeilen in Zutaten und dort folglich weniger Text in der Spalte Name)
und der Join der dann Rezepte und Zutaten zusammenbring kommt in Gaenze mit ID's aus => sehr schnell bei Vergleichsoperationen.
Ich wollte mich kurz fassen ;>
Ciao,
Mercy.
vielen Dank für deine umfassende Hilfe.
Am Anfang hatte ich eiegntlich auch 3 Tabelle geplant, so wie man das in jedem DB-Buch liest, einmal Zutaten, einmal Rezepte und einmal die zusammenführung der beiden. Allerdings hat mein Auftraggeber das nicht für sinnvoll empfunden, den genauen Wortlaut weiss ich jetzt nichtmehr.
Ich werd mal versuchen ob ich es mit den Dynamischen Statements schaffe, und im schlimmsten Fall muss dann wohl doch alles umgebaut werden.
Mit deiner Query brauch ich leider länger wie wenn ich es mit dem "IN" mache.
Dann bleibt mir wohl nix anderes übrig als doch 3Tabellen zu machen.
Am Anfang hatte ich eiegntlich auch 3 Tabelle geplant, so wie man das in jedem DB-Buch liest, einmal Zutaten, einmal Rezepte und einmal die zusammenführung der beiden. Allerdings hat mein Auftraggeber das nicht für sinnvoll empfunden, den genauen Wortlaut weiss ich jetzt nichtmehr.
Ich werd mal versuchen ob ich es mit den Dynamischen Statements schaffe, und im schlimmsten Fall muss dann wohl doch alles umgebaut werden.
Mit deiner Query brauch ich leider länger wie wenn ich es mit dem "IN" mache.
Dann bleibt mir wohl nix anderes übrig als doch 3Tabellen zu machen.
Last edited by a moderator:
M
Mercenary
Guest
Hallo.
Wenn er nachher den Sourcecode bekommt und jetzt schon so tief in die Technik greifende Vorgaben macht, dann soll er's doch im Zweifelsfall auch so haben.
Ich bin da sehr Anwenderorientiert, Technik ist ja nicht zum Selbstzweck da, weiss der Teufel, was der Kunde damit vor hat.
Wenn er nur von den Rezepten auf die Zutaten zugreifen wuerde waere das ja auch nicht unbedingt ein Problem aber wenn er so eine Verarbeitung erst auf den Zutaten macht um an die Rezepte zu kommen naja.
So ein Umbau wird auch ganz schoen Aufwand verzehren, zu Deinen Lasten oder zu den Lasten des Auftraggebers.
Und wenn der Auftraggeber das explizit so gefordert hat und auch neuerlich wieder fordert, dann brauchst Du Dir auch nichts vorwerfen zu lassen wenn du es auch so machst.
Mir ist nochwas eingefallen zu den Laufzeiten auf Deinem vServer; Es waere sicher zuverlaessiger, wenn Du solche Messungen bei Dir lokal vornimmst, nicht dass die langen Antwortzeiten mit Deinem Nachbarn auf demselben host zusammen haengen, der da womoeglich gerade versucht, die lags auf seinem 64 slot CS:S Server weg zu optimieren. ,>
Ausschliessen kann man das jedenfalls nicht.
Ciao,
Mercy.
Na dann hast Du jetzt neben dem "Autoritaetsbeweis" aus der Literatur auch eine Erklaerung, warum das gerade in diesem Anwendungsfall nachteilig ist, das kannst Du ja Deinem Auftraggeber so mal nahelegen und ihn dann nochmal entscheiden lassen, was er will.
Wenn er nachher den Sourcecode bekommt und jetzt schon so tief in die Technik greifende Vorgaben macht, dann soll er's doch im Zweifelsfall auch so haben.
Ich bin da sehr Anwenderorientiert, Technik ist ja nicht zum Selbstzweck da, weiss der Teufel, was der Kunde damit vor hat.
Wenn er nur von den Rezepten auf die Zutaten zugreifen wuerde waere das ja auch nicht unbedingt ein Problem aber wenn er so eine Verarbeitung erst auf den Zutaten macht um an die Rezepte zu kommen naja.
Lass das mal Deinen Auftraggeber nochmal entscheiden, schliesslich ist es sein Ding (und nicht Deins), nachher auch fuer die noetige Hardwareausstattung zu sorgen, den DB-Server gut zu konfigurieren und regelmaessige Wartungsjob auf dem Server einzurichten!
So ein Umbau wird auch ganz schoen Aufwand verzehren, zu Deinen Lasten oder zu den Lasten des Auftraggebers.
Und wenn der Auftraggeber das explizit so gefordert hat und auch neuerlich wieder fordert, dann brauchst Du Dir auch nichts vorwerfen zu lassen wenn du es auch so machst.
Das ueberrascht mich jetzt allerdings, das muss ich mir merken und bei Gelegenheit, wenn ich mal so eine Sandkastenumgebung habe ausprobieren.
Mir ist nochwas eingefallen zu den Laufzeiten auf Deinem vServer; Es waere sicher zuverlaessiger, wenn Du solche Messungen bei Dir lokal vornimmst, nicht dass die langen Antwortzeiten mit Deinem Nachbarn auf demselben host zusammen haengen, der da womoeglich gerade versucht, die lags auf seinem 64 slot CS:S Server weg zu optimieren. ,>
Ausschliessen kann man das jedenfalls nicht.
Ciao,
Mercy.
Huschi
Moderator
Also wenn ich mich noch einmischen dürfte:
ich würde folgenden Weg gehen (untested):
Erklärung:
1. Beachte das LIKE 'blabla%'. Denn das einleitende % braucht man wirklich nicht.
2. Bilde den Select Dynamisch. Also anhand der wirklichen Anzahl der angegebenen Zutaten.
3. Erkennst Du was es bringt?
Du erhälst Alternativen! Die besten Hit's mit evtl. voller Punktzahl ("Rank") als erstes und weitere Alternativen die nur bis zu einer Zutat enthalten.
4. Eine Ergänzung von " HAVING Rank>1" würde festlegen, daß mindestens 2 Zutaten stimmen müssen.
Hoffe geholfen zu haben.
huschi.
ich würde folgenden Weg gehen (untested):
Code:
SELECT Rezepte.ID, Rezepte.Name, Rezepte.Beschreibung, COUNT(Zutaten.ID) AS Rank
FROM Rezepte LEFT JOIN Zutaten ON (Rezepte.ID=Zutaten.Rezepte_ID)
WHERE (Zutaten.Name LIKE '$zutat1%' OR Zutaten.Name LIKE '$zutat2%' OR Zutaten.Name LIKE '$zutat3%' OR Zutaten.Name LIKE '$zutat4%' OR Zutaten.Name LIKE '$zutat5%')
GROUP BY Rezepte.ID
ORDER BY Rank DESC1. Beachte das LIKE 'blabla%'. Denn das einleitende % braucht man wirklich nicht.
2. Bilde den Select Dynamisch. Also anhand der wirklichen Anzahl der angegebenen Zutaten.
3. Erkennst Du was es bringt?
Du erhälst Alternativen! Die besten Hit's mit evtl. voller Punktzahl ("Rank") als erstes und weitere Alternativen die nur bis zu einer Zutat enthalten.
4. Eine Ergänzung von " HAVING Rank>1" würde festlegen, daß mindestens 2 Zutaten stimmen müssen.
Hoffe geholfen zu haben.
huschi.
M
Mercenary
Guest
Hallo.
Wenn ich mir das so ansehe ...
Ergaenzung ...
5. Im optimalen Falle sollte die DB-Engine bei dem Statement da auch nur einmal ueber die 3000 Zutaten "huschen" muessen (statt z.B. 5 mal).! Das sieht sehr gut aus.
5.1. Waere natuerlich noch schoener, wenn letztlich der Textvergleich nur noch auf 300-500 "echte" Zutaten ausgefuehrt wird und dann das Ranking auf die 3000 Rezept-Zutaten greifen wuerde.
@ Huschi
.
Ciao,
Mercy.
Wenn ich mir das so ansehe ...
Code:
SELECT Rezepte.ID, Rezepte.Name, Rezepte.Beschreibung, COUNT(Zutaten.ID) AS Rank
FROM Rezepte LEFT JOIN Zutaten ON (Rezepte.ID=Zutaten.Rezepte_ID)
WHERE (Zutaten.Name LIKE '$zutat1%' OR Zutaten.Name LIKE '$zutat2%' OR Zutaten.Name LIKE '$zutat3%' OR Zutaten.Name LIKE '$zutat4%' OR Zutaten.Name LIKE '$zutat5%')
GROUP BY Rezepte.ID
ORDER BY Rank DESC5. Im optimalen Falle sollte die DB-Engine bei dem Statement da auch nur einmal ueber die 3000 Zutaten "huschen" muessen (statt z.B. 5 mal).! Das sieht sehr gut aus.
5.1. Waere natuerlich noch schoener, wenn letztlich der Textvergleich nur noch auf 300-500 "echte" Zutaten ausgefuehrt wird und dann das Ranking auf die 3000 Rezept-Zutaten greifen wuerde.
@ Huschi
Haettest Du das nicht eher posten koennen
.Ciao,
Mercy.
viel Dank für eure Hilfe.
Hab jetzt ein Super funktionierende Abfrage.
Sollte es jemad interessieren, hier der Code:
Hab jetzt ein Super funktionierende Abfrage.
Sollte es jemad interessieren, hier der Code:
PHP:
$param = array();
$anzahl = 0;
if($zutat1 != "")
{
$param[$anzahl] = $zutat1;
$anzahl++;
}
if($zutat2 != "")
{
$param[$anzahl] = $zutat2;
$anzahl++;
}
if($zutat3 != "")
{
$param[$anzahl] = $zutat3;
$anzahl++;
}
if($zutat4 != "")
{
$param[$anzahl] = $zutat4;
$anzahl++;
}
if($zutat5 != "")
{
$param[$anzahl] = $zutat5;
$anzahl++;
}
$max = $anzahl - 2;
$schleife = $anzahl;
//Abfrage zusammensetzen
if($anzahl = 0)
$abfrage ="SELECT DISTINCT Rezepte.ID, Rezepte.Name, Rezepte.Beschreibung FROM Rezepte";
else
$abfrage = "SELECT Rezepte.ID, Rezepte.Name, Rezepte.Beschreibung, COUNT(Zutaten.ID) AS Rank ".
"FROM Rezepte LEFT JOIN Zutaten ON (Rezepte.ID=Zutaten.Rezept_ID) ".
"WHERE (".
"Zutaten.Name LIKE '$param[0]%' ";
$i = 1;
while($schleife > $i)
{
$abfrage .= "OR Zutaten.Name LIKE '$param[$i]%' ";
$i++;
}
$abfrage .= ") ".
"GROUP BY Rezepte.ID ".
"ORDER BY Rank DESC ";
$ergebnis = mysql_query($abfrage) or die ("Fehler");Huschi
Moderator
Willst Du noch ein paar Anstösse zum besseren PHP?
Z.B. reicht folgende Zeile aus
Danach kannst Du die Anzahl über count($param) ermitteln.
Desweiteren wird glaub ich ein ungültiger SQL-Query erstellt, wenn keine Zutaten angegeben werden. Du solltest die ganze Erstellung der Zutatenliste in den else-Block mit aufnehmen. (Das "DISTINCT" im IF-Block ist übrigens überflüssig.)
Dein Schleifen-Konstrukt für die Zutatenliste ist auch etwas schwierig zu verstehen. Sowas sähe doch viel besser aus, oder?
Und noch ein kleiner Performance-Tipp: Solange keine Variablen im String abzuarbeiten sind, sollte man einfache Hochkommata nutzen. (Z.B. 'GROUP BY Rezepte.ID ')
Grund: PHP parst jeden String in doppelten Hochkomma nach Variablen. Strings in einfache Hochkomma werden nicht geparst.
huschi.
Z.B. reicht folgende Zeile aus
Code:
if($zutat1 != "") $param[] = $zutat1;Desweiteren wird glaub ich ein ungültiger SQL-Query erstellt, wenn keine Zutaten angegeben werden. Du solltest die ganze Erstellung der Zutatenliste in den else-Block mit aufnehmen. (Das "DISTINCT" im IF-Block ist übrigens überflüssig.)
Dein Schleifen-Konstrukt für die Zutatenliste ist auch etwas schwierig zu verstehen. Sowas sähe doch viel besser aus, oder?
Code:
for ($i = 1; $i <= count($param); $i++)
$abfrage .= "OR Zutaten.Name LIKE '$param[$i]%' ";Und noch ein kleiner Performance-Tipp: Solange keine Variablen im String abzuarbeiten sind, sollte man einfache Hochkommata nutzen. (Z.B. 'GROUP BY Rezepte.ID ')
Grund: PHP parst jeden String in doppelten Hochkomma nach Variablen. Strings in einfache Hochkomma werden nicht geparst.
huschi.