query optimieren

[FoXMorayn]

(initialization) 0.000003   checking query cache for query 0.000183   checking permissions 0.00001   Opening tables 0.000023   System lock 0.00001   Table lock 0.000071   init 0.000162   optimizing 0.000035   statistics 0.000054   preparing 0.000038   Creating tmp table 0.000088   executing 0.000006   Copying to tmp table 0.018529   storing result in query cache 47.277711   Sorting result 0.000017   Sending data 0.00022   end 0.000004   removing tmp table 0.000011   end 0.000004   query end 0.000004   freeing items 0.000016   closing tables 0.000009   logging slow query 0.000057

SELECT t1.`id` 
FROM  `partner`  AS t1
LEFT  JOIN  `projekte`  AS t2 ON t1.`id`  = t2.`partner_id` 
LEFT  JOIN  `linktausch`  AS t3 ON t1.`id`  = t3.`partner` 
WHERE t1.`vorname`  LIKE  '%colour%'
OR t1.`nachname`  LIKE  '%colour%'
OR t1.`firma`  LIKE  '%colour%'
OR t1.`email`  LIKE  '%colour%'
OR t1.`notiz`  LIKE  '%colour%'
OR t1.`telefon`  LIKE  '%colour%'
OR t1.`hinweis`  LIKE  '%colour%'
OR t2.`url`  LIKE  '%colour%'
OR t2.`beschreibung`  LIKE  '%colour%'
OR t3.`backlink`  LIKE  '%colour%'
OR t3.`beschreibung`  LIKE  '%colour%'
OR t1.`id`  =  'DUMMY'
GROUP  BY t1.`id`

was sagt mir diese auswertung? ist mein query gut und ich habe einfach nur zuviele daten? das ganze muss irgendwie schneller gehen.
kann mir jemand einen tip geben?

 

[struppi]

Was sagt EXPLAIN

 

[FoXMorayn]

1 SIMPLE t1 ALL PRIMARY     [I]NULL[/I]     [I]NULL[/I]     [I]NULL[/I] 3429 Using temporary; Using filesort

                   1 SIMPLE t2 ALL     [I]NULL[/I]     [I]NULL[/I]     [I]NULL[/I]     [I]NULL[/I] 10675      

                   1 SIMPLE t3 ALL     [I]NULL[/I]     [I]NULL[/I]     [I]NULL[/I]     [I]NULL[/I] 4325 Using where

 

[struppi]

Das sieht nicht gut aus. Benutzt du keine Indizes?

 

[FoXMorayn]

ich hab mal ein wenig mit volltext indizes experimentiert, aber bin damit nicht wirklich klar gekommen. ( siehe http://www.html.de/datenbanken-z-b-mysql/34994-richtig-suchen-richtige-auswertung.html )

garantiert mir ein normaler index eine schnellere und zuverlässige suche? 12 von 14 spalten müssten indiziert werden. bei jeder suche müssen alle relevanten daten durchsucht werden.

habe gelesen, das ein optimizer sich dazwischen schaltet und versucht nur die wichtigsten daten in den index aufzunehmen, wann und welche daten entscheidet dabei das programm, wenn ich es nicht zwinge.
ist das richtig? müsste ich bei jeder änderung der datenbank ein 'hint' auführen? bringen mir die indizes dann überhaupt noch was?


das query aus dem 1. post habe ich nun in 3 gespalten, für jede datenbank eine abfrage. die ergebenisse fasse ich zusammen und wende ein array_unique an. damit liege ich geschätzt bei einer sekunde für das gleiche ergebnis! sind die joins, die ich ausführe vllt zuviel für den server? ein select über die 3 tabellen dauerte schon mehr als einige minuten, diese abfrage habe ich abgebrochen. nur der beschriebene select jeder einzelnen datenbank, bringt einen akzeptablen erfolg, aber braucht auch 3mal soviel code.

 

[struppi]

Indizes bei Datenbanken sind eine komplizierte Sache. Fulltext hat damit erstmal nichts zu tun und so wie ich die Abfrage sehe wäre das auch nicht hilfreich.

Im Prinzip sind die Felder in where, group, order oder beim join Kandiaten für einen Index. In deinem Falle wäre es aber kontraproduktiv alle Felder zu indizieren.

Ja, joins können teuer sein, müssen aber nicht. Wenn du einen Index auf partner.id (das muss aber sowieso einer sein), projekte.partner_id und linktausch.partner, legst sollte das ganze schon viel schneller abgehen.

Aber willst du wirklich so unterschiedliche Felder nach einem Begriff durchsuchen? Das halte ich nicht für sonderlich sinnvoll

 

[FoXMorayn]

ja aus usability gründen kommt alles aus einem formularfeld. (abhilfe könnte hier eine automatische erkennung von hausnummer plz telefon usw per php schaffen.)

die indizies hab ich noch nicht ganz verstanden. hilft es IDs für den join als index anzulegen, auch wenn diese als int angelegt sind? es muss so oder so jedes felld der spalte komplett ausgelesen und zugeordnet werden, um den join korrekt durchzuführen.

 

[struppi]

Indizieren kannst du jeden Feldtyp. Und das auslesen hat damit nichts zu tun, indizes sind (grob gesagt) für die Suche nach Datensätzen hilfreich.
Und ja, wie gesagt, alle Felder, die in joins auftauchen sind gute Kandidaten für einen Index.

 

[struppi]

Ach noch was. Explain ist genau dafür gedacht, rauszufinden ob und welche Indizes genutzt werden. D.h. dort muss unter key ein Wert stehen, dann ist die Abfrage besser.

 

[mermshaus]

Bringt ein normaler Index überhaupt was, wenn ich nach [WILDCARD][SUCHSTRING][WILDCARD] suche? Deshalb hatte ich in dem anderen Thread FULLTEXT vorgeschlagen.

 

[struppi]

Mit sicherheit nicht. Aber da er vermutlich fast jedes Feld in der Tabelle durchsuchen will, wäre es Unsinn alle Felder zu indizieren. Dann wäre der Index ja genauso gross wie die normale Tabelle und würde keinen Geschwindigkeitsvorteil mehr bringen. Daher ist die where Abfrage kaum zu optimieren.

 

[FoXMorayn]

bei einzelnen selects bringt ein index auch nichts?

EXPLAIN  SELECT  `spalte` 
FROM  `benutzer`

id select_type table type possible_keys key key_len ref rows Extra                 1 SIMPLE benutzer ALL     [I]NULL[/I]     [I]NULL[/I]     [I]NULL[/I]     [I]NULL[/I] 4325

für benutzer ist ein index länge 40 gesetzt.

 

[mermshaus]

Das Problem ist, dass du den Platzhalter ("%") zu Beginn der Strings in deiner Abfrage setzt. In einem Index werden die Daten grob gesagt "nach Größe" vorsortiert in einer Baumstruktur verwaltet (siehe etwa B-Baum). Diese Sortierung nach Größe würde bei Spalten mit Textinhalten wohl alphabetisch von Textanfang aus vorgehen. Das heißt, "Hund" ist kleiner als "Schlange", "Zebra" ist größer als "Schlange". Wenn die Suche nun bei "Schlange" steht, aber ein Eintrag "Hund" gesucht ist, kann der Algorithmus entscheiden, dass er dem "kleiner als"-Zweig folgen muss, weil der erste Buchstabe "H" kleiner ist als "S".

Beginnt der Suchausdruck aber mit einem Platzhalter, hat der Suchalgorithmus keine Chance, zu entscheiden, ob ein Datum kleiner oder größer als das derzeit gesuchte ist, da diese Eigenschaft undefiniert ist.

Die Doku beschreibt das hier, sehe ich gerade:

- MySQL :: MySQL 5.1 Reference Manual :: 7.5.3 How MySQL Uses Indexes

Aber da er vermutlich fast jedes Feld in der Tabelle durchsuchen will, wäre es Unsinn alle Felder zu indizieren. Dann wäre der Index ja genauso gross wie die normale Tabelle und würde keinen Geschwindigkeitsvorteil mehr bringen. Daher ist die where Abfrage kaum zu optimieren.

Ein FULLTEXT-Index dürfte die Feldinhalte in Tokens, also "Einzelwörter" aufteilen, diese in einer Baumstruktur anordnen und jeweils speichern, in welcher Datensatz-ID ein Token auftaucht. Das dürfte die Suche nach einzelnen Begriffen schon massiv beschleunigen.