[mysql] LEFT JOIN...GROUP BY is sloom

[Verwijderd]

Ik heb een database met 3 tabellen.

1 - forums (id, omschrijving);
2 - forumtopics (id, id_forums, id_user, titel, tekst, ind_locked, timestamp);
3 - forumposts (id, id_topics, id_user, tekst, timestamp);

Kardinaliteit tabel 1: 7
Kardinaliteit tabel 2: 2000
Kardinaliteit tabel 3: 35523

Ik wil nu een topic overzicht genereren. Mijn query:

code:

SELECT
    forumtopics.*, 
    count(forumposts.id),
    max(forumposts.timestamp)
FROM 
    forumtopics 
LEFT JOIN 
    forumposts
ON 
    forumtopics.id = forumposts.id_topics 
WHERE 
    forumtopics.id_forums = '$forum_id' 
GROUP BY 
    forumtopics.id 
LIMIT
     0,15

Het probleem is dat deze zeer en zeer traag is, als ik de GROUP BY weg haal, dan is hij super snel, alleen krijg ik dan een aantal de zelfde topics te zien (dit aantal is afhankelijk van het aantal replies)

forumtopics.id is een primaire sleutel en tevens INDEX
forumposts.id is een primaire sleutel en tevens INDEX

Moet ik mijn database beter optimaliseren?, zo ja hoe dan? zijn mijn indices wel goed? of ligt het aan de qeury?

[whoami]

Waarom doe je een group by? Imho is een group by enkel nuttig als je gebruik maakt van aggregated functions (zoals MAX, MIN, AVG, SUM, ...).

[CyberSnooP]

Op dit moment is je join zinloos, omdat je select niks uit de tabel forumposts haalt. Ik ga er vanuit dat je het doet zodat je het aantal posts per topic kunt berekenen.

Echter, de query wordt op die manier onwijs langzamer. Beter is het om die informatie in een veld bij het topic op te slaan (ookal spreekt dit je gevoel tegen, je slaat in principe dubbele informatie op). Bij elke post of delete moet je die teller dus aanpassen.

Als je dat hebt gedaan kun je simpelweg een select doen op alleen forumtopics

[Verwijderd]

whoami schreef op 18 September 2002 @ 11:32:
Waarom doe je een group by? Imho is een group by enkel nuttig als je gebruik maakt van aggregated functions (zoals MAX, MIN, AVG, SUM, ...).

hij moet ook het max aantal ophalen en de hoogste timestamp, ik had het niet in bovenstaande query gezet, stom... ik zal hem even aanpassen.

[Verwijderd]

CyberSnooP schreef op 18 September 2002 @ 11:35:
Op dit moment is je join zinloos, omdat je select niks uit de tabel forumposts haalt. Ik ga er vanuit dat je het doet zodat je het aantal posts per topic kunt berekenen.

Echter, de query wordt op die manier onwijs langzamer. Beter is het om die informatie in een veld bij het topic op te slaan (ookal spreekt dit je gevoel tegen, je slaat in principe dubbele informatie op). Bij elke post of delete moet je die teller dus aanpassen.

Als je dat hebt gedaan kun je simpelweg een select doen op alleen forumtopics

Nu wel, sorry was vergeten het erbij te zetten Jou optie om alle informatie in forumtopic op te slaam heb ik ook aan lopen denken, want dat is natuurlijk veel sneller.

Hoe gebeurd dit bij grootte fora's ala GOT dan?

[Verwijderd]

Zet ook wat indices op je foreign key kolommen. Die worden tenslotte ook gebruikt voor joins. Misschien zul je eens het queryplan moeten bekijken. Geen flauw idee hoe je dat doet met MySQL, maar het kan wel, is mij verteld.

Verder is een GROUP BY geen snelle operatie, dus verwacht een trage query.

HTH

[Verwijderd]

Hmm en als ik nu het volgende zal doen:

1 - forums (id, omschrijving);
2 - forumtopics (id, id_forums, id_firstpost, ind_locked, timestamp);
3 - forumposts (id, id_topics, id_user, tekst, timestamp);

dus met een id_firstpost werken?, zal dat beter zijn?

[ACM]

Theoretisch is het niet beter
Praktisch wel

Het is veel sneller daardoor, maar de foutkans neem toe en je slaat redundante informatie op.
Ik zou het wel doen, zeker als het niet zoveel uitmaakt dat er een enkel keertje es wat fout in zou kunnen zitten.

[cameodski]

Verwijderd schreef op 18 september 2002 @ 11:43:
Verder is een GROUP BY geen snelle operatie, dus verwacht een trage query.

GROUP BY is wel traag, maar niet zo traag.

Als je de juiste indices hebt, zit het probleem waarschijnlijk aan één van de volgende dingen:
- MySQL wordt niet zo blij van de LEFT JOIN (probeer anders eens zonder LEFT)
- Het GROUP BY proces heeft last van velden zoals titel en tekst, waardoor één record nog al groot is. (effe testen met alleen de count, max en eventueel het id in de resultset)

Ik vermoed dat je ook nog wel een ORDER BY nodig hebt en die moet dan vast op de MAX liggen, maar wat doe je dan als er geen posts zijn?

[Verwijderd]

cameodski schreef op 18 september 2002 @ 13:04:
[...]

GROUP BY is wel traag, maar niet zo traag.

True ... de door de gebruiker genoemde snelheid (of juist gebrek daaraan) is wel buitensporig, dus er zit waarschijnlijk iets anders dwars.

Als je de juiste indices hebt, zit het probleem waarschijnlijk aan één van de volgende dingen:
- MySQL wordt niet zo blij van de LEFT JOIN (probeer anders eens zonder LEFT)

Oh? Volgens http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html is het just sneller om expliciet het benodigde JOIN type aan te geven, zodat de query geoptimaliseerd kan worden.

[cameodski]

Verwijderd schreef op 18 september 2002 @ 13:25:
Oh? Volgens http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html is het just sneller om expliciet het benodigde JOIN type aan te geven, zodat de query geoptimaliseerd kan worden.

Sorry, ik was misschien niet helemaal duidelijk. Ik bedoelde ipv LEFT OUTER JOIN een INNER JOIN gebruiken. En INNER JOIN is toch echt sneller dan een LEFT OUTER JOIN.