MySQL 5.0.13 -> InnoDB Volltextindizierung!?

[Mik3e]

Hi zusammen,

Folgende Situation:
Es gibt eine Tabelle, in der Produktbeschreibungen gespeichert sind. Da diese prinzipiell eine beliebige Länge haben dürfen, ist das Attribut vom Typ TEXT.

Bisher habe ich immer mit MyISAM Tables gearbeitet, möchte nun aber (aufgrund der Transaktionssicherheit) InnoDB anwenden.

Zu meiner Überraschung musste ich feststellen, dass man bei InnoDB Tables Felder vom Typ "Text" nicht mehr indizieren kann (Volltextindizierung).

Nachdem in der Applikation natürlich eine Suche nach der Produktbeschreibung möglich sein soll, befürchte ich nun ziemliche Performance-Einbussen ohne Indizierung.

Hat vielleicht jemand von Euch eine Ahnung, warum die Inidzierung von Text-Feldern in InnoDB nicht möglich ist? (Wird die Inidzierung vielleicht automatisch vorgenommen?). Oder habt Ihr einen anderen Vorschlag, diesesProblem zu lösen?

Danke & LG,
Mike

 

[hpvw]

Sie können keinen Schlüssel auf eine BLOB- oder TEXT-Spalte setzen.

Es ist halt so.

InnoDB ist nicht die eierlegende Wollmilchsau, für die Du sie vielleicht gehalten hast.
Stell' Dir mal vor, InnoDB würde MyISAM einfach um Transaktionen und Fremdschlüssel ergänzen. Dann wäre es sinnlos, weiterhin an den MyISAM-Strukturen zu entwickeln.

Ich sehe drei Lösungsmöglichkeiten:

1. Du stellst durch Testdaten fest, wie groß der Performanceverlust ist und entscheidest dann, ob Du damit leben kannst.
2. Dir schaust Dir Dein Konzept noch mal genau an und überlegst, ob Du auf Transaktionssicherheit verzichten kannst. Die Fremdschlüsselreferenzierung ist IMHO ohnehin noch zu unvollständig implementiert (oder hat sich das in der 5er geändert?), um sie als Entscheidungskriterium zu nehmen.
3. Du benötigst die Transaktionssicherheit und InnoDB ohne Volltextindizierung ist zu langsam oder Du benötigst die Features der Volltextsuche: Lager die Volltext-Felder in eine MyISAM-Tabelle aus. Das ist zwar nicht unbedingt "normal", aber hier vielleicht aus Performancegründen geboten. Dein Konzept muss dann sicherstellen, dass Änderungen an den Produktbeschreibungen atomare Transaktionen sind. Ein Beispiel:
   Tabelle product (InnoDB)
   • id, int auto_increment pk
   • name, char(255)
   • andereAttribute
   Tabelle productDescription (MyISAM)
   • id, int auto_increment pk
   • product, int fk
   • description, text fulltext index

Gruß hpvw

 

[Mik3e]

Hi,

Ich will ja keine eierlegende Wollmilchsau, sondern ein halbwegs stabiles DBMS das grundlegende Funktionen bietet. Wozu brauch ich Triggers und stored Procedures (toll das die angeblich so toll sind ab 5.0), wenn ich nicht mal Textfelder indizieren kann?

Hast Du schon mal mit InnoDB gearbeitet? Wie prägt sich hier die Transaktionssicherheit aus (Commit, Rollback!?). Bisher habe ich bei MyISAM eine "künstliche" Transaktionssicherheit mit Table-Locking erzeugt, was allerdings doch eine recht kritische Angelegenheit ist (Stichwort Deadlocks etc.)..

Vielleicht mal auf Oracle umsteigen, damit habe ich bisher aber leider noch gar keine Erfahrung außer auf HP-Großrechnern. Und das ist schon ein paar Jährchen her.
Es gibt ja jetzt angeblich eine "offene" Oracle DB.. Nur hab ich keine Ahnung, wie gut das Teil mit PHP zusammenarbeitet.

LG
Mike

 

[Mik3e]

Hi,

Ich stöber gerade die InnoDB Doku durch.. Dabei bin ich auf folgende Zeilen gestoßen:

"InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine. "

und:

"InnoDB is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores over 1TB of data in InnoDB, and another site handles an average load of 800 inserts/updates per second in InnoDB."

Das klingt für mich alles nach Inidzierung... Wie könnte man sonst in einer DB mit 1 Terabyte überhaupt noch etwas fnden!?

Werde mich mal weiter durch das Manual schlagen.

LG
Mike

 

[hpvw]

Hast Du schon mal mit InnoDB gearbeitet?

Ja, in Verbindung mit MPTT-Strukturen.

Wie prägt sich hier die Transaktionssicherheit aus (Commit, Rollback!?).

Es funktioniert. Ich habe bewusst mal mittendrin ein Rollback eingebaut und das DBMS hat alles brav zurückgenommen. Bei mir steckt ausschließlich die Struktur in der InnoDB-Tabelle, alle Daten landen in MyISAM-Tabellen.

Zu den Stored Procedures: Ich bin am überlegen, ob ich die Steuerung der MPTT-Struktur (Einfügen, Verschieben, Löschen) in Stored Procedures auslagere. Eine Stored Procedure aufrufen gegen 100 Zeilen PHP-Code mit etlichen Queries hat schon was für sich. Dagegen spricht allerdings, dass ich mich noch in Stored Procedures einlesen und einen Server mit MySQL 5.x finden muss.

Gruß hpvw

 

[Mik3e]

Naja.. das mit den stored Procedures ist so eine Sache...
Wirklich verwenden kann man die ja meist nur für typische Maintanance-Geschichten.
Informelle Abfragen auf Daten werden ja normalerweise erst zur Laufzeit (abhänigig von unterschiedlichen Parametern) generiert. Das lässt sich mit Stored-Procedures nicht realisieren. Abgesehen davon ist es schwierig, die Stored-Procedures modular in eine Applikation zu implementieren (Updates etc. werden dadurch unnötig mühsam).

Das Teilen von Tabellen in MyISAM und InnoDB möchte ich ehestmöglich umgehen... Aber wie gesagt, ich muss mir erst mal die gesamte Doku durchlesen.

Oracle:
Nebenher lade ich mir gerade die Oracle DB 10g runter.. Bin ja schon gespannt, was das Teil alles draufhat und ob es überhaupt Libraries für PHP gibt. Es gibt immerhin ein eigenes PHP Development Center auf oracle.com -> Das stimmt mich zuversichtlich

Auch wenn MySQL sehr gut ist, an Oracle kommt fast keine DBMS ran... Und wenn man es schon verwenden kann, sollte man das auch ausnützen

LG
Mike

 

[Mik3e]

Hi,

Lese mir gerade Deine Modified Preorder Tree Traversal Algorithmen durch.. Ist ja eine geniale Sache (kannte ich gar nicht). Werde ich für meinen Kategorie-Baum einsetzen.

Der einzige Nachteil ist, dass Updates bei der Struktur sicher sehr mühsam zu realisieren sind...

Beispiel:
Verschieben das 4. Element in der 3. Ebene vor das 7. Element der 5. Ebene...
Das ist ne nette Methode, die das dann berechnen darf )

Ich werd mal ne Klasse programmieren, die diese Funktionen anbietet (Insert, Update, Delete). Schaun wir mal, wie weit ich kommen ...

Ich geb Dir dann den Source-Code.. Vielleicht findest Du ja noch die eine oder andere Verbesserungsmöglichkeit

Ciao,
Mike

 

[hpvw]

Ich habe das ja schon hinter mir
Das Verschieben ist nur halb so schlimm :-(
Ich habe es gelöst, indem ich den zu verschiebenden Teilbaum zunächst in einen temporären Baum schiebe, dann den originalen Baum vorbereite, den Teilbaum zurückschiebe und den temporären Baum lösche.
Dazu ist es nötig zusätzlich zu dem im Link genannten Modell eine rootId einzuführen, die die einzelnen Bäume kennzeichnet. So ist es auf anderen Seiten zum MPTT (oder auch nested sets) beschrieben, um mehr als eine Wurzel und somit mehrere Bäume in einer Tabelle abbilden zu können.

Den Pseudocode, den ich mir in Stichwort-Englisch geschrieben habe kann ich ja mal veröffentlichen.
Verschieben eines beliebiegen Teilbaums (auch eines Blattes):

lock table
start transaction
get lft and rgt from element to move (as old lft, old rgt)
create temp root (lft := 1; rgt := old rgt - old lft + 3)
move old elements to temp root,
    set
        root := temp root
        lft:=lft - old lft + 2
        rgt:=rgt - old lft + 2
compact old set
    update elements in old tree (two queries needed)
        set
        1)  lft := lft - old rgt + old lft - 1
            where lft > old rgt
        2)  rgt := rgt - old rgt + old lft - 1
            where rgt > old rgt
make space for insertion
    get rgt from element to insert after 
        or (if insert as first subnode of a parent) 
            get lft from the new parent (as new rgt)
    update elements in old tree (two queries needed)
        set
        1)  lft := lft + old rgt - old lft + 1
            where lft > new rgt
        2)  rgt := rgt + old rgt - old lft + 1
            where rgt > new rgt
insert the moved tree back in the set
    set
        root := old root
        lft:=lft + new rgt - 1
        rgt:=rgt + new rgt - 1
delete the temp root
commit
unlock table

Vielleicht geht es auch mit weniger Queries und ohne temporären Baum, aber nur so konnte ich es mir vorstellen und implementieren.

Zu jedem Query gehört übrigens eine Abfrage, ob es einen Fehler verursacht hat und abhängig von dieser Prüfung ein Rollback.

Gruß hpvw

PS: Auch wenn semantisch die Kombination aus rootId und lft bzw. rootId und rgt unique ist, sollte man sie nicht so definieren, da MySQL in Updates zeilenweise prüft und nicht erst beim Abschluss des gesamten Queries. Sind diese Schlüssel unique, gibt es Probleme, da die zuerst neu berechneten Werte mit den noch nicht berechneten kollidieren können, auch wenn es bei vollständiger Ausführung des Queries keine Kollision geben würde.

PPS: Zu Deinem Post von 13:17 Uhr:
Endgültige Gewissheit, über die Performance im Vergleich zu eine indizierten MyISAM-Tabelle wird nur ein Performancetest bezüglich Deines spezifischen Problems geben. Vielleicht wird bei den genannten Beispielen ja auch keine Volltextsuche durchgeführt. Andere Keys kannst Du ja auch bei InnoDB anlegen.

 

[Mik3e]

Hi,

Sieht ja interessant aus Werde mich dann auch mal daran machen..

Noch zwei Dinge:
1. Wie setzt Du Transaktionssteuerung bei gebündelten atomaren Transaktionen ein?
Sehe ich das so richtig:
a.) START TRANSACTION
b.) SELECT, UPDATE, DELETE etc.
c.) COMMIT
d.) Verify Data
e.) On Error: ROLLBACK;

2. Wie setzt du die foreign key constraints ein?
Ich blick bei Cascade, no action etc. noch nicht wirklich durch...

Ciao,
Mike

 

[hpvw]

Sieht ja interessant aus Werde mich dann auch mal daran machen..

Das Verschieben ist aber auch so ziemlich das umständlichste, was man machen kann. Im Prinzip steck da auch schon alles drin, was man zum Löschen und Einfügen benötigt.

Mein Konzept sieht übrigens vor, dass Strukturmanipulationen immer über Funktionen mit leichter verständlichen Parent- und Previous-Parametern erfolgen. Kurzer Auszug aus meiner Einfüge-Doku:

    /**
     * addElement
     *
     * Inserts a new node in the database.
     * Its position is identified by the parent and the
     * previous element.
     * Sample:
     * Root
     *   Element 1
     *     Element 1.1
     *     Element 1.2
     *   Element 2     <- parent
     *     Element 2.1 <- previous
     *     New element
     *     Element 2.2
     *   Element 3
     *
     * Another sample:
     * Root
     *   Element 1
     *     Element 1.1
     *     Element 1.2
     *   Element 2     <- parent and previous
     *     New element
     *     Element 2.1
     *     Element 2.2
     *   Element 3
     *
     * And one more:
     * Root            <- parent
     *   Element 1
     *     Element 1.1
     *     Element 1.2
     *   Element 2     <- previous
     *     Element 2.1
     *     Element 2.2
     *   New element
     *   Element 3
     * ...
     */

Noch zwei Dinge:
1. Wie setzt Du Transaktionssteuerung bei gebündelten atomaren Transaktionen ein?
Sehe ich das so richtig:
a.) START TRANSACTION
b.) SELECT, UPDATE, DELETE etc.
c.) COMMIT
d.) Verify Data
e.) On Error: ROLLBACK;

Nach dem COMMIT ist meines Wissens Hopfen und Malz verloren.
Die Prüfung muss also, meiner Meinung nach, vor dem COMMIT erfolgen.
Entweder so:

• LOCK TABLES
• SET AUTOCOMMIT=0
• BEGIN
• ([SELECT | UPDATE | DELETE | etc.], ...)
• Verify
  • on error: ROLLBACK
  • on success: COMMIT
• SET AUTOCOMMIT=1
• UNLOCK TABLES

Oder so:

• LOCK TABLES
• SET AUTOCOMMIT=0
• BEGIN
• [SELECT | UPDATE | DELETE | etc.]
• Verify, on error:
  • ROLLBACK
  • SET AUTOCOMMIT=1
  • UNLOCK TABLES
  • return;
• [SELECT | UPDATE | DELETE | etc.]
• Verify, on error:
  • ROLLBACK
  • SET AUTOCOMMIT=1
  • UNLOCK TABLES
  • return;
• ...
• COMMIT
• SET AUTOCOMMIT=1
• UNLOCK TABLES
• return;

2. Wie setzt du die foreign key constraints ein?

Gar nicht, wie angedeutet, halte ich die Implementierung bei MySQL noch nicht für ausgereift. Sollte sich das mit der 5er geändert haben, muss ich mich damit noch näher beschäftigen.

Gruß hpvw