[mysql] Reacties vertragen site

[RSD]

Met de volgende query haal ik de laatste reacties op:

SQL:

SELECT MAX(c.id) AS commentid,c.mediaid,m.title 
FROM `comment` c 
JOIN media m ON (m.id=c.mediaid) 
GROUP BY c.mediaid 
ORDER BY c.commentid DESC 
LIMIT 0,15

Echter als ik EXPLAIN doe, zie ik dat de query behoorlijk traag is:

Als info komt er voornamelijk uit dat:

Gebruikt geen keys
Rows = 8608
Extra = Using temporary; Using filesort

De query duurt ongeveer 0.04 sec. Dat is veelste lang volgens mij. Nu vroeg ik me af of ik deze query ook anders kan doen, zodat hij sneller gaat en zodat hij geen dubbele titels laat zien, dus dat hij niet eenzelfde regel weergeeft als de laatste reacties op één en hetzelfde artikel zijn.

[Voor 1% gewijzigd door moto-moi op 03-12-2010 15:25. Reden: highlighting == hipheid]

[Soultaker]

Voor welke kolommen heb je precies indices gedefinieerd? Ik neem aan in ieder geval voor comment.id en media.id (want dat lijken me primary keys), maar om de GROUP BY efficient uit te kunnen voeren, zou je op z'n minst een index moeten hebben op comment.mediaid, en het liefst of comment.(mediaid,id).

De query zelf ziet er trouwens prima uit, dus ik zou de oplossing eerder zoeken in het definiëren van betere indices, dan aan het herschrijven van de query. (Maar misschien dat blijkt dat dat toch nodig is nadat je ons verteld hebt waar de indices op gedefinieerd zijn.)

[Voor 30% gewijzigd door Soultaker op 16-11-2010 17:41]

[NMe]

Je maakt je druk om 4 honderdsten van een seconde. Zou ik me niet zo druk om maken eerlijk gezegd.

Verder: heb je netjes indexes staan op alle id-velden die in je query staan?

[RSD]

Indices staan op c.id, c.mediaid en op m.id.

[Soultaker]

Maar niet op comment.(mediaid,id) gecombineerd? Kun je die eens proberen toe te voegen?

[Voor 29% gewijzigd door Soultaker op 16-11-2010 17:57]

[RSD]

Hij gebruikt wel een key nu, maar nog steeds:

Using index; Using temporary; Using filesort en de snelheid is ook niet erg vele hoger.

[Wolfboy]

Hoe voer je die query uit? Weet je zeker dat die 0.04 seconden geen meetfout is?
Met queries die zo snel uitvoeren heb je al snel dat het resultaat soms 10x zo snel is zonder dat er daadwerkelijk iets veranderd is.

Anyway, met een index op "mediaid,id" zou het vrij snel moeten zijn.

[RSD]

Ja dat is een explain query, ik weet het ook niet. De kardinaliteit van die nieuwe key is mediaid=0, id-8609

Ik weet niet of dat wel goed is?

[Soultaker]

Die temporary table + filesort komt waarschijnlijk doordat je alles uiteindelijk wil sorteren op commentid. Daar is geen efficiëntere operatie voor te verzinnen, ben ik bang. Voordeel is wel dat je maximaal één rij per "media" hebt en niet één per comment.

[Wolfboy]

RSD schreef op dinsdag 16 november 2010 @ 18:35:
Ja dat is een explain query, ik weet het ook niet. De kardinaliteit van die nieuwe key is mediaid=0, id-8609

Ik weet niet of dat wel goed is?

Nee, dat is niet goed. Draai even een optimize table en probeer het dan nog eens

[cariolive23]

RSD schreef op dinsdag 16 november 2010 @ 17:15:
De query duurt ongeveer 0.04 sec. Dat is veelste lang volgens mij.

Ok, dat is 4 honderdsten van een seconde. Stel dat je dit 10x sneller maakt, dus 4 duizendsten van een seconde, hoeveel mensen gaan dit verschil merken? Ik ben bang dat jij de enige bent, puur omdat jij de enige bent die dit met z'n stopwatch kan meten. Geen mens die zich druk maakt over 4 honderdsten.

Heb je al eens jouw complete script doorgemeten? Ik durf te wedden dat er knelpunten in zitten die véél meer tijd kosten en waar je dus ook veel meer tijd kunt winnen.

[Voutloos]

Eens met cario in deze.

Áls het echt sneller moet, ga je al gauw extra complexiteit toevoegen, door oplossingen als caching en/of denormalisatie (bv een media.lastCommentTimestamp). De eenvoudigste truc is dan het cachen van de laagste commentid, zodat je die steeds in de where clause kan zetten.

Maar beargumenteer eerst maar voldoende dat dit dé bottleneck is en dat het anders moet.

[RSD]

Nou, de load van de server was behoorlijk hoog en nadat ik de comments tijdelijk had uitgeschakeld, werd het opeens veel minder, dus vandaar. Ik heb ook een veld met een timestamp waar de tijd in staat dat deze is toegevoegd. Kan ik deze misschien gebruiken?

Daarnaast als er 20k bezoeker op komen elke dag die weer goed zijn voor 200k pageviews, maken die paar honderdsten toch best wel uit.

[Voor 20% gewijzigd door RSD op 03-12-2010 15:18]

[RobIII]

RSD schreef op vrijdag 03 december 2010 @ 15:15:
Kan ik deze misschien gebruiken?

Heb je 't al eens geprobeerd

[RSD]

Nee, omdat ik niet zou weten wat ik ermee zou moeten doen. Er wordt aangegeven dat je die kan cachen, maar ik begrijp niet precies wat hij bedoelt.

Ik heb al een snellere oplossing. Door wat velden weg te laten en het gebruik van DISTINCT.

[Voor 24% gewijzigd door RSD op 03-12-2010 16:36. Reden: De oplossing ;-)]

[cariolive23]

RSD schreef op vrijdag 03 december 2010 @ 15:15:
Nou, de load van de server was behoorlijk hoog en nadat ik de comments tijdelijk had uitgeschakeld, werd het opeens veel minder, dus vandaar.

Ok, maar het uitschakelen van de comments, is veel meer dan deze ene query uitschakelen.

Daarnaast als er 20k bezoeker op komen elke dag die weer goed zijn voor 200k pageviews, maken die paar honderdsten toch best wel uit.

Is dat zo? Stel dat je deze 200.000 pageviews in slechts 2 uur moet verwerken, i.p.v. in 24 uur, dan heb je het over 27 pageviews per seconde. Wanneer jij per pageview 10 queries moet uitvoeren, dan heb je het dus over 270 queries per seconde, wat echt niet veel is voor een database. Ook MySQL moet dit aankunnen. Met het optimaliseren van één query die nu slechts 4 honderdsten van een seconde duurt, ga je de boel niet echt significant versnellen.

Ga dus uitzoeken wat het échte probleem is. Gebruik je bv. MyISAM? Dan krijg je vanzelf problemen met tablelocks wanneer je veel insert's/update's/delete's hebt en dus performanceproblemen. Of gebruik je zeer inefficiente PHP-code? Of voer je queries uit binnen een while-lusje? Allemaal dingen die 100x meer impact hebben dan een query van 4 honderdsten van een seconde....