Große Datenbank organisieren

[RFZ]

Hi,
ich hab eine fette Datenbank (MySQL) mit knapp 61.000 Zeilen, was ziemlich genau 100MB entspricht.
Im wesentlichen isses jeweils ein Titel, Text, Datum, Rubrik und ein Link.
Titel und Text sollen durchsucht werden können, Datum auch.
Über Titel und Text hab ich einen Volltextindex gelegt (macht nochmal satte 103MB!).
Datum ist nicht als Datumsfeld, sondern VARCHAR angegeben. Hätte ich durch ein Datenfeld gewisse Vorteile was die Performance betrifft?

Ich hab das Problem dass Suchvorgänge für ein und das selbe Wort manchmal weniger als 50ms brauchen, und manchmal über 60Sekunden, kann sich das jemand erklären? Was wäre bei den Verhältnissen normal?

Ich hab auch testweise ein Tabelle nur mit 61.000 Datums-Werten angelegt und diese mit "WHERE xy LIKE ''" durchsucht. Das war tausend mal schneller als wenn ich das selbe mit der großen Tabelle gemacht hab, liegt das daran dass in der Tabelle _nur_ diese eine Spalte war? Würde es deshalb Sinn machen Text und Titel von den anderen Spalten in eine eigene Tabelle abzutrennen?

Naja, alles in Allem suche ich hier nach wertvollen Tipps wie ich eine so große DB möglichst schnell durchsuchen lassen kann. Was für Indizes brauch ich? Welche Werte? (MySQL Befehl)
Worüber sollte ich die Indizes legen? Die Artikel sind auch in Rubriken unterteilt (davon gibt es ca. 100) welche als Integer gespeichert sind. Wie soll ich diese 'verwalten', wenn ich mal die Suche nur auf ein paar Rubriken beschränken will?

Ganz nebenbei, wie schafft Google das?? ;)

Ich hoffe ihr könnt mir helfen...

cu RFZ

[Bio]

Sup!

Datum ist nicht als Datumsfeld, sondern VARCHAR angegeben. Hätte ich durch ein Datenfeld gewisse Vorteile was die Performance betrifft?

Ja, das hätte sicher einige Vorteile, Du Held.

Ganz nebenbei, wie schafft Google das?? ;)

Eine andere DB als mysql und KIWI, denke ich ;-)

Gruesse,

Bio

[RFZ]

Sup!

Tach

Ja, das hätte sicher einige Vorteile, Du Held.

Die wären? Ich muss nur nach gewissen Tagen suchen, keine Zeiträme, da reicht VARCHAR aus. Und die Suchzeit ist extrem gering, 5-100ms in meiner Testtabelle mit 61.000 Zeilen.

Primär gings mir um die Textsuche, die sogar Minuten dauert...

Gruesse,
Bio

cu RFZ

[Bio]

Sup!

Ja, das hätte sicher einige Vorteile, Du Held.
Die wären? Ich muss nur nach gewissen Tagen suchen, keine Zeiträme, da reicht VARCHAR aus. Und die Suchzeit ist extrem gering, 5-100ms in meiner Testtabelle mit 61.000 Zeilen.

Um Strings zu vergleichen ist einfach viel mehr Aufwand nötig als man braucht, um Zahlen zu vergleichen, und ein Datums-Feld in einer Datenbank ist meist einfach nur eine Zahl, Long oder Long Long.

Primär gings mir um die Textsuche, die sogar Minuten dauert...

Sag' das doch.
Wenn die zu lahm ist, dann wird das wohl an mysql liegen.

Gruesse,

Bio

[RFZ]

Moin,

Primär gings mir um die Textsuche, die sogar Minuten dauert...
Sag' das doch.
Wenn die zu lahm ist, dann wird das wohl an mysql liegen.

Soll heissen ich soll das so akzeptieren? Ne das kann ich nicht :)

Bio

cu RFZ

[Michael Schröpl]

Hi Bio,

Sag' das doch.
Wenn die zu lahm ist, dann wird das wohl an mysql liegen.

hm ... das war deutlich unter Deinem Niveau.

Viele Grüße
      Michael

[Bio]

Sup!

Sag' das doch.
Wenn die zu lahm ist, dann wird das wohl an mysql liegen.

hm ... das war deutlich unter Deinem Niveau.

Mag sein, aber ich hatte keine Lust, eine Diskussion um Arten von Indizes, Bäume (binär, avl, b[+|*], r[+|*], k-d, u-b, gridfiles), Plattenzugriffe etc. zu entfachen, sowie über die Indizierung von Multimediadaten, insbesondere Texte. Abhängig von der Art des Textes und der Zahl der Worte, die vorkommen könnten, wären natürlich verschiedene Arten der Erfassung des Textes möglich. Auch Erfassung von Stammformen oder der Semantik wären denkbar... und man könnte probabilistische oder Vektorraum-Retrieval-Modelle anwenden, oder einfache boolsche...

Da der gute Mann aber mysql und dessen Möglichkeiten nutzen will, die ich nicht kenne, konnte ich dazu leider nicht viel sagen.

Gruesse,

Bio

[Michael Schröpl]

Hi Bio,

Wenn die zu lahm ist, dann wird das wohl an mysql liegen.
hm ... das war deutlich unter Deinem Niveau.

Mag sein, aber ich hatte keine Lust, eine Diskussion um Arten von Indizes, Bäume (binär, avl, b[+|*], r[+|*], k-d, u-b, gridfiles), Plattenzugriffe etc. zu entfachen,

ich schon - denn genau darum geht es im vorliegenden Fall ... und nicht um irgendwelche Hersteller.
Mit Falschaussagen bringst Du die Diskussion nicht voran.

Da der gute Mann aber mysql und dessen Möglichkeiten nutzen will, die ich nicht kenne,
konnte ich dazu leider nicht viel sagen.

Du hättest weniger sagen können - und IMHO sollen.

Viele Grüße
      Michael

[Michael Schröpl]

Hi RFZ,

Titel und Text sollen durchsucht werden können, Datum auch.

definiere "durchsuchen".

Hätte ich durch ein Datenfeld gewisse Vorteile was die Performance betrifft?

Definiere "durchsuchen".

Ich hab das Problem dass Suchvorgänge für ein und das selbe Wort manchmal weniger als 50ms brauchen, und manchmal über 60Sekunden, kann sich das jemand erklären?

EXPLAIN auf Deine Query.

Was wäre bei den Verhältnissen normal?

Für eine Antwort auf diese Frage liegen nicht genügend Informationen vor.

Würde es deshalb Sinn machen Text und Titel von den anderen Spalten in eine eigene Tabelle abzutrennen?

Nein. Ich glaube die beschriebene Abhängigkeit nicht, und sie wäre es auch nicht wert, Dein Datenmodell dafür zu ruinieren.

Naja, alles in Allem suche ich hier nach wertvollen Tipps wie ich eine so große DB möglichst schnell durchsuchen lassen kann.

Ich finde Deine Datenbank winzig klein. Meine ist um gut eine Zehnerpotenz größer, aber ich habe Such-Zeiten meistens unterhalb einer Sekunde, selten im kleinen einstelligen Sekundenbereich.
(Und ich verwende auch mySQL-Fulltext, meine Text-Felder sind im Schnitt etwa 5 KB groß.)

Was für Indizes brauch ich?

Diejenigen, die Deine Statements beschleunigen.

Worüber sollte ich die Indizes legen?

Typischerweise solche über Spalten Deiner WHERE- und ORDER-BY-Klauseln.
EXPLAIN sagt Dir, was genau die engine generiert hat.

Die Artikel sind auch in Rubriken unterteilt (davon gibt es ca. 100) welche als Integer gespeichert sind. Wie soll ich diese 'verwalten', wenn ich mal die Suche nur auf ein paar Rubriken beschränken will?

integer ist nicht verkehrt.

Ein Index darüber wird übrigens nicht viel bringen, dafür sind 100 Rubriken einfach zu wenig; FULLTEXT projeziert deutlich besser, und mehr als einen Zugriffspfad gleichzeitig kannst Du eh nicht nutzen.
Hättest Du die Anforderung, alle Einträge einer Rubrik zu lesen, ohne diese weiter zu filtern, dann wäre ein Index allerdings sinnvoll.

Ganz nebenbei, wie schafft Google das?? ;)

Indem sie ihre gesamte Architektur auf die Aufgabenstellung optimiert haben.

Viele Grüße
      Michael

[RFZ]

Moin,

Ich hab das Problem dass Suchvorgänge für ein und das selbe Wort manchmal weniger als 50ms brauchen, und manchmal über 60Sekunden, kann sich das jemand erklären?

EXPLAIN auf Deine Query.

OK, ich geb einfach mal ein Beispiel, da ich mit dem EXPLAIN nich viel anfangen kann...

Query: EXPLAIN SELECT * FROM stories WHERE MATCH (inhalt, titel) AGAINST ('weekend') AND MATCH (inhalt, titel) AGAINST ('party') LIMIT 0, 10

Ausgabe:
Table: stories; type: fulltext; possible_keys: titel; key: titel; key_len: 0; ref:; rows: 1; Extra: where used, Using filesort;

Aufbau der Tabelle

CREATE TABLE stories (
  titel text NOT NULL,
  inhalt text NOT NULL,
  (...)
  FULLTEXT KEY titel (titel,inhalt)
) TYPE=MyISAM;

Zeilengröse: ~ 3,4kb
Zeilenzahl: 61.069

So, und dieser Suchvorgang hat nun 15 Sekunden gedauert!!

Ich finde Deine Datenbank winzig klein. Meine ist um gut eine Zehnerpotenz größer, aber ich habe Such-Zeiten meistens unterhalb einer Sekunde, selten im kleinen einstelligen Sekundenbereich.
(Und ich verwende auch mySQL-Fulltext, meine Text-Felder sind im Schnitt etwa 5 KB groß.)

Was für Indizes brauch ich?
Diejenigen, die Deine Statements beschleunigen.

Sollte ich dazu was best. wissen?

Viele Grüße
      Michael

Dange :)
  RFZ

[Michael Schröpl]

Hi RFZ,

Query: EXPLAIN SELECT * FROM stories WHERE MATCH (inhalt, titel) AGAINST ('weekend') AND MATCH (inhalt, titel) AGAINST ('party') LIMIT 0, 10
So, und dieser Suchvorgang hat nun 15 Sekunden gedauert!!

ein AND über zwei FULLTEXT-Zugriffe kann natürlich einen impliziten JOIN über beliebig viele Zeilen produzieren.
Möglicherweise bekommst Du am Ende nur 10 Treffer, aber vorher hattest Du 1000 Treffer für "weekend" und 4000 für "party", und die mußten miteinander ausmultipliziert werden, um die Schnittmenge zu berechnen. Ein AND über schlechte Suchbegriffe kann sehr teuer sein - und weit über 90% der Verarbeitungszeit findet ggf. außerhalb der Einflußnahme Deines FULLTEXT-Index statt.

Mach mal die beiden Einzelsuchen (ohne Trefferausgabe) und miß deren Dauer - dann weißt Du, wie teuer das AND war.

Was für Indizes brauch ich?
Diejenigen, die Deine Statements beschleunigen.
Sollte ich dazu was best. wissen?

nach Deinem obigen Beispiel machst Du schon alles richtig - Du bist nur genau in die Schwachstelle Deiner Architektur gerannt.

Ich habe etwas sehr ähnliches wie Du hier produktiv laufen. Zu Vermeidung Deines Problems habe ich eine blacklist über "schlechte" Suchbegriffe; wenn auf dieser beispielsweise "party" stehen würde, dann würde meine Suchmaschine nur über "weekend" suchen und dann alle Treffer explizit nach "party" scannen (in einem zweiten Durchgang). Das kann eventuell deutlich schneller sein, wenn zwischen beiden Suchbegriffen mindestens eine Zehnerpotenz an Treffern liegt (der Trick liegt also darin, sich ein Programm zu schreiben, was eine "vernünftige" blacklist berechnet).
Aber ein System, das _nur_ mit schlechten Suchbegriffen arbeitet, kriegst Du mit FULLTEXT und AND nicht wirklich performant hin.

Viele Grüße
      Michael

[RFZ]

Hoi,

ein AND über zwei FULLTEXT-Zugriffe kann natürlich einen impliziten JOIN über beliebig viele Zeilen produzieren.
Möglicherweise bekommst Du am Ende nur 10 Treffer, aber vorher hattest Du 1000 Treffer für "weekend" und 4000 für "party", und die mußten miteinander ausmultipliziert werden, um die Schnittmenge zu berechnen. Ein AND über schlechte Suchbegriffe kann sehr teuer sein - und weit über 90% der Verarbeitungszeit findet ggf. außerhalb der Einflußnahme Deines FULLTEXT-Index statt.

»»

Mach mal die beiden Einzelsuchen (ohne Trefferausgabe) und miß deren Dauer - dann weißt Du, wie teuer das AND war.

OK, "weekend" 5,996 Sekunden, "party" 17,220 Sekunden "weekend party" 13,555 Sekunden. Was soll mir das nun wieder sagen? Ich glaub eher ich sollte meinen Hoster dafür verantwortlich machen, ich glaub die Server sind einfach zu sehr überlastet...
Ich glaub das AND macht dabei nie viel unterschied, sonst wär mir das auch mal aufgefallen. Ob jetzt ein oder mehrere Suchbegriffe, es bleibt langsam...

Ich habe etwas sehr ähnliches wie Du hier produktiv laufen. Zu Vermeidung Deines Problems habe ich eine blacklist über "schlechte" Suchbegriffe; wenn auf dieser beispielsweise "party" stehen würde, dann würde meine Suchmaschine nur über "weekend" suchen und dann alle Treffer explizit nach "party" scannen (in einem zweiten Durchgang). Das kann eventuell deutlich schneller sein, wenn zwischen beiden Suchbegriffen mindestens eine Zehnerpotenz an Treffern liegt (der Trick liegt also darin, sich ein Programm zu schreiben, was eine "vernünftige" blacklist berechnet).
Aber ein System, das _nur_ mit schlechten Suchbegriffen arbeitet, kriegst Du mit FULLTEXT und AND nicht wirklich performant hin.

Naja, mal schaun ob ich für sowas die Zeit finde :)
Aber danke für die Info, is ne gute Idee!

Viele Grüße
      Michael

cu RFZ

[Michael Schröpl]

Hi RFZ,

OK, "weekend" 5,996 Sekunden, "party" 17,220 Sekunden "weekend party" 13,555 Sekunden. Was soll mir das nun wieder sagen?

eieiei. Sind das Realzeitmessungen oder mySQL-interne Zeiten?

Falls letzteres, dann hast Du entweder noch viel schlechtere Suchbegriffe verwendet, als ich befürchtet hatte (wieviele waren es jeweils?), oder Dein Server streut ungeheuer.

Bedenke aber, daß ich "ohne Ausgabe der Treffer" mit Bedacht gesagt hatte: Probier mal, nur die Anzahl der Treffer ("SELECT count(*) ...") zu zählen - das müßte dann deutlich schneller gehen, wenn es an der Datenauslieferung lag.

Viele Grüße
      Michael

[RFZ]

Hi,

Falls letzteres, dann hast Du entweder noch viel schlechtere Suchbegriffe verwendet, als ich befürchtet hatte (wieviele waren es jeweils?), oder Dein Server streut ungeheuer.

Nun, es waren genau diese beiden Suchbegriffe :)

Bedenke aber, daß ich "ohne Ausgabe der Treffer" mit Bedacht gesagt hatte: Probier mal, nur die Anzahl der Treffer ("SELECT count(*) ...") zu zählen - das müßte dann deutlich schneller gehen, wenn es an der Datenauslieferung lag.

Ich hab mit count(*) gearbeitet, die Zeit ist die Laufzeit des PHP-Scripts, welches aber ausser der Anfrage nichts weiteres macht.
Die Zeiten schwanken stark, sind aber immer über 5 Sekunden, oft über 10.

Ich werd einfach mal gegen 6 Uhr morgens testen, dann sollte es, falls es an der Serverauslastung liegt, schneller gehn...

Viele Grüße
      Michael

cu RFZ