MySQL: Performaceproblem

[Rol]

Hi,

ich habe in einer MySQL DB eine Tabelle mit ca. 1,4 Mio Datensätzen und prinzipiell folgendem Aufbau:

+-----+-----+-----+
| Sp1 | Sp2 | Sp3 |
+-----+-----+-----+
|aaaaa| foo | bar |
+-----+-----+-----+
|aaaaa| foo | bar |
+-----+-----+-----+
|aaaaa| foo | bar |
+-----+-----+-----+
|bbbbb| foo | bar |
+-----+-----+-----+
|bbbbb| foo | bar |
+-----+-----+-----+
|ccccc| foo | bar |
+-----+-----+-----+
|ccccc| foo | bar |
+-----+-----+-----+

Die Tabelle hat noch ca. zehn Spalten mehr von denen aber noch ein paar wegfallen könnten.

Die Abfrage:

SELECT sp2, sp3 FROM tabelle WHERE Sp1='bbbbb';

dauert auf meinem PC (PIII, 650 Mhz) ca. 7 Sekunden, was entschieden zu lange ist.

Bei o.g. Select-Abfrage wird doch jede Zeile von Sp1 geprüft. Diese Spalte ist aber vorsortiert, so dass doch nach dem ersten Auftauchen eines anderen Wertes (nach min. einem erfolgreichem matching) die Suche abgebrochen werden könnte.

Geht so was oder bin ich etwas ganz auf dem Holzweg?

Noch 'ne Frage:

Im Ergebnis der Select-Abfrage muss ich dann noch mehrmals suchen.
Wie kann ich mir dabei eine erneute, erste (siehe oben) Abfrage ersparen?
Wenn ich das Ergebnis in eine anderen Tabelle schreibe um es zwischenzuspeichern, bekomme ich Probleme, wenn mehrere Prozesse zeitüberschneidend da sgleiche tun.

Viele Grüße

Rol

[paschef]

Hallo,

Du könntest einen index auf die Spalte Sp1 legen. Dann sollte die Suche bedeutend schneller gehen.

Und was spricht gegen
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwas'
um noch weiter einzugrenzen ?

Gruß,
paschef

[Rol]

Hallo paschef,

Du könntest einen index auf die Spalte Sp1 legen. Dann sollte die Suche bedeutend schneller gehen.

Zu meiner Schande muss ich gestehe, dass ich das mit dem index noch nicht so richtig kapiert habe. Könntest du mir etwas auf die Sprünge helfen?

Und was spricht gegen
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwas'
um noch weiter einzugrenzen ?

Im Ergebnis soll noch _mehrfach_ gesucht werden, also:
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwas'
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwasanderes'
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'nochwasanderes'

Dann hätte ich die langdauerte Abfrage eben immer wieder.

Gruß,
Rol

[Daniela Koller]

Hi Rol

Im Ergebnis soll noch _mehrfach_ gesucht werden, also:
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwas'
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwasanderes'
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'nochwasanderes'

Dann hätte ich die langdauerte Abfrage eben immer wieder.

Wenn du die auf einmal brauchst dann:

Select Sp1,Sp2,Sp3 From tabelle where Sp3 in ('irgendwas','irgendwas2'...)

Klammersetzung für in bin ich jetzt nicht mehr sicher. Allerdings
geht das nur wenn du feste Werte hast für Sp3 und diese nicht in
der selben Query aus einer anderen Abfrage geholt werden sollen.

Gruss Daniela

[Rol]

Hi Daniela

Im Ergebnis soll noch _mehrfach_ gesucht werden, also:
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwas'
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwasanderes'
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'nochwasanderes'

Dann hätte ich die langdauerte Abfrage eben immer wieder.

Wenn du die auf einmal brauchst dann:

Nein (siehe unten)

Select Sp1,Sp2,Sp3 From tabelle where Sp3 in ('irgendwas','irgendwas2'...)

In dem von der ersten Select-Abfrage zurück gegebenen Zeilen muss _manchmal_ noch bis zu ca. zehn mal gesucht werden. Jeweils mit einem anderem Suchbegriff, eben sooft bis ein Ergebnis gefunden wurde oder sich meine Suchbegriffe erschöpfen.

Guß

Rol

[Klaus Mock]

Hallo,

Zuerst etwas zu Indizes:
Datenbanken 'sortieren' Datensätze grundsätzlich nicht nach irgendwelchen Kriterien in ihrer Datenbasis. Auch mySQL nicht. Wie die Datensätze abgelegt werden, ist Sache des DBMS, Du kannst es nicht wirklich beeinflussen. Du kannst allerdings dem DBMS anweisen, daß es zusätzliche Listen über bestimmte Datenbankfelder anlegt, um bestimmte Ausprägungen (Werte) in dem/den Feld(ern) in der Tabelle schnell zu finden. So etwas nennt man dann Index. Wie Du das bei mySQL machst findest Du unter http://www.mysql.com/doc/C/R/CREATE_INDEX.html.

In dem von der ersten Select-Abfrage zurück gegebenen Zeilen muss _manchmal_ noch bis zu ca. zehn mal gesucht werden. Jeweils mit einem anderem Suchbegriff, eben sooft bis ein Ergebnis gefunden wurde oder sich meine Suchbegriffe erschöpfen.

Versuche ein SQL-Statement zu formulieren, welches auch diese Suche beinhaltet. Alles was Du in der Datenbank, also mit SQL, erledigen kannst, ist in der Regel performanter als das, was Du in einem Programm erledigst. Datenabnken sind eigentlich dafrü gebau, möglichst performant irgendwelche Datensätze zu suchen.
Bedenke, daß Du auch Abfragen formulieren kannst, welche mit mehr als einer Suchbedingung arbeiten:
select sp1,sp2,sp3
  from tabelle
  where sp1 = 'wasauchimmer'
   and (sp2 = 'dies' or sp3 = 'das')

Grüße
  Klaus

[paschef]

Hallo

Du könntest einen index auf die Spalte Sp1 legen. Dann sollte die Suche bedeutend schneller gehen.
Zu meiner Schande muss ich gestehe, dass ich das mit dem index noch nicht so richtig kapiert habe. Könntest du mir etwas auf die Sprünge helfen?

Also ungefähr so:
Wenn Du einer Spalte einen Index zuweist, dann erstellt und verwaltet MySQL eine extra Tabelle, in der bestimmte Informationen zu Deiner Spalte stehen. Z.B. steht da dann drinnen, daß der Wert 'bbbbb' in der Spalte Sp1 in den Zeilen 1234 bis 1238 vorkommt. Die Nummerierung gibt es in Wirklichkeit so nicht, aber das ist hier nicht so wichtig...
Wenn Du nun per SELECT nach einem Wert in Sp1 suchst, dann schaut MySQL zuerst in der index-Tabelle nach, stellt dort fest, daß es nicht alle 1.4 Mio Datensätze zu durchsuchen braucht, und geht ruckzuck zu den betreffenden Zeilen 1234 bis 1238.

Du kannst für jede Spalte einen eigenen Index anlegen. Wenn Du oft Werte in zwei oder mehr Spalten suchst, dann kannst Du auch für mehrere Spalten einen Index anlegen.

Allerdings kostet die Verwaltung auch Resourcen, es macht also keinen Sinn, für jede Kombination von Spalten einen eigenen Index zu bilden. Aber falls Du vorhersehen kannst, daß Du überwiegend Lese-Zugriffe auf bestimmte Spalten hast, dann ist ein Index wunderbar. Auch JOINs laufen beträchtlich schneller.

Wenn Du PHPMyAdmin hernimmst: Da kann man das sehr bequem (unterhalb der Tabellenansicht) anlegen.

Ausführliches auch hier:
<a href="http://www.mysql.com/doc/O/p/Optimising_Database_Structure.html">http://www.mysql.com/doc/O/p/Optimising_Database_Structure.html</a>

Im Ergebnis soll noch _mehrfach_ gesucht werden, also:
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwas'
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'irgendwasanderes'
SELECT Sp1,Sp2,Sp3 FROM tabelle WHERE Sp3 = 'nochwasanderes'

Dann hätte ich die langdauerte Abfrage eben immer wieder.

Mit 'nem Index sollte das kein Problem mehr sein.

Gruß,
paschef

[Michael Schröpl]

Hi Rol,

ich habe in einer MySQL DB eine Tabelle mit ca. 1,4 Mio Datensätzen
und prinzipiell folgendem Aufbau:
SELECT sp2, sp3 FROM tabelle WHERE Sp1='bbbbb';

Wie viele _verschiedene_ Werte enthält die Spalte "sp1"?
(Wie viele Treffer bekommst Du im Schnitt?)

Wenn das wenige sind (1-3stellig), dann fehlt einfach nur der Index.
(Mit dem wird es dann um 3-5 Zehnerpotenzen schneller.)

dauert auf meinem PC (PIII, 650 Mhz) ca. 7 Sekunden, was entschieden
zu lange ist.

Das riecht nach einem full table scan, also keinem verwendeten Index.

Bei o.g. Select-Abfrage wird doch jede Zeile von Sp1 geprüft.

Genau das solltest Du ändern.

Diese Spalte ist aber vorsortiert,

Das bezweifele ich.
Du hast keinen Einfluß auf die interne Anordnung der Datensätze.

so dass doch nach dem ersten Auftauchen eines anderen Wertes
(nach min. einem erfolgreichem matching) die Suche abgebrochen
werden könnte.

Nein, kann sie nicht.
Deine Anfrage verlangt _alle_ Treffer, nicht nur den ersten.

Geht so was oder bin ich etwas ganz auf dem Holzweg?

Wenn Deine Treffer in der Tat unique sein sollten,
dann wäre ein Primärschlüssel über Spalte Sp1 zu empfehlen.

Im Ergebnis der Select-Abfrage muss ich dann noch mehrmals suchen.

Wieso? Du kannst mehrere WHERE-Klauseln kombinieren.

Wie kann ich mir dabei eine erneute, erste (siehe oben) Abfrage
ersparen?

Solltest Du nicht tun.
Mit Index wird die so schnell, daß Du dafür keinen Grund mehr hast.

Wenn ich das Ergebnis in eine anderen Tabelle schreibe um es
zwischenzuspeichern, bekomme ich Probleme, wenn mehrere Prozesse
zeitüberschneidend da sgleiche tun.

Temporäre Tabellen von mySQL sind prozeßlokal, da würdest Du das
Problem nicht haben. Aber das brauchst Du nicht - was Du brauchst,
ist ein guter Index über Deine Such-Spalte(n).
Eventuell ein Index über mehrere Spalten auf einmal - das kommt auf
Deine Suchanforderungen an ... und das wäre wesentlich _schneller_,
als im Ergebnis der ersten Suche wiederum einen full table scan zu
machen.

Viele Grüße
      Michael

[Rol]

Hi Michael,

Wie viele _verschiedene_ Werte enthält die Spalte "sp1"?

ich schätze 10000 - 20000

(Wie viele Treffer bekommst Du im Schnitt?)

10-1000

Wenn das wenige sind (1-3stellig), dann fehlt einfach nur der Index.
(Mit dem wird es dann um 3-5 Zehnerpotenzen schneller.)

Das wäre super!

Diese Spalte ist aber vorsortiert,

Das bezweifele ich.
Du hast keinen Einfluß auf die interne Anordnung der Datensätze.

Die Daten bekomme ich in einer Textdatei, dort sind sie nach dieser Spatle sortiert. Ich lese sie dann in dieser reihenfolge in die DB ein.

Wenn Deine Treffer in der Tat unique sein sollten,

Ich verstehe nicht ganz was du mit unique _hier_ meinst.
Vieleicht zur Erklärung:
In Sp1 stehen Postleitzahlen, in Sp3 Straßennamen und in Sp2 einen Art Staußen-PLZ-ID (die ich brauche) .
Suchen wir z.B. nach der "Dr.-Max-Plack-Straße" in 98765 Musterstadt.
Wenn ich Glück habe, finde ich _ein_ ergebnis bei:
SELECT Sp2 FROM tabele WHERE Sp1='98765' AND Sp3='Dr.-Max-Plank-Straße'

Es gibt aber oft Schreibfehler bei den Straußen.
Also suche ich, wenn ich kein Ergebnis bekomme nach den einzelen Straßennamen-Teilen:
[1]SELECT Sp2 FROM tabele WHERE Sp1='98765' AND Sp3 LIKE'%Dr'
[2]SELECT Sp2 FROM tabele WHERE Sp1='98765' AND Sp3 LIKE'%Max'
[3]SELECT Sp2 FROM tabele WHERE Sp1='98765' AND Sp3 LIKE'%Plank'

bei [1] bekomme ich mehere Ergebnisse -> weiter versuchen
bei [2] wenn ich Glück habe und es in dieser PLZ nur eine MAX-Straße gibt _ein_Ergebnis
bei [3] kein ergebnis, weil es richtig "Planck" heisen muß und dies die Ursanche für das nichtfinden beim ersten Versuch war ;-).

dann wäre ein Primärschlüssel über Spalte Sp1 zu empfehlen.

Wie gesagt, das mit dem Schlüssel ist mir irgendwie schleierhaft, wie macht man das ?

Grüße

Rol

[Michael Schröpl]

Hi Rol,

Wie viele _verschiedene_ Werte enthält die Spalte "sp1"?
ich schätze 10000 - 20000
(Wie viele Treffer bekommst Du im Schnitt?)
10-1000

Das ist schon recht viel - der Index projeziert nicht so toll. (Bei
Deiner enormen Datenmenge gibt er allerdings trotzdem den Ausschlag.)

Du hast keinen Einfluß auf die interne Anordnung der Datensätze.
Die Daten bekomme ich in einer Textdatei, dort sind sie nach dieser
Spalte sortiert. Ich lese sie dann in dieser reihenfolge in die DB
ein.

Jeder einzelne INSERT schreibt die Daten einer Zeile "irgendwohin".

Wenn Deine Treffer in der Tat unique sein sollten,
Ich verstehe nicht ganz was du mit unique _hier_ meinst.

Wenn Deine Postleitzahlen jeweils nur genau einen Treffer geliefert
hätten, dann hättest Du einen Primärschlüssel darüber legen können.
In Deinem Fall geht das allerdings nicht.

In Sp1 stehen Postleitzahlen, in Sp3 Straßennamen und in Sp2 einen
Art Staußen-PLZ-ID (die ich brauche) .
Suchen wir z.B. nach der "Dr.-Max-Plack-Straße" in 98765 Musterstadt.
Wenn ich Glück habe, finde ich _ein_ ergebnis bei:
SELECT Sp2 FROM tabele WHERE Sp1='98765' AND
Sp3='Dr.-Max-Plank-Straße'

Ein Index über das Paar (Sp1, Sp3) würde Deiner Datenbank erlauben,
allein schon durch Navigation durch den Indexbaum alle Treffer zu
finden.
Hast Du nur einen Index über Sp1, dann muß die Datenbank zu jedem
Treffer im Indexbaum den entsprechenden Datensatz lokalisieren und
in diesem das Feld Sp3 nachsehen - das ist langsamer. (Dafür kostet
der kombinierte Index über Sp1 und Sp3 wiederum mehr Platz, und seine
Pflege macht die INSERTs langsamer.)

Wenn Du Deine Daten jeweils in einem Rutsch komplett neu einfügst,
dann ist es übrigens besser, zuerst einzufügen und danach den Index
zu bauen als umgekehrt. (Erstens geht es schneller und zweites ist
der Indexbaum weniger fragmentiert.)

Also suche ich, wenn ich kein Ergebnis bekomme nach den einzelen
Straßennamen-Teilen:
[1]SELECT Sp2 FROM tabele WHERE Sp1='98765' AND Sp3 LIKE'%Dr'
[2]SELECT Sp2 FROM tabele WHERE Sp1='98765' AND Sp3 LIKE'%Max'
[3]SELECT Sp2 FROM tabele WHERE Sp1='98765' AND Sp3 LIKE'%Plank'

Hm ... LIKE mit wildcard vorne macht nun allerdings die Nutzung des
Indexbaums kaputt. Denn die Einträge innerhalb des Baums sind präfix-
sortiert ... und wenn Du nicht weißt, wonach Du suchst, kann das RDBMS
die Treffermenge nicht schon innerhalb des Indexbaums berechnet.

In diesem Falle bringt der kombinierte Index nichts.

bei [1] bekomme ich mehere Ergebnisse -> weiter versuchen

Es gibt eine Möglichkeit, Sp3 gegen einen regular expression zu
matchen - das dürfte schneller sein als drei Abfragen nacheinander.

bei [3] kein ergebnis, weil es richtig "Planck" heisen muß und dies
die Ursanche für das nichtfinden beim ersten Versuch war ;-).

Ob Du einen regular expression so formulieren kannst, daß er die
gewünschte Flexibilität hat, weiß ich allerdings nicht.

Viele Grüße
      Michael

[Andreas]

Hi Michael!
Sehr interessante Sache, so langsam fange auch ich an das zu verstehen. Eine Frage hätte ich aber, wie werden denn die Indizes aktualisiert? Wenn ich jetzt einen Index z.B. mit phpMyAdmin erstellt habe, über 2 Spalten z.B., wie funktioniert das dann? Wenn ich Inserts oder Updates mache wird das doch bestimmt nicht automatisch aktualisiert, oder? Wie mache ich das am besten? Muß ich das dann bei jeder Abfrage extra machen? Das verstehe ich noch nicht ganz.
Viele Grüße
Andreas

[Klaus Mock]

Hallo,
Wenn ich jetzt einen Index z.B. mit phpMyAdmin erstellt habe, über 2 Spalten z.B., wie funktioniert das dann? Wenn ich Inserts oder Updates mache wird das doch bestimmt nicht automatisch aktualisiert, oder?

Do, die Indizes werden dann bei jeder Datenmodifikation aktualisiert. Deshalb ist es auch wichtig, daß Du nur jene Indizes anlegst, die wirklich benötigt werden, da die Aktualisierung der Indizes bei Insert, Updates und Deletes auch einiges an REchenleistung bruahcen.

Grüße
  Klaus

[Andreas]

Hi!

Do, die Indizes werden dann bei jeder Datenmodifikation aktualisiert. Deshalb ist es auch wichtig, daß Du nur jene Indizes anlegst, die wirklich benötigt werden, da die Aktualisierung der Indizes bei Insert, Updates und Deletes auch einiges an REchenleistung bruahcen.

Hm, ich hatte aber mal gehört, das die zwischendurch immer wieder neu erstellt werden müssen, oder werden die bei vielen automatischen inserts, updates und deletes irgendwann wirklich etwas durcheinander und langsamer?
Und was macht man dann? Die indices löschen und neu erstellen(phpmyadmin)?
Grüße
Andreas

[Michael Schröpl]

Hi Andreas,

Doch, die Indizes werden dann bei jeder Datenmodifikation
aktualisiert. Deshalb ist es auch wichtig, daß Du nur jene
Indizes anlegst, die wirklich benötigt werden, da die
Aktualisierung der Indizes bei Insert, Updates und Deletes
auch einiges an Rechenleistung brauchen.
Hm, ich hatte aber mal gehört, das die zwischendurch immer wieder
neu erstellt werden müssen, oder werden die bei vielen automatischen
inserts, updates und deletes irgendwann wirklich etwas durcheinander
und langsamer?

Wenn Du einen vollständig balancierten binären Baum hast, dann kostet
die Suche eines Blattes innerhalb dieses Baums exakso viele Vergleiche,
wie der Baum "tief" ist.
Wenn der Baum durch Löschungen an der einen und Einfügungen an der an-
deren Stelle "schief" wird, dann werden einzelne Suchvorgänge schnel-
ler und andere langsamer - im Schnitt werden sie allerdings langsamer.
Außerdem ist für das Anlegen neuer Einträge neuer Speicherplatz not-
wendig, während beim Löschen alter Einträge nicht immer nutzbarer
Speicherplatz frei wird.
Ein "degenerierter" Indexbaum ist also nicht nur langsamer, sondern
vor allem auch größer, d. h. er belegt mehr Platz auf der Festplatte.

Und was macht man dann? Die indices löschen und neu erstellen
(phpmyadmin)?

Genau das.
Während einer solchen Änderung wird Dein System allerdings nicht
nutzbar sein - das macht man in einer "Wartungspause", vielleicht am
Wochenende oder auch nur alle paar Monate einmal - je nachdem, wie
hoch Deine Änderungsrate ist.

Meine Anmerkungen betreffen allerdings schon wirklich hohe Performance-
Anforderungen. In den meisten Fällen brauchst Du Dich um solche Dinge
nicht selbst zu kümmern - wenn Du nicht pro Tag mehrere Prozent Deines
Datenbestandes austauschst, reicht das, was die Datenbank von sich aus
kann, eine ganze Weile aus.

Viele Grüße
      Michael

[Rol]

Hi

»»Wenn ich Inserts oder Updates mache wird das doch bestimmt nicht automatisch aktualisiert...

Zum Glück mal ein Problem, das ich hier nicht habe.

Meine Tabelle wird einmal erstellt und dann nur alle halben Jahre upgedatet (oder geupdatet? Scheiß denglish!)

Die zweimal im Jahr kann ich auch die Inizes neu machen.

Viele Grüße

Rol, der sich langsam an brachbare Ergebnisse ranarbeitet ;-).

[Andreas]

HI!

Meine Tabelle wird einmal erstellt und dann nur alle halben Jahre upgedatet (oder geupdatet? Scheiß denglish!)

Das heißt nur SELECT Abfragen ein halbes Jahr?

Die zweimal im Jahr kann ich auch die Inizes neu machen.

Das ist mein Problem - was heißt denn "neu machen"? Wie mache ich einen Index, der existiert neu? Ich dachte, der würde sich dann bei änderungen selbst aktualisieren, wie ist das jetzt genau?

Rol, der sich langsam an brachbare Ergebnisse ranarbeitet ;-).

schön für Dich :-)
Und der Unterschied in der Geschwindigkeit wo wirklich so eklatant? Und nur durch den einen Index? War das jetzt weil da so viele gleiche Ergebnisse drin sind? Ich habe inzwischen auch eine super Tabelle mit PLZ-Daten und Bundesländern gefunden, 50.000 DS, das komische, die Tabelle hat die Spalten ID|Ort|PLZ|Vorwahl|Land|Staat. Ich habe die Tabelel als DUMP bekommen, udn so waren indices über folgende Spalten angelegt:
ID: primärschlüssel
Ort: Index
PLZ: Index
Vorwahl: Index
Staat: Index
Zuerst, die Daten selbst machen knapp 1.9 MB Speicherplatz aus, die Indices 2.5MB! Ist das kein Mißverhältnis?
Warum sollte man einen Index über die PLZ-Spalte erstellen, die es jede PLZ eh nur einmal gibt? ich dachte das wäre dann Schwachsinn?! Dagegen ist kein Index über das Land vorhanden, wovon es ja nur 16 gibt, das wäre doch für einen Index geradezu prädestiniert, oder habe ich da was grundlegend falsch verstanden?

Zum Hintergrund, eine normale Abfrage sieht so aus:

SELECT
  objekte.ort
    FROM orte
    LEFT JOIN objekte
       ON orte.plz=objekte.plz
    WHERE orte.Land='$bundesland'

Was würdet Ihr hierfür für Indices empfehlen? ('Ort' kann ich aus beiden Tabellen nehmen!)

Viele Grüße
Andreas

[paschef]

Hallo,

Zuerst, die Daten selbst machen knapp 1.9 MB Speicherplatz aus, die Indices 2.5MB! Ist das kein Mißverhältnis?

Nö. Das bedeutet ja nicht, daß statt in 1.9MB jetzt in 2.5MB gesucht wird. Du hast in diesen 2.5MB ja mehrere einzelne indices. Aber Du siehst schon, das man den Einsatz von INDEX sorgfältig planen sollte.

Warum sollte man einen Index über die PLZ-Spalte erstellen, die es jede PLZ eh nur einmal gibt? ich dachte das wäre dann Schwachsinn?! Dagegen ist kein Index über das Land vorhanden, wovon es ja nur 16 gibt, das wäre doch für einen Index geradezu prädestiniert, oder habe ich da was grundlegend falsch verstanden?

Zum Hintergrund, eine normale Abfrage sieht so aus:

SELECT
  objekte.ort
    FROM orte
    LEFT JOIN objekte
       ON orte.plz=objekte.plz
    WHERE orte.Land='$bundesland'

Was würdet Ihr hierfür für Indices empfehlen? ('Ort' kann ich aus beiden Tabellen nehmen!)

Grundsätzlich 2 Tips:
1.) Häufige Wiederholungen durch Normalisierung in eigene Tabelle(n) auslagern.
2.) Index über die Spalte(n) anlegen, die am häufigsten abgefragt werden. Besonders für JOIN sehr effektiv...
Wenn 1.) hier nicht in Frage kommt, dann würde ich für Deine Abfrage auf jeden Fall einen index auf "orte.land+orte.plz" und "objekte.plz" legen. Die anderen indices stören aber bei überwiegenden SELECT-Zugriffen nicht weiter.

Gruß,
paschef

[Andreas]

Hallo!

Nö. Das bedeutet ja nicht, daß statt in 1.9MB jetzt in 2.5MB gesucht wird. Du hast in diesen 2.5MB ja mehrere einzelne indices. Aber Du siehst schon, das man den Einsatz von INDEX sorgfältig planen sollte.

Stimmt eigentlich, lustigerweise wird mir der MySQL-Speicherplatz nicht berechnet :-) Also kann ich da ja so richtig loslegen :-) Hätte es evtl aus Sinn, wenn ich für die häufste Abfragen einen Index über PLZ/Land anlege, und für die die nur nach PLZ suchen noch zusätzlich einen nur über PLZ?

Grundsätzlich 2 Tips:
1.) Häufige Wiederholungen durch Normalisierung in eigene Tabelle(n) auslagern.

Aber dann brauche ich ja viele Joins, die auch recourcen fressen, oder?

2.) Index über die Spalte(n) anlegen, die am häufigsten abgefragt werden. Besonders für JOIN sehr effektiv...
Wenn 1.) hier nicht in Frage kommt, dann würde ich für Deine Abfrage auf jeden Fall einen index auf "orte.land+orte.plz" und "objekte.plz" legen. Die anderen indices stören aber bei überwiegenden SELECT-Zugriffen nicht weiter.

Was sollte ich da noch auslagern? irgendwo brauche ich ja eine Zuordnung, oder?

Grüße
Andreas

Gruß,
paschef

[Michael Schröpl]

Hi Andreas,

Stimmt eigentlich, lustigerweise wird mir der MySQL-Speicherplatz
nicht berechnet :-) Also kann ich da ja so richtig loslegen :-)
Hätte es evtl aus Sinn, wenn ich für die häufste Abfragen einen
Index über PLZ/Land anlege, und für die die nur nach PLZ suchen
noch zusätzlich einen nur über PLZ?

wenn Du eine Tuning-Maßnahme brauchst, dann rechne durch, ob sie
sich lohnt.
Wenn es nur knapp besser ist, dann sind die zusätzlichen Ressourcen
teuer und der Pflegeaufwand der eigenen Erweiterung ebenfalls.

1.) Häufige Wiederholungen durch Normalisierung in eigene
Tabelle(n) auslagern.
Aber dann brauche ich ja viele Joins, die auch recourcen fressen,
oder?

Nicht JOINs, sondern UNIONs - oder in mySQL mehrere SELECT-Statements
für die Zugriffe auf diverse Tabellen.

Viele Grüße
      Michael

[Andreas]

Hi!

1.) Häufige Wiederholungen durch Normalisierung in eigene
Tabelle(n) auslagern.
Aber dann brauche ich ja viele Joins, die auch recourcen fressen,
oder?

Nicht JOINs, sondern UNIONs - oder in mySQL mehrere SELECT-Statements
für die Zugriffe auf diverse Tabellen.

Und ist das dann gut oder nicht?

Grüße
Andreas

[Michael Schröpl]

Hi Andreas,

1.) Häufige Wiederholungen durch
Normalisierung in eigene Tabelle(n)
auslagern.
Aber dann brauche ich ja viele Joins, die auch
recourcen fressen, oder?
Nicht JOINs, sondern UNIONs - oder in mySQL
mehrere SELECT-Statements für die Zugriffe auf
diverse Tabellen.
Und ist das dann gut oder nicht?

Je nachdem - genau wie mit den Indexen.

Wenn der Gewinn an Performance die zusätzlichen
Statements und deren Abfeuern durch das mySQL-API
deutlich mehr als aufwiegt, dann macht man das halt.

Viele Grüße
      Michael

P.S.: Überlege Dir mal, wieso die Suchmaschine des
      Self-Portals mehrere Indexdateien durchsuchen
      kann und nicht nur eine einzige ...

[Andreas]

Hallo!

P.S.: Überlege Dir mal, wieso die Suchmaschine des
      Self-Portals mehrere Indexdateien durchsuchen
      kann und nicht nur eine einzige ...

??? ich hatte immer gedacht diese basiere auf MySQL??? Sind das die Verschiedenen Index-Dateien, halt Forum 2002, 2001, 2000... SELFHTML..? Sind das jeweils eigene Indexdateien? heißt das ihr habt z.B. die Ausgabe aller Forumsbeiträge 2001 in eine einzige Datei geschrieben? Wie kann man sowas denn strukturiert durchsuchen und die originalen Dateien als Links generiern? Kann man sich sowas mal angucken, bzw. gibt es irgendwo Infos wie die Self-Suche genau funktioniert?

Viele Grüße
Andreas

[Michael Schröpl]

Hi Andreas,

P.S.: Überlege Dir mal, wieso die Suchmaschine des
      Self-Portals mehrere Indexdateien durchsuchen
      kann und nicht nur eine einzige ...
??? ich hatte immer gedacht diese basiere auf MySQL???

Nein. Das sind ganz normale ASCII-Dateien, spaltensepariert mit senkrechten Strichen, und ein Haufen Perl-Logik mit regulären Ausdrücken.

Sind das die Verschiedenen Index-Dateien, halt
Forum 2002, 2001, 2000... SELFHTML..?

Genau. Zwar haben alle dasselbe Format (so wie alle
Zeilen Deiner Tabelle), aber ich wollte bewußt die
Möglichkeit schaffen, nur Teilmengen derselben zu
durchsuchen, um dem Anwender zu erlauben, weniger
Last auf dem Server zu verursachen und gleichzeitig
seine Ergebnisse schneller zu erhalten. (Deshalb ist
der Defaultwert auch, die kleinen Indexdateien mit
der hohen inhaltlichen Qualität automatisch zu selek-
tieren und die großen mit dem Archiv-Material nicht.)

Sind das jeweils eigene Indexdateien? heißt das ihr
habt z.B. die Ausgabe aller Forumsbeiträge 2001 in
eine einzige Datei geschrieben?

Im Prinzip ja - allerdings bereits vorverarbeitet.
HTML-tags sind beispielsweise keine mehr drin.

Wie kann man sowas denn strukturiert durchsuchen
und die originalen Dateien als Links generieren?

Genau so, wie Du das mit SQL auch machen würdest:
Jede Zeile hat knapp 10 Spalten, und der URL des
Originals ist eine dieser Spalten.

Kann man sich sowas mal angucken, bzw. gibt es
irgendwo Infos wie die Self-Suche genau funktio-
niert?

Hm ... ja, die gibt es eigentlich schon ... ist
bloß nie veröffentlicht worden, weil zu technisch
und zu kleine Zielgruppe und so ...

Viele Grüße
      Michael

[Michael Schröpl]

Hi Andreas,

Meine Tabelle wird einmal erstellt und dann nur alle halben
Jahre upgedatet (oder geupdatet? Scheiß denglish!)
Das heißt nur SELECT Abfragen ein halbes Jahr?

Vielleicht. Es könnte auch bedeutet, den Index ein halbes Jahr lang
degenerieren zu lassen und ihn anschließen neu aufzubauen.

Die zweimal im Jahr kann ich auch die Inizes neu machen.
Das ist mein Problem - was heißt denn "neu machen"?
Wie mache ich einen Index, der existiert neu?

DROP INDEX und danach wieder CREATE INDEX.

Ich dachte, der würde sich dann bei änderungen selbst aktuali-
sieren, wie ist das jetzt genau?

Das tut er schon ... aber seine Struktur leidet darunter.
Ein Pullover, der an vielen Stellen gestopft ist, wird dadurch
auch nicht besser, auch wenn er immer noch warm hält. ;-)

Und der Unterschied in der Geschwindigkeit wo wirklich so eklatant?

Ja. Du ersetzt einen linearen Aufwand durch einen logarithmischen.

Und nur durch den einen Index?

Streiche "nur". Der Einsatz des Index bedeutet, das gesamte Verfahren
zu ändern. Im Wesentlichen ersetzt Du das Suchen nach einem Datensatz
durch das Nachsehen in einer Sammlung einmalig durchgeführter und
sehr schnell zugänglicher Such-Ergebnisse - Du verlagerst also einen
Teil der Rechenzeit aus dem SELECT in das CREATE INDEX.
(Deshalb dauert das auch so lange!)

War das jetzt weil da so viele gleiche Ergebnisse drin sind?

Ganz im Gegenteil!
Je eindeutiger die Ergebnisse sind, desto besser der Effekt des Index.

Ich habe inzwischen auch eine super Tabelle mit PLZ-Daten und
Bundesländern gefunden, 50.000 DS, das komische, die Tabelle hat
die Spalten ID|Ort|PLZ|Vorwahl|Land|Staat. Ich habe die Tabelle
als DUMP bekommen, udn so waren indices über folgende Spalten
angelegt:
ID: primärschlüssel
Ort: Index
PLZ: Index
Vorwahl: Index
Staat: Index
Zuerst, die Daten selbst machen knapp 1.9 MB Speicherplatz aus, die
Indices 2.5MB! Ist das kein Mißverhältnis?

Nein. Du bezahlst Faktor 2 an Speicherplatz, um Faktor 1000 an Tempo
zu gewinnen!
Der Trade-Off zwischen Speicherplatz und CPU-Zeit ist m. E. der nahe-liegendste Ansatzpunkt für eine Optimierung - der wird Dir noch oft
über den Weg laufen.
Bei einem Cache ist das Verhältnis meistens schlechter.

Warum sollte man einen Index über die PLZ-Spalte erstellen, die
es jede PLZ eh nur einmal gibt? ich dachte das wäre dann
Schwachsinn?!

Genau das Gegenteil ist der Fall.

Dagegen ist kein Index über das Land vorhanden, wovon es ja nur 16
gibt, das wäre doch für einen Index geradezu prädestiniert, oder
habe ich da was grundlegend falsch verstanden?

Ja. ;-)

Ein Zugriff über den Index kostet durchaus auch Rechenzeit - aber
sehr viel weniger, als _sämtliche_ Datensätze zu prüfen.
Wenn Du aber diese Rechenzeit bezahlen mußt, dann willst Du als
Ergebnis möglichst bereits den _exakten_ Treffer haben.
Wenn Dir der Zugriff auf den Indexbaum von 1.5 Millionen Datensätze
noch 100000 Treffer zurück liefert, von denen Du dann über weitere
WHERE-Klauseln fast alle wieder wegwerfen mußt, dann hat der Index
Deine Anforderung nur zum Teil beschleunigt.

Zum Hintergrund, eine normale Abfrage sieht so aus:
SELECT
  objekte.ort
    FROM orte
    LEFT JOIN objekte
       ON orte.plz=objekte.plz
    WHERE orte.Land='$bundesland'
Was würdet Ihr hierfür für Indices empfehlen?

a) Ein Index über das Paar ("plz", "land") in der Tabelle "orte"
b) Ein Index über "plz" in der Tabelle "objekte"

Was die Reihenfolge des Paares angeht, müßte ich wissen, welche der
beiden Spalten eindeutigere Treffer liefert - diese sollte innerhalb
dieses Paares vorne sein.

('Ort' kann ich aus beiden Tabellen nehmen!)

Es mag als Fein-Tuning sinnvoll sein, auch noch "ort" als dritte
Spalte in den Index über "orte" aufzunehmen. Das würde es dem RDBMS
ersparen, überhaupt jemals auf die eigentlichen Tabellenzeilen zuzu-
greifen.

Es reicht allerdings nicht, eine beliebige Abfrage auszuwählen, um
daraus die optimale Struktur der Indexe zu bestimmen - Du mußt viel-
mehr _alle_ möglichen Abfragen kennen und am besten auch noch die
Wahrscheinlichkeit für deren Auftreten.
Wenn Du dann die Kosten für die Ausführung jedes einzelnen Statements
plus deren Wahrscheinlichkeit kennst, kannst Du die Gesamtkosten für
alle Abfragen berechnen. Und _das_ ist die Größe, die Du wahrschein-
lich optimieren willst! Denn von ihr hängt die Last auf Deinem Server
ab. Es kann durchaus sein, daß Du ein einzelnes Statement langsamer
machst, weil ein anderes, wahrscheinlicheres Statement dabei sehr viel
schneller wird.

Es kann aber genauso gut sein, daß Dein Optimierungsziel nicht in
den kleinstmöglichen Gesamtkosten, sondern in der Vermeidung eines
einzelnen Super-GAU liegt.
Nimm Dir mal die Self-Suche als Beispiel: Der Betreiber wird daran
interessiert sein, daß der Mix aus allen Abfragen den Server mög-
lichst wenig belastet - er wird also eine häufige Suche von 0.5 auf
0.2 Sekunden beschleunigen wollen, wenn dafür eine seltene Suche
von 5 auf 20 Sekunden gebremst wird. Für den Besucher, der genau
die 20-Sekunden-Suche braucht, ist das natürlich eine Katastrophe -
deshalb _kann_ das Optimierungsziel durchaus auch beinhalten, daß
keine einzelne Abfrage langsamer als <n> werden darf.

An dieser Stelle gilt noch mehr als für viele andere Forum-Fragen:
Ohne eine _exakte_ Aufgabenstellung bist Du völlig aufgeschmissen.
Es nützt wenig, _irgendwas_ zu optimieren, was später im realen
Einsatz gar nicht entscheidend ist ... das allerdings macht die
Sache so spannend ... ;-)

Viele Grüße
      Michael

[Andreas]

Hi Michael!
Erstmal vielen Dank für die ausführliche Erklährung, aber so 100%ig bin ich noch nicht da hinter gestiegen.

War das jetzt weil da so viele gleiche Ergebnisse drin sind?

Ganz im Gegenteil!
Je eindeutiger die Ergebnisse sind, desto besser der Effekt des Index.

Was meinst Du jetzt mit eindeutig? Wenn ich PLZ nehme ist das ja im Prinzip ein primärschlüssel, keine doppelten Einträge. Ist das jetzt extremst eindeutig? Aber das würde ich nicht verstehen, ob der DB jetzt in der Tabelle alle DS durchsucht, oder im Index, kann das so ein Unterschied sein?

Warum sollte man einen Index über die PLZ-Spalte erstellen, die
es jede PLZ eh nur einmal gibt? ich dachte das wäre dann
Schwachsinn?!

Genau das Gegenteil ist der Fall.

Also ist PLZ eindeutig, richtig?

...habe ich da was grundlegend falsch verstanden?
Ja. ;-)

:-)))

Ein Zugriff über den Index kostet durchaus auch Rechenzeit - aber
sehr viel weniger, als _sämtliche_ Datensätze zu prüfen.
Wenn Du aber diese Rechenzeit bezahlen mußt, dann willst Du als
Ergebnis möglichst bereits den _exakten_ Treffer haben.
Wenn Dir der Zugriff auf den Indexbaum von 1.5 Millionen Datensätze
noch 100000 Treffer zurück liefert, von denen Du dann über weitere
WHERE-Klauseln fast alle wieder wegwerfen mußt, dann hat der Index
Deine Anforderung nur zum Teil beschleunigt.

Ah, jetzt verstejhe ich doch "eindeutig, d.h. möglichst wenige DS zurückgeben, was?

Zum Hintergrund, eine normale Abfrage sieht so aus:
SELECT
  objekte.ort
    FROM orte
    LEFT JOIN objekte
       ON orte.plz=objekte.plz
    WHERE orte.Land='$bundesland'
Was würdet Ihr hierfür für Indices empfehlen?

a) Ein Index über das Paar ("plz", "land") in der Tabelle "orte"
b) Ein Index über "plz" in der Tabelle "objekte"

Was die Reihenfolge des Paares angeht, müßte ich wissen, welche der
beiden Spalten eindeutigere Treffer liefert - diese sollte innerhalb
dieses Paares vorne sein.

Also PLZ - natürlich gibt es mehr PLZ als Bundesländer :-)

Es reicht allerdings nicht, eine beliebige Abfrage auszuwählen, um
daraus die optimale Struktur der Indexe zu bestimmen - Du mußt viel-
mehr _alle_ möglichen Abfragen kennen und am besten auch noch die
Wahrscheinlichkeit für deren Auftreten.
Wenn Du dann die Kosten für die Ausführung jedes einzelnen Statements
plus deren Wahrscheinlichkeit kennst, kannst Du die Gesamtkosten für
alle Abfragen berechnen. Und _das_ ist die Größe, die Du wahrschein-
lich optimieren willst! Denn von ihr hängt die Last auf Deinem Server
ab. Es kann durchaus sein, daß Du ein einzelnes Statement langsamer
machst, weil ein anderes, wahrscheinlicheres Statement dabei sehr viel
schneller wird.

Ja, es gibt bestimmte Abfragen, die sehr häufig kommen, und die obige ist die heufigste. Alle anderen sind unwichtig.

ich glaube, so langsam verstehe ich es. Dann kann ich mich ja mal an eine andere Tabelle wagen:
Die BLZ wie oben beschrieben, und da ist die Suche sehr viel komplizierter. Und zwar geht es darum, eine Kombination aus BLZ und Bankname auf Plausibilität zu überprüfen.

Die Abfrage sieht etwas so aus:

SELECT * FROM blz
WHERE BLZ='$blz'
   AND (Ort Like '%$begriff_1%'
     OR Ort Like '%$begriff_2%'
     OR Bezeichnung Like '%$begriff_1%'
     OR Bezeichnung Like '%$begriff_2%')

Nach dem Schema will ich einen vom Benutzer eingegebene Wert auf Plausibilität überbrüfen, aber ich weiß genau das das so nicht gut ist:-)
Ich denke hier sollte ich einen Index über 'BLZ,Ort,Bezeichnung' erstellen, oder? Oder sollte ich hier lieber mit Fulltext arbeiten? Mein Problem ist halt, das der USer wahrscheinlich sowas wie 'SPK Wuppertal' eingibt, oder 'Sparkasse W' oder was da alles möglich ist, und in einem gewissen Rahmen sollen diese Sachen erkannt werden, daher spalte ich den String "SPK Wuppertal" beim Leerzeichen auf und durchsuche die 2 Spalten einzelnd nach allen Begriffen.

Was sagst Du dazu? Wie könnte man das optimieren?

jedenfalls nochmal vielen Dank für Deine Hilfe bisher, so langsam lichtet sich der Schleier...

Viele Grüße
Andreas

[Andreas]

Hi nochmal!
Und direkt noch eine Frage hinterher, hat es Sinn mehrere Indices zu erstellen, also nicht nur (PLZ,Ort,Land) sondern für eine andere Abfrage vielleicht noch (PLZ,Ort) und für noch eine andere nur (PLZ)? Sucht sich dann mysql das passende raus, oder wie ist das?
Viele Grüße
Andreas

[Michael Schröpl]

Hi Andreas,

Ganz im Gegenteil!
Je eindeutiger die Ergebnisse sind, desto besser der Effekt des
Index.
Was meinst Du jetzt mit eindeutig?

mit eindeutig meine ich "eindeutig". ;-)

Wenn ich PLZ nehme ist das ja im Prinzip ein primärschlüssel,
keine doppelten Einträge. Ist das jetzt extremst eindeutig?

Ja.

Ah, jetzt verstejhe ich doch "eindeutig, d.h. möglichst wenige DS
zurückgeben, was?

Wenige Treffer sind besser als viele - aber "eindeutig" bedeutet
"genau ein Treffer". Das ist die Voraussetzung für einen Primär-
schlüssel. Und Primärschlüssel sind die bestmöglichen Schlüssel,
weil sie die kleinstmöglichen Treffermengen liefern.

Was die Reihenfolge des Paares angeht, müßte ich wissen,
welche der beiden Spalten eindeutigere Treffer liefert -
diese sollte innerhalb dieses Paares vorne sein.
Also PLZ - natürlich gibt es mehr PLZ als Bundesländer :-)

Bingo.

Die Abfrage sieht etwas so aus:
SELECT * FROM blz
WHERE BLZ='$blz'
   AND (Ort Like '%$begriff_1%'
     OR Ort Like '%$begriff_2%'
     OR Bezeichnung Like '%$begriff_1%'
     OR Bezeichnung Like '%$begriff_2%')

Wenn Du eine '%'-wildcard links in einem LIKE hast, dann hast Du
verloren. Kein Index ist in diesem Falle nutzbar - nur noch ein
full table scan.

Wenn Du _das_ schnell bekommen willst, hilft m. E. nur eine sehr
heftige Holzhammermethode: Generiere _alle_ substrings von "Ort"
und baue eine separate Tabelle damit, welche Substrings nach
Primärschlüsseln von "blz" übersetzt.
Über diese Substrings kannst Du einen Index legen - _diese_ Zu-
griffe werden schnell, und wenn die Treffermenge klein wird, dann
ist der JOIN mit "blz" auch schnell.

Nach dem Schema will ich einen vom Benutzer eingegebene Wert auf
Plausibilität überbrüfen, aber ich weiß genau das das so nicht gut
ist:-)

Gut erkannt.

Ich denke hier sollte ich einen Index über 'BLZ,Ort,Bezeichnung'
erstellen, oder? Oder sollte ich hier lieber mit Fulltext arbeiten?

FULLTEXT speichert nur Worte. In mySQL 3 kannst Du damit keine
substrings suchen - in mySQL 4 soll das gehen, habe ich gehört.

Mein Problem ist halt, das der USer wahrscheinlich sowas wie
'SPK Wuppertal' eingibt, oder 'Sparkasse W' oder was da alles
möglich ist, und in einem gewissen Rahmen sollen diese Sachen
erkannt werden, daher spalte ich den String "SPK Wuppertal" beim
Leerzeichen auf und durchsuche die 2 Spalten einzelnd nach allen
Begriffen.

Gute Idee. Ich mache das sehr ähnlich. Du mußt allerdings damit
leben, daß FULLTEXT einige Einschränkungen hat - beispielsweise
eine Mindestlänge von Worten.
Diese läßt sich allerdings im Quelltext des myISAM-Treibers ganz
leicht ändern - danach mySQL neu übersetzen und es läuft.

Viele Grüße
      Michael

[Andreas]

Hallo!

Ganz im Gegenteil!
Je eindeutiger die Ergebnisse sind, desto besser der Effekt des
Index.

Wenige Treffer sind besser als viele - aber "eindeutig" bedeutet
"genau ein Treffer".

Dann ist aber "eindeutiger" ein Wort was es nicht geben dürfte :-)

Das ist die Voraussetzung für einen Primär-
schlüssel. Und Primärschlüssel sind die bestmöglichen Schlüssel,
weil sie die kleinstmöglichen Treffermengen liefern.

Ist der Primärschlüssel auch ein Index? Alo wen man den definiert hat man damit auch einen Index über diese Spalte?

Mein Problem ist halt, das der USer wahrscheinlich sowas wie
'SPK Wuppertal' eingibt, oder 'Sparkasse W' oder was da alles
möglich ist, und in einem gewissen Rahmen sollen diese Sachen
erkannt werden, daher spalte ich den String "SPK Wuppertal" beim
Leerzeichen auf und durchsuche die 2 Spalten einzelnd nach allen
Begriffen.

Gute Idee. Ich mache das sehr ähnlich. Du mußt allerdings damit
leben, daß FULLTEXT einige Einschränkungen hat - beispielsweise
eine Mindestlänge von Worten.

´Wenn Du sagst man könnte keine Substrings suchen, wie meinst Du das genau? Meinst Du damit mehrere Suchbegriffe gleichzeitig(halt wenn ich den ursprünglichen String in PHP auseinander genommen habe und jetzt einzelne Variablen mit den einzelnen Wörtern habe)? Oder meinst Du das man den kpl. String mysqk übergibt und da nach Teilstrings sucht?
Konnte man bei Fulltext überhaupt mehrere Suchwörter angeben?

Diese läßt sich allerdings im Quelltext des myISAM-Treibers ganz
leicht ändern - danach mySQL neu übersetzen und es läuft.

Im MySQL4(was ich nicht habe) kann man das sogar einstellen :-) Aber ich fange bestimmt nicht mit solchen Abenteuern an... später bestimmt mal ;-)

Aber Danke nochmal für die vielen Antworten, hast mir sehr geholfen!

Viele Grüße
Andreas

[Michael Schröpl]

Hi Andreas,

Dann ist aber "eindeutiger" ein Wort was es nicht
geben dürfte :-)

Yep. Streiche "eindeutiger", setze "selektiver" oder
"besser projezierend".

Ist der Primärschlüssel auch ein Index?
Alo wen man den definiert hat man damit auch einen
Index über diese Spalte?

Ja, ein PRIMARY KEY impliziert einen UNIQUE INDEX.
(Wie anders würdest Du denn performant prüfen wollen,
daß dort nur eindeutige Werte drin stehen? ;-)

Wenn Du sagst man könnte keine Substrings suchen,
wie meinst Du das genau?

Wenn Du nach "Chrysler" suchst, findest Du "DaimlerChrysler" nicht (FULLTEXT in mySQL 3.x).

Konnte man bei Fulltext überhaupt mehrere Suchwörter
angeben?

Nein. Aber Du kannst mehrere MATCH-Klauseln mit AND
verknüpfen. Wenn jeder Match hinreichend selektiv ist, dann ist das Ausmultiplizieren der Treffermengen nicht mehr arg teuer.
Eine Phrasensuche nach "Hallo Andreas" in der Spalte x könntest Du durch
   SELECT * FROM <tablename>
    WHERE MATCH (X) AGAINST "hallo"
      AND MATCH (X) AGAINST "andreas"
      AND X LIKE "%hallo andreas%";
realisieren. mySQL nimmt dann zuerst den FULLTEXT-Index und führt das LIKE nur auf die Treffer der vorherigen MATCHes durch - das kann sehr schnell werden, wenn die Suchbegriffe gut genug sind. (Sind sie extrem unterschiedlich gut, dann kann es sogar schneller sein, einige von ihnen wegzulassen - "Andreas" ist wahrscheinlich erheblich selektiver als "Hallo".)

Das ist das Modell, nach dem ich gerade selbst eine Suchmaschine gebaut habe.

Viele Grüße
      Michael

[Andreas]

Hallo Michael!

Wenn Du nach "Chrysler" suchst, findest Du "DaimlerChrysler" nicht (FULLTEXT in mySQL 3.x).

schade, wird zeit das 4.0 stabil wird, soll aber erst Herbst dieses Jahres so weit sein, und auch das nicht sicher, habe ich mir sagen lassen!

SELECT * FROM <tablename>
    WHERE MATCH (X) AGAINST "hallo"
      AND MATCH (X) AGAINST "andreas"
      AND X LIKE "%hallo andreas%";

Das ist das Modell, nach dem ich gerade selbst eine Suchmaschine gebaut habe.

Sehr interessante Sache! Vielen Dank, so langsam versteh ich das immer besser, ist halt ein großes Puzzel mit Mio von Teilen was sich langsam aber sicher immer mehr zusammensetzt - wobei das meiste nochfehlt ;-)

War wirklich interessant für mich der Thread, Dank Deiner Antworten!

Viele Grüße
Andreas

[Rol]

Hi

Meine Tabelle wird einmal erstellt und dann nur alle halben Jahre upgedatet (oder geupdatet? Scheiß denglish!)
Das heißt nur SELECT Abfragen ein halbes Jahr?

Nein! SELECT Abfrage gibt es laufend, aber Neue Daten/Daten update gibt's nur alle halben Jahre.
Dann schmeiss ich die ganze Tabell runter und lese alles neu ein.

Gurß

Rol

[Andreas]

Hi!

Nein! SELECT Abfrage gibt es laufend, aber Neue Daten/Daten update gibt's nur alle halben Jahre.
Dann schmeiss ich die ganze Tabell runter und lese alles neu ein.

Das meinte ich schon so :-) Halt nur SELECT hätte ich schreiben sollen!
Grüße
Andreas

[Rol]

Danke an alle!

Ein einfaches

CREATE INDEX indexSp1 OM tabelle (Sp1);

bringt die Selctabfrage von >7 sec auf <0,1 sec.
Werd mich jetzt noch ein Bisschen in die Theorie vertiefen...

Viele Grüße

Rol

[Michael Schröpl]

Hi Rol,

Ein einfaches
CREATE INDEX indexSp1 OM tabelle (Sp1);
bringt die Selctabfrage von >7 sec auf <0,1 sec.

Eben.

Werd mich jetzt noch ein Bisschen in die Theorie vertiefen...

Wenn Deine nachgeschalteten Abfragen tatsächlich noch in Sp3 filtern
wollen, dann solltest Du einen Index über das Paar (Sp1, Sp3) anlegen.

Viele Grüße
      Michael