MySQL Querys optimieren

[styla]

Hi,
Ich hab eine Datenbank in der über 5 Millionen Daten sind.
Wenn ich jetzt eine Abfrage starte:
 

SELECT beschreibung FROM tab_daten WHERE beschreibung = \'%\".$suchwort.\"%\' ORDER by beschreibung LIMIT 100\";

 
 
Jetzt dauert das ganze ca 6-10 Sekunden bis er alle Datensätze durchsucht hat und mir aus den Ergebnissen 100 Treffer anzeigt.
Auf anderen Seiten dauert die Suche jedoch gerade mal 1 Sekunde oder noch schneller.
Das ich später noch Volltextsuche implementiere weiß ich nicht ob das noch länger dauert.
Deswegen suche ich eine lösung, bzw. kann mir jemand Tipps geben wie ich MySQL dies bezüglich bei der Suche nach Datensätzen beschleunigen kann.
Infos zum System:
GenuineIntel, Intel® Celeron® CPU 2.40GHz
SuSE 9.3 OS Linux 2.6.11.4-21.11-default
httpd 2.0.53-9
mailman 2.1.5-16
mod_perl 2.0.0-4
mod_python 3.1.3-42
mysql 4.1.10a-3
perl-Apache-ASP 2.57-suse9.3.build75050824.12
php 4.3.10-14  
mysql wird bald auf 5 geupdated !!!
Ich bedanke mich schonmal für jeglichen Rat und Hilfestellungen.
mfg
STyla

[Powie]

also als erstes ist Suspekt das du da mit =\'%wert%\' suchst... das als erste macht für mich erstmal keinen Sinn.... Suchst du nicht mit Like ?
Optimieren kannst du mit 2 Dingen:
1. Das entsprechende Feld indizieren, da ist es aber von Interesse was das für ein Feld ist . Normal indizieren wenn ein varchar, wenn es ein längeres Text oder Memofeld ist, dann mit einem Volltextindex belegen und mit Match durchsuchen.
2. -> Das kann aber nur der Provider: mySQL Query Cache benutzen!
http://www.powie.de/cms/artikel/artikel.php?id=35&page=7\" rel=\"external nofollow\">http://www.powie.de/cms/artikel/artikel.php?id=35&page=7

[styla]

oha, also das mit like kannte ich noch nicht. Ich dachte SELECT blabla LIKE \'%blaa%\' wäre das gleiche wie \"variable = \"%blaa%\"\"
Ok, als zu Punkt 1:
Gut ich hätte 2 Felder welche benötigt werden zu indizieren. Eins mit normal und eins mit Volltext.
Punkt 2: ja kann ich machen, ich bin der Provider!
zusätzlich noch zum optimieren:
Welcher Type ist denn am besten bei so einer datenmenge und ca. 100.000 Abfragen pro Tag ?
MyIsam, HEAP oder noch ein andere, mysql 5 hat ja neue eingeführt.
Dann ich bin mir nicht sicher, gibt es noch einen anderen Befehl um daten einzufügen außer INSERT ? Z.b. sollte man ja beim löschen auch nicht DELETE nehmen sondern TRUNCATE.
Zuletzt: Ist eine presistente Connection gut, also sollte man diese auf jedenfall wählen, oder normales connect ? Und sollte man auch immer disconnecten ? Das habe ich bis jetzt immer weggelassen.
Danke schonmal.
PS: Wenn du Zeit hast würde ich mich freuen wenn du eventuell eine Lösung hast für mein XML Problem im PHP-Allgemein Forum.

[Powie]

Also ..... indiziere zuerst einmal die beiden Felder. Dann führe beim Textfeld eine Suche mit Like durch, und beim Memofeld mach es mit der Volltextsuche mit Match. Musst du einfach mal testen was wie am besten funktioniert.
Tabellentyp: MyISAM
Ob du Delete oder Truncate benutzt ist wurscht, das hat auf die Suche kaum Auswirkungen, die Suche geht dann über den Index...
Verbindung: Scheiden sich die Geister.... Ich habe festgestellt das es  besser funktioniert wenn bei einem viel besuchten System die Verbindungen immer neu geöffnet/geschlossen werden, das müllt den Speicher nicht zu. Zudem hatte ich bei persistenten Verbindungen öfters Probleme mit den Connnect und der Problematik das die max.Connections erreicht waren.. und dann war feierabend  :gaga:

[styla]

ahh wunderbar!
Zu MyISAM:
Da weiß ich nicht genau, ich hatte durch zufall eine Seite gefunden(hab sie leider nicht mehr hmmpf ich guck nachher nochmal) dort stand, wenn ichs richtig verstanden habe, dass MyISAM der schlechteste Datentyp wäre.
Naja aber ich teste heute Abend mal wie das mit der such klappt bei volltext und like und jeweils mit den indexen.
Ähmm ich glaube Truncate hat ansich nichts mit der Suche zu tun /uploads/emoticons/icon_e_wink.gif.c059000ae48ff64afa53be0962c021f2.gif\" alt=\":wink:\" />
Dachte eher, da täglich ca. 200-1000 Einträge hinzukommen/geändert werden ist Truncate von vorteil gegenüber DELETE.
Naja danke erstmal.
Jetzt müsste ich nur noch das mit dem xml Zeugs hinkriegen...

LIKE und %% sind immer langsam, nur eine Volltextsuche ist wirklich performant.

[styla]

verstehe ich das richtig das die Volltextsuche im Prinzip das gleiche ist wie wenn ich bei Google etwas suche ?

[Powie]

schaus dir einfach mal im pForum an:
Wenn das Feld Volltext indiziert ist sucht man mit:
Where Match FELD Against (\'SUCHTEXT\')

[styla]

hi,
ja schon klar.
Ich meine, ob MySQL die Texte dann ungefähr so durchsucht wie Google ?
Beispiel:
Suche nach \"Kopierprogramme für DVDs\"
ist nirgends in der Datenbank vorhanden, aber: \"Kopierprogramm Nero Burning Rom\" ist z.B. in der Datenbank.
Gibt der das dann auch aus ? Also das der sozusagen den Satzbau kontrolliert und die am ähnlichsten Datensätze ausgibt ?

Ja, genau so ist das. Wenn du im BOOLEAN Mode suchst, kannst du auch die Opteratoren, wie +, - und Anführungsstriche verwenden.
Kannst auch einfach mal EXPLAIN verwenden, dann sagt dir MySQL, welche Felder indexiert werden sollten.
 - Editiert von electr0n am 23.07.2006, 16:43 -

[styla]

hi,
jo danke schonmal.
Morgen kommt ein MySQL Mitarbeiter zu uns, der schaut sich das ganze an und sagt genau wo man was optimieren kann und sollte.
Bin schon gespannt.
Ich bin ebenfalls dabei jetzt endlich meine kleinen Funktionen zu sammeln. Die werde ich dann auch beim nächsten pageupdate in den Downloads aufnehmen. Ich nenn dann hier mal den link.
Zu Google:
Sehe ich das richtig das google auch booleans verwendet und beim Suchwort die leerstellen durch den Operator + ersetzt ?
mfg
STyla

[derboehm]

Hey,
zum Datenbank typ wollte ich nochmal was sagen. ich nutze InnoDB bei ca. 10000 user und mit 500 -600 in den stoßzeiten online ist das der beste datenbank typ... dieser ist deutlich schneller als MyISAM beim selecten nur beim schreiben (insert) ist er ein wenig langsamer!

[styla]

hi,
puhh endlich von der arbeit zurück. Sone scheisse. Klimaanlage ausgefallen. :=(  :R:
Also wie gesagt ein MySQL Mitarbeiter war heute bei uns und hat entscheidendes bei uns optimiert. Ich werde demnächst mal ein paar Tipps hier posten was man optimieren kann und sollte.
@derBoehm
eventuell auch für dich wichtig, weshalb wir nicht innoDB verwenden können ist, dass die Volltextsuche von MySQL NUR mit dem Tabellentyp MyISAM funktioniert. Zusätzlich wurde bei uns der query cache und viele weitere Sachen optimiert. Ich habs leider noch nicht gesehen wie schnell jetzt eine Abfrage läuft (bin in einer anderen Position wo bissel viel zu tun ist) habe aber gehört die soll jetzt wie schmitz katze rennen. Naja morgen werde ich den genuss haben das ganze zu testen.Besonders gut finde ich auch was er uns da noch eingestellt hat für genauere Suchabragen das \"automatic Relevanzfeedback\" was bedeutet wenn jemand nach \"Datenbanken\" sucht (kann ja vieles sein) meint man aber normal halt eine Datenbank wie MySQL oder DB2. Das wird dabei dann berücksichtigt und automatisch gefunden.
Haben jetzt auch MySQL 5 und nicht mehr die 4er Version.  :H:
 - Editiert von STyla am 24.07.2006, 23:41 -