Index auf Varchar???

[xip]

Hallo,

ich habe mal eine Frage über Performance von Mysql. Ich betreibe derzeit lokal eine Website und habe in einer meiner Tabellen ca. 500000 Einträge. Tja, und es kommen einige Anfragen pro sekunden und deshalb ist meine Prozessorlast 100%. Tja...

Ich habe keine Indizes drinnen. hier mal ganz kurz die Description!!

+--------------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-------+
| feld | varchar(500) | YES | | NULL | |
| domain | varchar(50) | YES | | NULL | |
| time | timestamp | NO | | CURRENT_TIMESTAMP | |
+--------------+--------------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)

Ich suche immer mit einer select anweisung nach dem Feld feld. Das sind die häufigsten Anfragen.

Wie sollte ich da am besten den Index setzen?? FULLTEXT auf Feld? Und dann mit Match suchen?

Hab ich schon probiert und es wurde sogar noch langsammer!!

Ich hoffe ihr könnt mir helfen.

lg

 

[SlaterB]

naja, das ist auch ein ganz schön hohes Thema,
dafür verdienen manche Berater 500 Euro die Stunde

zu Textsuche höre ich immer nur
Apache Lucene - Overview
kann aber nix dazu sagen

 

[madboy]

Würde sagen, das hängt davon ab wie gesucht wird.

like '%bla%'

ist da ganz böse, wird immer ein full table scan nötig (zumindest unter Oracle) und da bringen Indexe auch nix.
Besser ist

like 'bla%'

so weit ich mich erinnere, aber ist offentlich nicht immer möglich.

 

[ice-breaker]

naja, das ist auch ein ganz schön hohes Thema,
dafür verdienen manche Berater 500 Euro die Stunde

und ich machs umsonst

madboy kann ich mir nur anschließen, es gibt aber noch eine sehr gute Optimierung.
Speichere zusätzlich einen 32/64 Bit hash des Textfeldes (FNV-1a, Bob Jenkins) in der Datenbank, suchen tust du dann folgendermaßen:

SELECT col1, col2 FROM table WHERE hash_text = FNV('textvar') AND text = 'textvar'

du solltest natürlich noch beschreiben, ob es ein equals-check ist (dann ist mein Tipp gut brauchbar) oder du einen String innerhalb des Textes suchst -> Volltextsuche (Lucene oder Sphing, MySQL Fulltextindex geht notfalls auch, aber die Architektur davon ist miserabel, dementsprechend auch die Performance bei sehr vielen Daten)

Ein Index muss aber natürlich sowieso drauf, erst Recht bei der anzahl, kein Wunder dass der Prozessor auf 100% ist.

 

[xip]

vielen Dank für eure Antwort.

Tja mit den Datenbankeinträgen ist das schon so eine Sache.

Ich kenne mich damit nicht so gut aus.

Wollte jetzt erstmal INDEX setzen:

CREATE FULLTEXT neuerindex ON tabellefeld (feld);

oder wie würdet ihr den setzen? Soll ich mit Match()..Against() suchen?

allerdings hatte ich gerade eine INDEX so gesetzt und die Performance deutlich steigern können:

ALTER TABLE tabellefeld ADD INDEX testindex (feld, domain, time);

 

[ice-breaker]

wunderbar wenn nicht auf Fragen geantwortet wird sondern die gleiche Frage einfach wieder gestellt wird :autsch:

 

[xip]

tja, sie SELECT Queries sind deutlich schneller geworden, aber wenn ich noch mit INSERT INTO arbeite wirds sogar noch langsamer!

 

[ARadauer]

tja, sie SELECT Queries sind deutlich schneller geworden, aber wenn ich noch mit INSERT INTO arbeite wirds sogar noch langsamer!

das ist genau das Verhalten das man erwartet.
Du hast nun über alle drei Felder einen Index. Dadurch bist du beim Lesen schneller nur beim Schreiben wo dein Index aktualisert werden muss bist du langsamer.. und das Teil wird auch mehr Speicher brauchen (ist das heute noch ein Thema?)

also wenn du auf das char Feld einen Index legst und beim select kein like (%wurschti) drinnen hast sollte es auf jeden Fall schneller werden.

Das wär so das Mittelding aus beiden Extremen, kein Index und Index über alles.

Zeig mal bitte dein Select query

 

[xip]

dank dir für deine Antwort:

ich suche so:

SELECT feld FROM tabellefeld WHERE domain = 'WERTAUSPROGRAMM';

 

[ice-breaker]

dann implementiere meine Lösung aus #4 und setze einen Index auf das Feld "hash_text". Den Index auf dem Varchar entfernst du wieder.
MySQL hat keine builtin-Methode für diesen Hashwert, den müsstest du also in Java berechnen.

Damit sollte die Datenbank wieder super schnell sowohl beim Einfügen als auch beim Lesen sein, wenn nicht, analysieren wir weiter

 

[SlaterB]

domain klingt auch nach begrenzter Wertemenge, da könnte man dann auch Int-Werte 1-x nehmen,
evtl. in einer separaten Tabelle das Mapping darstellen, kommt dem Hash aber sicher nahe

 

[xip]

so wie ich das verstehe lahmt diese INSERT INTO Statement die Datenbank. Weil der Index bei jedem neu angepasst wird.

Wenn ich mir jetzt von jedem String den ich in die Datenbank schreibe den HASH Wert berechnen lasse und ihn dann in die Datenbank schreibe, suche ich nur nicht mehr nach dem String sondern nach dem HASH Wert. Aber ich füge weiterhin durch
INSERT INTO ein. Wobei wir wieder beim vorherigen Problem sind. Oder verstehe ich da was falsch?

 

[ice-breaker]

Mit der Mapping-Tabelle muss er ja trotzdem erst den varchar suchen, das Mapping nehmen und dann joinen.
Der Sinn des Hashes ist einfach die String-Lookups zu verschnellern, 1. geht eint Int schneller, 2. braucht ein Int deutlich weniger Platz im Index (also mehr Arbeitsspeicher für den Rest)

 

[ice-breaker]

so wie ich das verstehe lahmt diese INSERT INTO Statement die Datenbank. Weil der Index bei jedem neu angepasst wird.

Moment, das musst du nunmal spezifizieren, was du genau meinst
Also ein Insert auf Tabelle A lahmt auch alle Anfragen die an Tabelle B gehen?

Aber ich füge weiterhin durch INSERT INTO ein. Wobei wir wieder beim vorherigen Problem sind. Oder verstehe ich da was falsch?

neija die Problemstellung die du nun von dir gibst, ist anders als vorher, das Problem solltest du also genauer beschreiben.

 

[SlaterB]

> Oder verstehe ich da was falsch?

ja, denn durch den Index auf den Hash statt auf den String sollte es schneller gehen

----

zur Mapping-Tabelle:
Joinen und Suche, ja, aber die Suche in den 1000 Elementen der Mapping-Tabelle wird sicher schneller gehen als in den 500.000 normalen Einträgen, wenn diese Relation besteht,

evtl. läßt sich das Mapping gar in Java cachen, dann nicht mal Join,
sondern z.B. ne HashMap um den Domain-int zu bekommen, womit man auch wieder bei Hash wäre

 

[ice-breaker]

zur Mapping-Tabelle:
Joinen und Suche, ja, aber die Suche in den 1000 Elementen der Mapping-Tabelle wird sicher schneller gehen als in den 500.000 normalen Einträgen, wenn diese Relation besteht

Wie kommst du auf die 1000?
Ich sehe in keinem Post konkrete Werte oder Angaben, dass hier eine 1:n-Beziehung der Informationen vorliegt.
Ausserdem sucht er doch nach dem feld-Wert und nicht nach der Domain.

 

[SlaterB]

> Wie kommst du auf die 1000?

na schau dir doch den Anfang meines Vorschlags an:
> SELECT feld FROM tabellefeld WHERE domain = 'WERTAUSPROGRAMM';

> domain klingt auch nach begrenzter Wertemenge, da könnte man dann auch Int-Werte 1-x nehmen,

nur wenn das erfüllt ist, wenn es um wenige Domain-Arten gibt, dann macht meine Idee Sinn

 

[ice-breaker]

achja, stimmt, er sucht ja nach Domains, unsere beiden Ideen kombiniert könnten da schon einiges bringen.

Aber ohne genauere Information des Thread Erstellers kann man nur rumraten.