[MySQL] Optimaliseren van te trage query

[Upsal]

Na zoeken op google, lezen in de GoT FAQ en andere topics, ben ik er nog steeds niet achter gekomen hoe ik de volgende query sneller kan maken:

code:

SELECT m2.`nickname`, m1.`ip`, COUNT( DISTINCT m1.`ip` ) AS `aantal`, m1.`referer`
FROM `members` AS `m1`
INNER JOIN `members` AS `m2` ON m2.`nickname` = m1.`referer`
WHERE m2.`health` > '0'
AND m2.`class` < '2'
GROUP BY m1.`referer`
ORDER BY `aantal` DESC
LIMIT 0 , 10

Huidige code doet het perfect, maar helaas duurt het 10 sec voordat de query af is, en in die 10 sec is de MySQL server volledig belast, en omdat ik het als een terugkerende (cronjob) query wil gebruiken, en de tabel 2800 rijen bevat, zal die op zijn minst snel moeten worden uitgevoerd.
Ik zelf zat al te denken om de "inner join" weg te halen, maar het probleem dan is, dat ik dan 2 members tabellen heb, die dan door MySQL door elkaar worden gehaald. Er is in werkelijkheid 1 tabel members die -naar mijn weten- 2 keer moet worden vermeld in de query. De 2de keer is voor het volgende, simpel gezegd: er mogen alleen rijen worden geplaatst in het resultaat als de kolom "referer" een echte "membersnaam" is. Zo filtert hij de neppe referers eruit.

[ Voor 4% gewijzigd door Upsal op 05-09-2005 21:34 ]

[momania]

Je kan toch ook gewoon een "where member.nickname = member.referer" doen?

Ik zie niet in waarom je een tabel aan zichzelf moet joinen

[GlowMouse]

Upsal schreef op maandag 05 september 2005 @ 21:33:
De 2de keer is voor het volgende, simpel gezegd: er mogen alleen rijen worden geplaatst in het resultaat als de kolom "referer" een echte "membersnaam" is. Zo filtert hij de neppe referers eruit.

Is het niet handiger om te zorgen dat de data klopt: de neppe referers er eerst uitfilteren.
Verder ben je nu aan het joinen en gebruik je WHERE in je query. Heb je indexen ingesteld om alles sneller te laten verlopen?

Je kan toch ook gewoon een "where member.nickname = member.referer" doen?

Niemand is waarschijnlijk zijn eigen referer.

[ Voor 14% gewijzigd door GlowMouse op 05-09-2005 21:38 . Reden: quote toegevoegd ]

[Upsal]

momania schreef op maandag 05 september 2005 @ 21:37:
Je kan toch ook gewoon een "where member.nickname = member.referer" doen?

Ik zie niet in waarom je een tabel aan zichzelf moet joinen

Dan krijg ik een verkeerd resultaat!

Met m'n huidige query krijg ik een 10 rijen, met "aantal" varierend van 1 t/m 8 (8 = hoogste)
Maar met de oplossing van jouw krijg ik een vaag resultaat waar de refers namen betreffen die maar 1 keer zijn gerefererd , plus een "aantal" dat gelijk is aan 1.

[Upsal]

GlowMouse schreef op maandag 05 september 2005 @ 21:37:
[...]

Is het niet handiger om te zorgen dat de data klopt: de neppe referers er eerst uitfilteren.
Verder ben je nu aan het joinen en gebruik je WHERE in je query. Heb je indexen ingesteld om alles sneller te laten verlopen?

Indexen zijn als het goed is ingesteld, wat je daar zegt over die neppe filters er preventief uitfilteren is een mogelijke oplossing, het liefst zou ik deze query goed werkend willen krijgen, maar als er echt niks op zit, dan dat maar.

[momania]

Upsal schreef op maandag 05 september 2005 @ 21:39:
[...]


Dan krijg ik een verkeerd resultaat!

Hoe ziet je tabel er dan uit en wat wil je als resultaat hebben?
Wat staat er bijvoorbeeld in de referer kolom, etc?

[Upsal]

momania schreef op maandag 05 september 2005 @ 21:43:
[...]

Hoe ziet je tabel er dan uit en wat wil je als resultaat hebben?
Wat staat er bijvoorbeeld in de referer kolom, etc?

In de referer kolom hoort een membersnaam te staan, maar het is voorgekomen dat gebruikers niet wisten wat ze moesten invullen en dan hebben ze het woord "nee" ingevuld. Dit hoort dus eigelijk niet in de lijst thuis. De bedoelding van de query is om een tabel te laten weergeven die de hoogste membersrefers toont (top 10 beroemste members zeg maar). Als ik "where member.nickname = member.referer" uitvoer, krijg ik een niet kloppend resultaat, precies zoals GlowMouse al zegt: een lijst van members die zichzelf hebben opgegeven als referer.

[ Voor 6% gewijzigd door Upsal op 05-09-2005 21:46 ]

[CubicQ]

Maar welke indexen heb je op de tabel staan?

Ik zit trouwens even te googlen, maar het valt me op dat je met MySQL helemaal geen fatsoenlijk query plan kan laten zien. Met een query plan kan je meestal vrij makkelijk zien waar de bottleneck zit.

[edit]
Overigens zou het waarschijnlijk een boel schelen als je een memberId toevoegd, en de referer verandert van referer naar refererId en een FK toevoegd. Nu join je op een string.

[ Voor 28% gewijzigd door CubicQ op 05-09-2005 21:52 ]

[GlowMouse]

CubicQ schreef op maandag 05 september 2005 @ 21:50:
Ik zit trouwens even te googlen, maar het valt me op dat je met MySQL helemaal geen fatsoenlijk query plan kan laten zien. Met een query plan kan je meestal vrij makkelijk zien waar de bottleneck zit.

Bedoel je EXPLAIN? Misschien dat de TS hier ook de output van kan laten zien. En dan gelijk met welke indexen je gebruikt, dan kun je kijken of en waar er wat mis gaat.

[H!GHGuY]

als zoals hij aangeeft de tabel 2800 rijen bevat
2800*2800 = veel
dan kun je al gaan denken waar zijn grootste bottleneck zit...

code:

SELECT nickname, referer, ...
FROM members
WHERE referer IN (SELECT nickname FROM members)
AND ...

dit resulteert op z'n minst al in 1 subselect, die MySQL kan bewaren, sorteren en dan met een snelle binary search kan filteren.

ik hoop alleen dat (jouw versie van) MySQL dit ondersteunt.

[ACM]

HIGHGuY schreef op maandag 05 september 2005 @ 22:15:
als zoals hij aangeeft de tabel 2800 rijen bevat
2800*2800 = veel
dan kun je al gaan denken waar zijn grootste bottleneck zit...

Maar met goed gebruik van indices is het 2800 * gemiddelde frequentie van het veld.
Het zou bijvoorbeeld kunnen helpen om een gecombineerde index op "nickname, health en class" te hebben als dezelfde nicknames vaak voorkomen. Als dezelfde nicknames weinig voorkomen is een enkelvoudige index op nickname al een leuke start om de boel te versnellen. Een index op referer kan ook helpen.
Het omdraaien van de join-condities ook trouwens, dus m1.nickname en m2.referer gebruiken. Althans, als er fatsoenlijke indexen aangelegd zijn.

dit resulteert op z'n minst al in 1 subselect, die MySQL kan bewaren, sorteren en dan met een snelle binary search kan filteren.

MySQL kan helemaal niet snel met subselects overweg, het hoeft dus helemaal niet het geval te zijn dat die subquery-variant sneller is dan de join-versie.

[Upsal]

Bedankt voor jullie hulp,

Het probleem bleek tóch aan de indexes te liggen, héél vreemd dat die nu in 1 keer spoorloos was, zonder dat ik het merkte. Ik heb dus nu de index van "Referer" weer opnieuw ingesteld. Dit heb ik trouwens nog geprobeerd: "WHERE referer IN (SELECT nickname FROM members)", maar ik krijg alleen maar syntax fouten met deze manier. Is voor mij nu niet van belang, want de query tijd blijft nu dik onder de 1 seconden.

[ Voor 10% gewijzigd door Upsal op 05-09-2005 22:52 ]