SQL Query optimierung / beschleunigung

[nivadis]

Hallo,

habe folgende query mit subselects und joins, etc.

die abfrage dauert über 20 Sekunden .. was mich sehr nervt ... jedoch finde ich im Internet kein vernünftigen query optimizer / analyzer .. vielleicht könnt ihr mir helfen...

(Handelt sich um eine art Volltextsuche in mehreren Tabellen)
Die Datenbank Struktur ist im Datei-Anhang.
folgende query:

SELECT COUNT( p.id )
FROM produkte AS p
LEFT JOIN hersteller AS h ON h.id = p.hersteller_id
LEFT JOIN produkte_history AS ph ON ph.produkte_id = p.id
LEFT JOIN produkte_has_files AS phfs ON phfs.produkte_id = p.id
LEFT JOIN files AS fs ON fs.id = phfs.files_id
LEFT JOIN kontakte AS k ON k.id = ph.an_id
WHERE (
p.name LIKE '%hp%'
OR h.name LIKE '%hp%'
OR p.typenbezeichnung LIKE '%hp%'
OR ph.rechnungsnummer LIKE '%hp%'
OR p.seriennummer LIKE '%hp%'
OR p.serialnummer LIKE '%hp%'
OR p.sntext LIKE '%hp%'
OR p.dmmd_nummer LIKE '%hp%'
OR p.dmmd_anummer LIKE '%hp%'
OR fs.orignname LIKE '%hp%'
OR fs.freitext LIKE '%hp%'
OR p.freitext LIKE '%hp%'
OR (
k.firma1 LIKE '%hp%'
OR k.spitzname LIKE '%hp%'
AND ph.erstellt_datum = (
SELECT max( ph.erstellt_datum )
FROM produkte_history AS ph
WHERE ph.produkte_id = p.id
AND ph.an_id = k.id )
)
OR p.id IS NULL
)
AND k.id = (
SELECT DISTINCT an_id
FROM produkte_history
WHERE erstellt_datum = (
SELECT max( erstellt_datum )
FROM produkte_history
WHERE produkte_id = p.id )
GROUP BY erstellt_datum )
GROUP BY p.id
ORDER BY p.erstellt_datum DESC

die Indizes sind alle auf die PKs und FKs gesetzt.

ich weiß einfach nicht mehr weiter!

danke schon mal im voraus!

lg nivadis

[nivadis]

ok die query braucht bei mehr datensätzen gleich schon 141 Sekunden

Hier die Mess Analyse von PHPMyAdmin
Messen
Status Dauer
(initialization) 1.2755227
Opening tables 0.8946887
System lock 0.0747440
Table lock 0.0530915
init 0.0412365
optimizing 0.0000212
statistics 0.1404672
preparing 0.1260762
executing 0.2320507
Sending data 0.000998
end 0.0000167
query end 0.000013
freeing items 0.0063272
closing tables 0.0000147
removing tmp table 0.0001287
closing tables 0.0000107
logging slow query 0.000006

Zeige Datensätze 0 - 29 (50 insgesamt, die Abfrage dauerte 141.0148 sek.)


was genau bedeuten die oberen beiden punkte... kann man dort was optimieren?

(initialization) 1.2755227
Opening tables 0.8946887

danke. lg nivadis

[cortex]

bin sicher nicht der db-guru... was mir auffällt:

- sehr viele joins
- sehr viele LIKE mit vorangestellten platzhalter |1
- sehr viele subselects

|1 bei der suche via %LIKE kann kein index genutzt werden; ein nachgestellter platzhalter (LIKE%) ist unproblematisch.

values innerhalb einer WHERE-condition zu indizieren, ist das A und O einer effizienten db-abfrage. ich würde für den ersten moment vorschlagen:

- auf die vorangestellten platzhalter verzichten
- alle spalten, die in den WHERE-conditions auftauchen, indizieren

cx

[nivadis]

Hmm dann die frage, wenn das alles sehr viele sind .. wie kann man das ganze realisieren mit weniger ...

gut das Problem ist es soll eine Volltextsuche werden und somit ist der Platzhalter sehr relevant.

da auch Buchstabenkombinationen in mitten eines Datenbankfeldes gefunden werden soll.

die Indizierung habe ich schon durchgeführt ... jedoch ohne merkbaren erfolg ...

oder eine andere Idee meinerseits:

würde die Verwendung der "Volltextsuche" in mysql das Statement beschleunigen?

meine Idee wäre wie folgt: (jedoch gibts dabei immer den Fehler #1210: Incorrect arguments to MATCH) vielleicht habe ich die Syntax der Funktion nicht ganz verstanden aber irgendwie bekomme ich das so auch nicht ans laufen.

SELECT * FROM
produkte AS p
INNER JOIN hersteller AS h ON h.id=p.hersteller_id
INNER JOIN produkte_history AS ph ON ph.produkte_id=p.id
INNER JOIN produkte_has_files AS phfs ON phfs.produkte_id=p.id
INNER JOIN files AS fs ON fs.id=phfs.files_id
INNER JOIN kontakte AS k ON k.id=ph.an_id



WHERE MATCH (p.name,h.name,p.typenbezeichnung,ph.rechnungsnummer,p.seriennummer,p. serialnummer,p.sntext,p.dmmd_nummer,p.dmmd_anummer,fs.orignname,fs.fre itext,p.freitext) AGAINST ('%hp%')
OR
( k.firma1 like '%hp%'
OR k.spitzname like '%hp%'
AND ph.erstellt_datum =
( SELECT max( ph.erstellt_datum )
FROM produkte_history AS ph
WHERE ph.produkte_id = p.id
AND ph.an_id = k.id
)
)
AND k.id = (
SELECT DISTINCT an_id FROM produkte_history WHERE erstellt_datum = (
SELECT max(erstellt_datum) FROM produkte_history WHERE produkte_id = p.id)
GROUP BY erstellt_datum )
GROUP BY p.id
ORDER BY p.erstellt_datum DESC
LIMIT 0,50

[cortex]

wie kann man das ganze realisieren mit weniger

k.a. - dazu kann ich dir keine konkreten hinweise geben.


das Problem ist es soll eine Volltextsuche werden und somit ist der Platzhalter sehr relevant.


dann musst du in kauf nehmen, dass der db-index nicht benutzt wird. ganz im gegenteil: wenn die tabelle des öfteren geändert wird, wird sich das neuschreiben des index als zusätzlicher flaschenhals bemerkbar machen.


die Indizierung habe ich schon durchgeführt ... jedoch ohne merkbaren erfolg ...


dazu sagte ich bereits:

bei der suche via %LIKE kann kein index genutzt werden; ein nachgestellter platzhalter (LIKE%) ist unproblematisch.


würde die Verwendung der "Volltextsuche" in mysql das Statement beschleunigen?


du dokterst lediglich an den symptomen herum. je unschärfer deine WHERE-bedingung, desto aufwändiger die abfrage. was du da vorhast, bringt das dbms gehörig ins schwitzen. nochmal mein hinweis:


values innerhalb einer WHERE-condition zu indizieren, ist das A und O einer effizienten db-abfrage.


Incorrect arguments to MATCH


die syntax lautet folgendermaszen:


WHERE
( var = 'value' )
AND MATCH
( something1, something2, something3 )
AGAINST
( 'anything' )


das WHERE ( var = 'value' ) ist natürlich nicht obligatorisch...

cx

[nivadis]

values innerhalb einer WHERE-condition zu indizieren, ist das A und O einer effizienten db-abfrage.

aber grad nochmal zur verständnis frage: also alle felder die in der WHERE bedingung durchsucht werden sollten in der Datenbank als Indizes markiert werden?


danke.

lg nivadis

[cortex]

also alle felder die in der WHERE bedingung durchsucht werden sollten in der Datenbank als Indizes markiert werden?

korrekt, und zwar so, wie sie notiert werden:

WHERE var_a = '$a' AND var_b = '$b' benötgt einen (gemeinsamen) index über var_a und var_b

schau dir mal das entsprechende kapitel im handbuch an: Wie MySQL Indizes benutzt (http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html)

cx

[nivadis]

ja ok .. nur bei mir ist halt das Problem das ich eine abfrage über mehrere Tabellen mache und ich den Index doch nicht über mehrere Tabellen machen kann, oder?

ich hab einen FULLTEXT index über die entsprechenden Felder in den einzelnen Tabellen gemacht. ohne erfolg immmer folgende fehlermeldung:

#1191 - Can't find FULLTEXT index matching the column list



und das mit dem Platzhalter funktioniert wohl doch mit indizierung

If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly.

lg nivadis

[DokuLeseHemmung]

Am Rande:
EXPLAIN SELECT ....dein.statement......
Explain kann dir zeigen wo es klemmt.
Es macht keinen Sinn an der falschen Stelle zu rumzudoktorn.

[cortex]

[...] und ich den Index doch nicht über mehrere Tabellen machen kann, oder?

soweit ich weiss, ist das nicht möglich.


#1191 - Can't find FULLTEXT index matching the column list


falls das auf deine volltext-suche (MATCH... AGAINST...) zielt: man braucht einen FULLTEXT index, der alle such-spalten umfasst. falls sich nun deine such-spalten in mehreren unterschiedlichen tabellen befinden... sackgasse.


und das mit dem Platzhalter funktioniert wohl doch mit indizierung


LIKE 'var%' kann einen index nutzen; LIKE '%var' hingegen nicht.

cx

[cortex]

If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly.

1. ein string mit max. 3 zeichen dürfte für eine suche in praxi etwas kurz sein .-
2. im oben genannten zitat taucht das wörtchen index nicht auf

cx

[nivadis]

ja kann wohl sein das ich mich da etwas vertan hab ..... und nun hab ich die LÖSUNG .. die alles beschleunigt hat.

einmal trotz mit %suchwort% .. auch wenn du mit dem das die indizierung nicht genutzt wird recht ahst .. es kommt mir hier mit indizierung schneller vor!

also das ganze war einfach ne dämliche formulierung im SQL Statement .... einfach ein paar klammern anders gesetzt und schon läuft das ganze gleich viel flotter ... LOL .. das es an sowas scheitern kann!


aber auf jedenfall vielen dank für eure Hilfe!