Datenbankindizierung mysql

Computer: Software & Programmierung Datenbanken allgemein
#1

Hi Leute,

ich habe eine InnoDB-Tabelle, die sieht folgendermaßen aus:

ID: Int, primary key
link: text
tag: text
comment: text 
datetime: timestamp 
subject: text
deleted: bool

Hier werden zu bestimmten Themengebieten (subjects) Links gespeichert (link), getaggt (tag) und mit einem Kommentar (comment) versehen. Gelöschte Einträge werden nur als gelöscht markiert (deleted), nicht tatsächlich gelöscht.

Normalisierung ist mir in dem Fall unwichtig, da es für den Kern der Frage keine Rolle spielt.

Jetzt gibt es zwei Suchfelder. Einmal die Auswahl eines Subjects, das muss dann ein voller Treffer sein und zweitens einen freien Suchtext. Der Suchtext wird an Leerzeilen getrennt und dann auf die Spalten tag und comment losgelassen.

Beispiel: Themengebiet „Technik“, Suche nach „Biometrie Fingerabdruck“

Im SQL sieht das dann so aus:

SELECT ID, link, tag, comment, datetime, subject 
FROM links 
WHERE deleted=0 
AND subject='Technik' 
AND (tag LIKE '%Biometrie%' OR comment LIKE '%Biometrie%') 
AND (tag LIKE '%Fingerabdruck%' OR comment LIKE '%Fingerabdruck%') 
ORDER BY datetime DESC, subject, tag

Soweit so gut. Das geht auch alles, aber die Frage ist jetzt, wie ich die Indizierung mache.

Durch diese verschachtelten Unds/Oders bin ich nicht ganz klar, wie die Indizes optimalerweise aussehen müssten.

Für Hinweise bin ich dankbar.

Herzlichst,

Günther

#2

Hallo Günther,

SELECT ID, link, tag, comment, datetime, subject
FROM links
WHERE deleted=0
AND subject=‚Technik‘
AND (tag LIKE ‚%Biometrie%‘ OR comment LIKE ‚%Biometrie%‘)
AND (tag LIKE ‚%Fingerabdruck%‘ OR comment LIKE
‚%Fingerabdruck%‘)
ORDER BY datetime DESC, subject, tag

Durch diese verschachtelten Unds/Oders bin ich nicht ganz
klar, wie die Indizes optimalerweise aussehen müssten.

Optimalerweise ist immer so ne Sache… Aber wenn Du mit Jokerzeichen arbeitest, verwendet MySQL eh keine Indizes, sondern den Turbo-Boyer-Moore-Algorithmus (habe ich lange drauf gewartet, das Wort mal anzubringen :wink: Und der soll laut MySQL-Seite schneller sein.
Wenn Sie … LIKE ‚%string%‘ verwenden und string mehr als drei Zeichen umfasst, verwendet MySQL den Turbo-Boyer-Moore-Algorithmus zur Initialisierung des Musters für den String; mit diesem Muster wird die Suche dann schneller durchgeführt.
und
Der Index kann auch für LIKE-Vergleiche benutzt werden, wenn das Argument zu LIKE ein Konstanten-String ist, der nicht mit einem Jokerzeichen beginnt.
http://dev.mysql.com/doc/refman/5.1/de/mysql-indexes…
Aber da vielleicht auch mal nach Stringkonstanten gesucht wird, würde ich trotzdem zumindest die in Frage kommenden Felder (wahrscheinlich Subject und tag) indizieren, da bei einer AND-Abfrage der Index nur benutzt wird, wenn alle verknüpften Felder indiziert sind. Wie gesagt, letzteres spielt nur bei der Abfrage nach Konstantenstrings eine Rolle.

Viele Grüße
Marvin

#3

Hallo Marvin,

vielen Dank für deine schnelle Antwort. Dass ein Joker einen Unterschied macht, war mir gar nicht bekannt. Wieder was gelernt. Könnte ich also für diese Joker-Abfrage grundsätzlich auf Indizes verzichten?

Aber meine eigentliche Frage bleibt. Wie indiziere ich eine verschachtelte UND/ODER-Abfrage.

Nehmen wir den Fall an

SELECT *
FROM links
WHERE deleted=0
AND subject=‚Technik‘
AND (tag=‚Biometrie‘ OR comment=‚Biometrie‘)
AND (tag=‚Fingerabdruck‘ OR comment=‚Fingerabdruck‘)
ORDER BY datetime DESC, subject, tag

Wäre dann ein mehrspaltiger Index

deleted, subject, tag, comment

richtig? Wenn alles AND wäre, ware es ja so. Aber mit diesem Oder dazwischen?

Und macht es Sinn, für die Order By-Klausel auch einen Index zu definieren? Also

datetime, subject, tag

Es gibt ja auch noch die EXPLAIN-Anweisung. Die ist für mich aber ein Stück weit mystisch und liefert mir auch immer „nur“ den Index für die WHERE-Klausel zurück, nicht für die Sortierung. Oder?

Herzlichst,

Günther

#4

Moin, Günther,

Nehmen wir den Fall an

SELECT *
FROM links
WHERE deleted=0
AND subject=‚Technik‘
AND (tag=‚Biometrie‘ OR comment=‚Biometrie‘)
AND (tag=‚Fingerabdruck‘ OR comment=‚Fingerabdruck‘)
ORDER BY datetime DESC, subject, tag

Wäre dann ein mehrspaltiger Index

deleted, subject, tag, comment

richtig? Wenn alles AND wäre, ware es ja so.

nein, dem ist nicht so. Das wäre nur dann sinnvoll, wenn nach der Verkettung der Felder gesucht würde, sie werden einzeln aber abgefragt.

Aber mit diesem Oder dazwischen?

Hat damit wenig zu tun. Stell mal die Frage, was nötig ist, um ein Einzelergebnis zu liefern, also die Grundlage für ein AND oder ein OR.

Und macht es Sinn, für die Order By-Klausel auch einen Index
zu definieren? Also

datetime, subject, tag

Sortiert wird nicht die Tabelle, sondern das Ergebnis der Abfrage, deshalb ist es sinnlos, einen Index zu legen.

Gruß Ralf

#5

Hallo Ralf,

Vielen Dank für deine Antwort.

Wäre dann ein mehrspaltiger Index

deleted, subject, tag, comment
richtig? Wenn alles AND wäre, ware es ja so.

nein, dem ist nicht so. Das wäre nur dann sinnvoll, wenn nach
der Verkettung der Felder gesucht würde, sie werden einzeln
aber abgefragt.

Aber die MySQL-Doku sagt was anderes:

http://dev.mysql.com/doc/refman/5.5/en/multiple-colu…

Oder verstehe ich dich falsch?

Aber mit diesem Oder dazwischen?

Hat damit wenig zu tun. Stell mal die Frage, was nötig ist, um
ein Einzelergebnis zu liefern, also die Grundlage für ein AND
oder ein OR.

Wie die Dokumentation (Link oben) beschreibt, wird bei Or-Verknüpfungen der mehrspaltige Beispielindex nicht verwendet. Also macht es doch tatsächlich einen Unterschied.

Und macht es Sinn, für die Order By-Klausel auch einen Index
zu definieren? Also

Sortiert wird nicht die Tabelle, sondern das Ergebnis der
Abfrage, deshalb ist es sinnlos, einen Index zu legen.

Das leuchtet ein. Aber würde der Index zum Sortieren genutzt, wenn ich alle Datensätze der Tabelle ausgeben würde? Oder ist das auch Quatsch?

Herzlichst,

Günther

1 Like