MySQL sehr langsam bei folgender Abfrage...

[acimas]

Hallo!

Habe eben ein komplett neues System mit Suse 9.1 installiert auf einem P4, 2.6 Ghz mit 512 MB ... dort habe ich auch das gleiche Problem wie auf meinem Server...

so mein Problem bei folgendem Befehl

SELECT u.uid, f.forum_id, p.topic_id, u.uname, p.post_time, t.topic_title, f.forum_name FROM nuke_posts as p, nuke_users as u, nuke_forums as f, nuke_forumtopics as t WHERE (p.post_text LIKE '%xxx%' OR t.topic_title LIKE '%xxx%') AND p.topic_id = t.topic_id AND p.forum_id = f.forum_id AND (p.poster_id = u.uid || p.poster_id = '1') AND f.forum_type != 1 GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 200

braucht das System etwa 10 Minuten bis er das Resultat liefert...
- unter show Process steht dann nur copying to tmp table...

alle anderen Anfrage etc funktionieren einwandfrei und sehr schnell. Das Problem bestand bei MySQL 3.xx nicht ... erst nach dem Update auf 4.18 ...

Das Forum hat etwa 90.000 Postings ...

hat jemand eine Idee woran das liegen könnte ... habe auch schon diverse Einstellungen in der my.cnf probiert... aber kein Erfolg...

[suntzu]

Wie sind die Indices auf den Tabellen gesetzt?

[acimas]

Tabelle: nuke_posts

Name Typ Kardinalität Aktion Feld
PRIMARY PRIMARY 94343 post_id
post_id INDEX 94343 post_id
topic_id INDEX 9434 topic_id
forum_id INDEX 6 forum_id
poster_id INDEX 1747 poster_id

[oxygen]

so mein Problem bei folgendem Befehl

Code: Select all

SELECT u.uid, f.forum_id, p.topic_id, u.uname, p.post_time, t.topic_title, f.forum_name FROM nuke_posts as p, nuke_users as u, nuke_forums as f, nuke_forumtopics as t WHERE (p.post_text LIKE '%xxx%' OR t.topic_title LIKE '%xxx%') AND p.topic_id = t.topic_id AND p.forum_id = f.forum_id AND (p.poster_id = u.uid || p.poster_id = '1') AND f.forum_type != 1 GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 200

Krank. Ein Join über 4 Tabellen in Verbindung mit zwei LIKE's. Da hilft auch kein KEY oder INDEX wirklich. Schmeiß das Script weg.

[bjulian84]

Die Likes könnte man ja vielleicht noch ertragen, aber dann LIKE %x% :evil:
a) Abfrage aufteilen
b) wenn's ein LIKE %x% sein muss, dann nimm doch die FULLTEXT-Indizes (vgl. http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html)

Gruss,
Julian

[nikon]

zumal:

PRIMARY PRIMARY 94343 post_id
post_id INDEX 94343 post_id

mach den INDEX(post_id) weg, dann wird auch schon performanter.
und (wie mein Vorredner schon sagte) nimm FULLTEXT für
p.post_text
und
t.topic_title
.

[bjulian84]

Oh, war mir gar nicht aufgefallen - den Overhead kann man wohl wirklich sparen 8)

[nikon]

abgesehen davon bringt das auch einwenig Performance.

[bjulian84]

Aber hauptsächlich bei INSERTS / UPDATES - denn da muss er neu aufgebaut werden!
Beim SELECT sollte das doch eigentlich egal sein?
Er (MySQL) wird ja wohl nicht beide Indizes durchsuchen wollen?!


(Schon klar, dass das überflüssig ist )

[nikon]

weiss ich nicht - so tief bin ich nicht in der Materie.

btw. wir werden OT.

[odysseus]

Aber hauptsächlich bei INSERTS / UPDATES - denn da muss er neu aufgebaut werden!

Der Aufwand zur internen Pflege des Volltext-Index' ist veschwindend gering, wenn man bedenkt, welchen handfesten Vorteil du durch die Volltextsuche im Vergelich zu den LIKEs herausholst.

Dein Query mit den beiden LIKE Statements wird nie, nie, nie, niemals anständig laufen. Eine Volltextsuche ist hier die einzig sinnvolle Lösung. :)

[bjulian84]

Um den Volltext-Index gings nicht, sondern um den doppelten auf post_id (via Primary Key und Index)

[acimas]

Danke an alle... ich werde es gleich mal probieren...

die SQL-Abfrage kommt übrigens von dem Script searchbb.php ...

[bjulian84]

Im Ernst :?: :?: :?: :?:
Zeugt ja nicht von beeindruckender Perfomance das ganze :evil:

[acimas]

So habe umgestellt auf Fulltext und die Indizes auch ...

Indizes :
1 Name Typ Kardinalität Aktion Feld
PRIMARY PRIMARY 6 forum_id
forum_id INDEX 6 forum_id
forum_name INDEX 6 forum_name

Die SQL - Abfrage wie folgt:

SELECT u.uid, f.forum_id, p.topic_id, u.uname, p.post_time, t.topic_title, f.forum_name, match (p.post_text) against ('test') as score FROM nuke_posts as p, nuke_users as u, nuke_forums as f, nuke_forumtopics as t WHERE (match (p.post_text) against ('test')) AND p.topic_id = t.topic_id AND p.forum_id = f.forum_id AND (p.poster_id = u.uid || p.poster_id = '1') AND f.forum_type != 1 GROUP BY t.topic_id ORDER BY score desc LIMIT 200


Abfrage ist leider nur ein bissel schneller geworden...

Ich habe folgendes festgestellt:

Suche ich nach einem Wort welches nicht im Forum vorkommt oder nur einmal, dann liegt die Ausgabe bei unter einer Sekunde ... je häufiger das Wort im Forum vorkommt je länger dauert die Ausgabe... wie ist das zu erklären ??

Danke für eure Hilfe!

[bjulian84]

Ein mögliches Problem ist wahrscheinlich der "Super-Join".
Ich weiß nicht, wie MySQL die Abfragen optimiert, aber - ich nehme an, er muss ja jeden gefunden Datensatz dann zusammen"joinen".

Versuch doch mal die Abfrage zu splitten, um zu sehen, wie die Perfomance dann ist?

[acimas]

Sorry, aber

Was ist den Super-Join ??

Thx!

[Anonymous]

SELECT
u.uid, f.forum_id, p.topic_id, u.uname
,p.post_time, t.topic_title, f.forum_name
FROM
nuke_posts as p
,nuke_users as u
,nuke_forums as f
,nuke_forumtopics as t
WHERE
(p.post_text LIKE '%xxx%' OR t.topic_title LIKE '%xxx%')
AND p.topic_id = t.topic_id
AND p.forum_id = f.forum_id
AND (p.poster_id = u.uid || p.poster_id = '1')
AND f.forum_type != 1
GROUP BY t.topic_id
ORDER BY p.post_time DESC LIMIT 200

Eine Frage, was macht das "Group By" da? Mir fällt auf Anhieb nicht ein, wozu man das gebrauchen könnte, falls man keine Aggregatfunktionen benutzt.

Ist sichergestellt, dass auf "forum_id", "topic_id" und "poster_id" jeweils ein Index liegt?

[acimas]

Das sorgt dafür, dass nur der Thread für jeder Fundstelle nur einmal gelistet wird, denn innerhalb eines Threads kann das gesuchte ja mehrfach vorkommen. Ist also absolut notwending und kostet nix an Zeit ...

[Anonymous]

Hm, das ist kein Standard-Sql :(. Eigentlich müßte dann sowas wie max(p.post_time) und group by nach allem anderen formuliert werden. Kannst Du bitte mal ein explain vor das select schreiben und es dann einmal mit einem häufig und einmal mit einem selten vorkommenden Suchwort ausführen?! Das Ergebnis täte mich interessieren.

[acimas]

Danke an alle für die Tips !


Problem gelöst ... Abfrage ist jetzt super super schnell...

Habe die Suche nach dem User im Forum herausgenommen und siehe da... es fluppt ...