MySQL sehr langsam bei folgender Abfrage...

MySQL, PostgreSQL, SQLite
acimas
Posts: 8
Joined: 2004-06-16 11:09
Location: Solingen

MySQL sehr langsam bei folgender Abfrage...

Post by acimas » 2004-07-08 15:43

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
Posts: 669
Joined: 2002-12-20 19:47
Location: Mönchengladbach

Re: MySQL sehr langsam bei folgender Abfrage...

Post by suntzu » 2004-07-08 16:03

Wie sind die Indices auf den Tabellen gesetzt?

acimas
Posts: 8
Joined: 2004-06-16 11:09
Location: Solingen

Re: MySQL sehr langsam bei folgender Abfrage...

Post by acimas » 2004-07-08 16:15

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
Posts: 2138
Joined: 2002-12-15 00:10
Location: Bergheim

Re: MySQL sehr langsam bei folgender Abfrage...

Post by oxygen » 2004-07-08 16:34

acimas wrote:
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
Posts: 23
Joined: 2004-06-25 10:55
Location: Dortmund

Re: MySQL sehr langsam bei folgender Abfrage...

Post by bjulian84 » 2004-07-08 16:36

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
Posts: 21
Joined: 2004-06-28 10:03

Re: MySQL sehr langsam bei folgender Abfrage...

Post by nikon » 2004-07-08 17:18

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
Posts: 23
Joined: 2004-06-25 10:55
Location: Dortmund

Re: MySQL sehr langsam bei folgender Abfrage...

Post by bjulian84 » 2004-07-08 17:27

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

nikon
Posts: 21
Joined: 2004-06-28 10:03

Re: MySQL sehr langsam bei folgender Abfrage...

Post by nikon » 2004-07-08 18:31

abgesehen davon bringt das auch einwenig Performance.

bjulian84
Posts: 23
Joined: 2004-06-25 10:55
Location: Dortmund

Re: MySQL sehr langsam bei folgender Abfrage...

Post by bjulian84 » 2004-07-08 18:39

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 :wink:)

nikon
Posts: 21
Joined: 2004-06-28 10:03

Re: MySQL sehr langsam bei folgender Abfrage...

Post by nikon » 2004-07-08 18:40

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

btw. wir werden OT.

odysseus
Posts: 115
Joined: 2003-02-07 10:21

Re: MySQL sehr langsam bei folgender Abfrage...

Post by odysseus » 2004-07-08 21:11

bjulian84 wrote: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
Posts: 23
Joined: 2004-06-25 10:55
Location: Dortmund

Re: MySQL sehr langsam bei folgender Abfrage...

Post by bjulian84 » 2004-07-09 08:49

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

acimas
Posts: 8
Joined: 2004-06-16 11:09
Location: Solingen

Re: MySQL sehr langsam bei folgender Abfrage...

Post by acimas » 2004-07-09 09:58

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

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

bjulian84
Posts: 23
Joined: 2004-06-25 10:55
Location: Dortmund

Re: MySQL sehr langsam bei folgender Abfrage...

Post by bjulian84 » 2004-07-09 10:12

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

acimas
Posts: 8
Joined: 2004-06-16 11:09
Location: Solingen

Re: MySQL sehr langsam bei folgender Abfrage...

Post by acimas » 2004-07-12 17:59

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
Posts: 23
Joined: 2004-06-25 10:55
Location: Dortmund

Re: MySQL sehr langsam bei folgender Abfrage...

Post by bjulian84 » 2004-07-12 18:28

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
Posts: 8
Joined: 2004-06-16 11:09
Location: Solingen

Re: MySQL sehr langsam bei folgender Abfrage...

Post by acimas » 2004-07-12 18:47

Sorry, aber

Was ist den Super-Join ??

Thx!

Anonymous

Re: MySQL sehr langsam bei folgender Abfrage...

Post by Anonymous » 2004-07-12 19:01

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
Posts: 8
Joined: 2004-06-16 11:09
Location: Solingen

Re: MySQL sehr langsam bei folgender Abfrage...

Post by acimas » 2004-07-12 19:12

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

Re: MySQL sehr langsam bei folgender Abfrage...

Post by Anonymous » 2004-07-12 19:38

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
Posts: 8
Joined: 2004-06-16 11:09
Location: Solingen

Re: MySQL sehr langsam bei folgender Abfrage...

Post by acimas » 2004-07-12 19:42

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 ...