Rol: MySQL: Performaceproblem

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

  1. 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

    1. 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

      1. 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

        1. 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

          1. 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

      2. 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

  2. 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

    1. 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

      1. 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

        1. 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

          1. 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

            1. 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

              1. 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

          2. 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 ;-).

            1. 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

              1. 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

                1. 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

                  1. 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

                    1. 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

                      1. 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 ...

                        1. 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

                          1. 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

              2. 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

                1. 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

                  1. 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

                  2. 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

                    1. 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

                      1. 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

                        1. 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

              3. 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

                1. 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

  3. 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

    1. 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