MySQL Scipt Laufzeit

[csigg]

Hallo,

ich habe einen vServer zum testen, und würde nun gern wissen wo ich die Dauer eines MySQL Scripts beschränken kann, da im Moment große Abfragen meine ganze DB blockieren.

Ich hab zwei Tabelle, einmal Rezepte mit ca 300 einträge und einmal Zutaten mit ca. 3000 einträgen.
Bei folgendem Statement hängt mein DB-Server nun, wenn ich entweder die variablen nicht belege, also alles anzeigen lasse, oder etwas eingebe was in Zutaten erhalten ist. Nur wenn ich was eingeb, das nirgends drinsteht, dann geht die abfrage ziemlich schnell, hat da jemand ne Ahnung??

$abfrage = "SELECT DISTINCT Rezepte.ID, Rezepte.Name, Rezepte.Beschreibung FROM Rezepte, Zutaten ".
"WHERE Rezepte.ID IN (select Rezept_ID from Zutaten WHERE Name LIKE '%$zutat1%') ".
"AND Rezepte.ID IN (select Rezept_ID from Zutaten WHERE Name LIKE '%$zutat2%') ".
"AND Rezepte.ID IN (select Rezept_ID from Zutaten WHERE Name LIKE '%$zutat3%') ".
"AND Rezepte.ID IN (select Rezept_ID from Zutaten WHERE Name LIKE '%$zutat4%') ".
"AND Rezepte.ID IN (select Rezept_ID from Zutaten WHERE Name LIKE '%$zutat5%')";

 

[ISSchuster]

Ich würde nicht die Laufzeit des Scripts beschränken sondern die Abfrage optimieren.

Der momentane Aufbau ist - wie du ja festgestellt hast - nicht besonders schnell und auch nicht unbedingt sinnvoll... Mach aus den 5 Abfragen der Zutaten mal eine...


Mfg

Stefan Schuster

 

[csigg]

du meinst also sowas:

$abfrage = "SELECT DISTINCT Rezepte.ID, Rezepte.Name, Rezepte.Beschreibung FROM Rezepte, Zutaten ". 
						   "WHERE Rezepte.ID IN (select Rezept_ID from Zutaten WHERE Name LIKE '%$zutat1%') ";

Aber wie kann ich dabei nach 5 Zutaten fragen??

 

[Mario]

Aber wie kann ich dabei nach 5 Zutaten fragen??

WHERE Name LIKE '%$zutat1%' || Name LIKE '%$zutat2%' || ...

 

[marneus]

Das wird AFAIK nicht klappen, da mySQL die or-Kondition nicht als || akzeptiert. Also bitte ausschreiben: OR

 

[Mario]

Das wird AFAIK nicht klappen, da mySQL die or-Kondition nicht als || akzeptiert. Also bitte ausschreiben: OR

Ähm, da muß ich mal widersprechen: Es geht sowohl || als auch OR bzw. && als auch AND .

Ich arbeite ausschliesslich mit || bzw. &&, hatte bisher keine Probleme. Oder sollte ich bisher nur Glück gehabt haben?

Das ganze unter MySQL >= 3.23 . Noch nicht getestet mit MySQL >=5.

 

[csigg]

EIn bisschen besser ist es geworden, aber kann es sein dass ne Abfrage auf die Tabelle 10Sekunden auf nem normalen vServer braucht??

Der Prozeß in phpMyAdmin steht die ganze Zeit auf Sending Data

 

[Mario]

EIn bisschen besser ist es geworden, aber kann es sein dass ne Abfrage auf die Tabelle 10Sekunden auf nem normalen vServer braucht??

- Haben die Tabellen einen Index (Volltext als Beispiel)? Wenn nicht, könnte das ein Problem sein
- Ändert sich der Inhalt der tabelle häufig? Wenn ja, dann hilft ein regelmässig ausgeführtes 'OPTIMIZE TABLE ...' eventuell, die Ausführungszeiten einer Abfrage zu beschleunigen.

 

[csigg]

einen index haben sie nicht.
da werd ich mich mal ein bisschen informieren.

Wie ruf ich das optimize table auf, über die Shell??

 

[Mario]

Wie ruf ich das optimize table auf, über die Shell??

Das ist ein ganz reguläres MySQL Statement: 'OPTIMIZE TABLE table1, table2, table3, ...'

Da aber kein Index besteht, würde ich erwarten, das das OPTIMIZE nicht wirklich was bringt, aber ein Test kann nicht schaden.

 

[csigg]

hab den index jetzt auf meine ID´s gesetzt, allerdings steht bei phpMyAdmin dran dass es nicht gut ist, wenn das die selber felder sind.

Optimize table hab ich mal ausprobiert, aber es dauert immer noch, teilweise minuten.

Hat jemand ne Idee, wie man die Suche noch schneller machen könnte.
Ich sollte sowas für nen Bekannten Programmieren, und das war der Test dazu,
allerdings ist das viel zu langsam für eine Web-Anwendung.

 

[Mercenary]

Hallo.

Indices solltest Du nicht nur auf die ID's legen sondern gerade auch auf die Spalten, nach denen Du Suchoperationen durchfuehrst (hier z.B. Name aus Zutaten).

Was auf einigen Engines auch gut optimiert ist, dass Daten aus Spalten, die in einem Index enthalten sind gar nicht mehr aus der Datengrube geholt werden, weiss nicht ob das bei MySQL so ist aber die Dampfhammermethode um sowas auszunutzen ist dann die haeufig benoetigten Spalten in den Index aufzunehmen-> Das ist gut bei Abfragen aber verringert die Performance wenn sich ein Indexeintrag aendert (was dann auch haeufiger eintritt weil ja mehr Spalten da sind die sich aendern koennten), ausserdem wird dann mehr Speicher fuer den Index gebraucht. Ist also abzuwaegen, das kommt auf die Natur der Daten an.

Zur Abfrage selbst; Der Mengenoperator IN schreibt sich zwar bequem ist aber von der langsameren Sorte wenn es um solche Abfragen geht;
Du joinst doch eh schon beide Tabellen dann kannst Du doch auch gleich die ganze Kondition in einem flachen, einfachen join verbauen also ungefaehr so:

WHERE Rezepte.ID = Zutaten.Rezept_ID AND Zutaten.Name LIKE '%$zutat1%' || Name LIKE '%$zutat2%' || ...

Ciao,
Mercy.

P.S.: Uebersehen ... das mit den "nicht belegten Variablen" solltest Du vielleicht in einer Sandkastenumgebung testen, ich wuesste nicht, was MySQL macht, wenn es ein Konstrukt bzw. mehrere ala "LIKE '%%'" bekommt.

 

[Mordor]

Hätte da auch noch nen Lösungsansatz:

Mußt du die Zutaten unbedingt mit LIKE abfragen? Sind diese nicht eindeutig? Da könntest du dir schon was an Performance sparen.

Als nächstes würde ich nicht immer nach fünf Zutaten suchen, sondern im Script das ganze variabel machen, so dass er wirklich nur nach so vielen Zutaten sucht, wie er auch muss. Das ganze würde ich mit eine IF Satz lösen, der abfragt, welche $zutat* gesetzt ist, und nur diese dann in die Abfrage einfügt.

Ansonsten gibts hier im Forum noch einige Threads zur Optimierung von Mysql!!!

Gruß Mordor

Ps Habs doch gefunden. Schau dir das mal an:

MySQL Performance Tuning mit Tuning-Primer.sh Script

MySQL Performance Tuning mit Tuning-Primer.sh Script Quelle Das Script ist von den MySQL Programmieren. Es analysiert und macht Verbesserungsvorschläge. Anleitung: (Installation) #per SSH mit root einloggen, dann mit cd /usr cd bin #in das /usr/bin Verzeichnis wechseln. Nun kopieren wir...

serversupportforum.de

 

[csigg]

jetzt funktioniert das ganze auf einmal ziemlich schnell, danke.

allerdings hab ich jetzt ein Problem. Meine Queuery Spuckt nichtmehr aus was sie soll. Vielleicht kann mit jemand helfen.

Ich hab die Tabelle Rezepte
CREATE TABLE `Rezepte` (
`id` int(11) NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`Beschreibung` varchar(255) default NULL,
`Anleitung` text NOT NULL,
PRIMARY KEY (`id`)
)

und die Tabelle Zutaten:
CREATE TABLE `Zutaten` (
`id` int(11) NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`Menge` varchar(255) default NULL,
`Rezept_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
)

Nun will ich über 5 EIngabefelder Zutaten einlesen, die dann alle in den angezeigten Rezepten vorkommen müssen.

Das funktioniert mit dem obigen Statement leider nicht.

 

[Mercenary]

Hallo.
Jetzt wo Du's sagst und ich darueber gruebel, was da womoeglich rauskommt aber rauskommen sollte faellt mir ein ...
Kann es sein, dass da eigentlich nur Rezepte gesucht sind, die jeweils alle 0-n Zutaten enthalten?
Dazu muesstest Du wenn's denn mit einem einfachen Select-Clause sein soll, die Zutaten halt 1-n mal gegen die Rezepte joinen (die Spalten dann ueber die Aliase der Tabellen ansprechen), das kann ein wenig unuebersichtlich werden.
Oder man macht einen Cursor auf da kann man dann ganz wirre Konstrukte drin machen, kostet aber auch mehr Zeit (was man auszugleichen versuchen kann, indem man das als stored-proc hinterlegt).
Bisher dreht sich der Code (ausgegangen vom ersten Beispiel) nur darum Rezepte zu finden, die jeweils mindestens eine der aufgefuehrten Zutaten enthalten.

Zu den komischen Ergebnissen nochmal der Hinweis auf das Konstrukt LIKE '%%', weiss der Teufel, was da rauskommt.
Mordor hat da eigentlich die typische Vorgehensweise beschrieben wobei ich nicht weiss, ob Du auch den Daumen auf der Anwendung hast, die das dem Anwender praesentieren soll.
Ich wuerde mir auch eher vorstellen eine Liste mit Zutaten anzubieten und dann eine beliebige Zahl auswaehlen zu lassen; Dann nimmt man auch nur die ID's der ausgewaehlten Zutaten und sucht damit das geht wesentlich schneller als mit Zeichenketten.
Selbstredend wird das Statement auch so dynamisch zusammengestellt, dass keine "Leerlaeufe" stattfinden.

Koenntest Du noch folgende entscheidende Information beibringen:
- Hast Du den Daumen auf der Anwendung die "auf der Datenbank laufen" soll?
- Wie sieht jetzt das verwendete Statement aus?
- Wie sieht jetzt die DDL fuer die Indices aus?
- Wie sieht so ein Resultset dazu aus? Und wie sollte es aussehen?

Ciao,
Mercy.

 

[frank`]

Ein Index bringt bei LIKE nichts,wenn du am Anfang des Suchstrings ne Wildmark hast.

 

[csigg]

also ich hab dabei eigentlich nix zu sagen *g*.

Mein Auftraggeber will halt ein paar rezepte inkl. Zutaten. Und dann soll man mit dereingabe von ein paar Zutaten, die alle im Rezept vorhanden sein müssen, ein passendes Rezept finden.

Die aktuelle abfrage ist:

$abfrage = "SELECT DISTINCT Rezepte.ID, Rezepte.Name, Rezepte.Beschreibung FROM Rezepte, Zutaten ".
"WHERE Rezepte.ID = Zutaten.Rezept_ID AND ".
"(Zutaten.Name LIKE '%$zutat1%' ".
" OR Zutaten.Name LIKE '%$zutat2%' ".
" OR Zutaten.Name LIKE '%$zutat3%' ".
" OR Zutaten.Name LIKE '%$zutat4%' ".
" OR Zutaten.Name LIKE '%$zutat5%') ";

Die gibt mir aber nicht das gewünschte aus.
Da die Zutaten eingegeben werden sollen brauch ich fast die %%, dass ich bei eingabe von Ei auch Eier finde.
mein Resultset:

$ergebnis = mysql_query($abfrage) or die ("Fehler");
echo '<h2>Ergebnisse</h2>';
echo '<table width="400">';
$i = 0;
while($row = mysql_fetch_assoc($ergebnis))
{
$id = $row['ID'];
$name = $row['Name'];
$beschreibung = $row['Beschreibung'];
echo '<tr>';
echo ' <td width="200">'.$name.'</td>';
echo ' <td width="200">'.$beschreibung.' -> <a href="suche_erg2.php?id='.$id.'">mehr</a></td>';
echo '</tr>';
$i++;
}

 

[Mordor]

Dann mach es doch so, dass du nicht immer nach allen fünf Zutaten fragst, sondern an die MYSQL-Anfrage nur die Zutaten weiter gibst, die auch wirklich belegt sind. Dann bekommst du bei einigen Abfragen wesentlich weniger Arbeitsaufwand!

 

[csigg]

dann muss ich aber ungefähr 25 verschieden Abfragen generieren, weil die Felder ja nicht zwingend von oben nach unten belegt werden müssen.

ausserdem bin ich dann wieder bei meinem Problem, wenn wirklich alle 5 Felder eingegeben wurden.

Ich bin grad dabei ein anderes Statement zu entwickeln, allerdings hat bisher noch keines funktioniert ;-)

 

[Mordor]

Hast du eigentlich mal nachgesehen, wieviel Speicher dein Mysql braucht? Bzw. ob der Mysql-Server richtig optimiert ist?