Gathering of Tweakers

Voor een site heb ik een 'Product' tabel met daarin producten. Bezoekers kunnen producten reviewen, deze reviews komen in 'Review' te staan, en worden gekoppeld met een Many to Many tabel genaamd 'Product_Review'. De Many to Many relatie is in deze situatie niet nodig, maar wel voor toekomstige plannen dus dat blijft zo.
Op reviews kan gestemd worden, er kan een thumbsup of een thumbsdown gegeven worden. Dit word opgeslagen in een tabel genaamd reviewVotes. De tabellen zien er als volgt uit (alleen de relevante kolommen):
SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE IF NOT EXISTS `review` (   `reviewId` int(11) NOT NULL auto_increment, ) CREATE TABLE IF NOT EXISTS `reviewVotes` (   `voteId` int(11) NOT NULL auto_increment,   `reviewId` int(11) NOT NULL,   `vote` tinyint(3) NOT NULL ) CREATE TABLE IF NOT EXISTS `product_review` (   `productId` int(11) NOT NULL,   `reviewId` int(11) NOT NULL, )

Een vote word opgeslagen als een 1 of een -1 in de reviewVotes tabel. Wat ik nu probeer is in 1 query alle reviews van een bepaald product op te halen inclusief hun positieve en negatieve score. Nu lukt dit, maar krijg ik per review wel 2 rijen terug (een voor de positieve en een voor de negatieve votes). Wat ik probeer te bereiken is 1 rij per review terug te krijgen met 2 nieuwe kolommen, een voor positieve en een voor negatieve votes. Wat ik nu doe is:
SQL:

1 2 3 4 5 6

SELECT `r`.*, sum(`rv`.`vote`) FROM `review` AS `r`, `reviewVotes` AS `rv`, `product_review` AS `pr` WHERE `r`.`reviewId` = `rv`.`reviewId` AND `r`.`reviewId` = `pr`.`reviewId` AND `pr`.`productId` = ? GROUP BY `r`.`reviewId`, `rv`.`vote`

Ik snap dat die GROUP BY `rv`.`vote` zorgt dat ik 2 rijen terugkrijg, maar zonder dat krijg ik enkel de som van de votes terug, terwijl ik graag onderscheid wil maken tussen de positieve en negatieve scores.

Ik neem aan dat sum(`dv`.`vote`) een tikfout is en dat dit sum(`rv`.`vote`) moet zijn?

Mag ik vragen wat het nut is van een SUM als het veld enkel 1 of -1 kan bevatten?

Zoiets misschien?
SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

select      rev.review_id     , neg_votes.aantal_positief     , pos_votes.aantal_positief from  review rev left outer join      (select          review_id         , sum(vote) aantal_negatief     from reviewvotes     where vote < 0     ) neg_votes on rev.review_id = neg_votes.review_id left outer join      (select      review_id     , sum(vote) as aantal_positief     from reviewvotes     where vote > 0     ) pos_votes on rev.review_id = pos_votes.review_id

Je krijgt wel alle records treug nu, ook zonder votes. En natuurlijk niet getest, maar uit klad gedaan

quote:

TeeDee schreef op woensdag 09 juli 2008 @ 14:31:
Ik neem aan dat sum(`dv`.`vote`) een tikfout is en dat dit sum(`rv`.`vote`) moet zijn?

klopt, gefixt

quote:

MueR schreef op woensdag 09 juli 2008 @ 14:35:
Mag ik vragen wat het nut is van een SUM als het veld enkel 1 of -1 kan bevatten?

Dit leek me de snelste manier om de votes op te tellen zonder ingewikkelde IF-THEN-ELSE constructies te gebruiken of 2 losse kolommen te gebruiken voor de votes

quote:

Redshark schreef op woensdag 09 juli 2008 @ 14:37:
Zoiets misschien?
SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

select      rev.review_id     , neg_votes.aantal_positief     , pos_votes.aantal_positief from  review rev left outer join      (select          review_id         , sum(vote) aantal_negatief     from reviewvotes     where vote < 0     ) neg_votes on rev.review_id = neg_votes.review_id left outer join      (select      review_id     , sum(vote) as aantal_positief     from reviewvotes     where vote > 0     ) pos_votes on rev.review_id = pos_votes.review_id

Je krijgt wel alle records treug nu, ook zonder votes. En natuurlijk niet getest, maar uit klad gedaan

zal hem proberen, ziet er goed uit .

quote:

sky- schreef op woensdag 09 juli 2008 @ 14:35:

offtopic:
Leer je zelf aan om _geen_ backticks te gebruiken ! Backticks onderdrukken de gereserveerde MySQL column namen, zoals date en datetime etc.

W T F. Daar zijn ze voor. Door juist consistent te gebruiken voorkom je ook problemen als er mysql reserverd words bijkomen.

Dat kan natuurlijk simpeler
SQL:

1 2	sum(rv.vote=1) as aantal_positief,  sum(rv.vote=-1) as aantal_negatief

of leuker:
SQL:

1 2	sum((rv.vote*rv.vote+rv.vote)/2) as aantal_positief,  sum((rv.vote*rv.vote-rv.vote)/2) as aantal_negatief

Voor de rest: backticks, select * en het niet gebruiken van inner joins maken het niet leesbaarder.

quote:

Voutloos schreef op woensdag 09 juli 2008 @ 14:41:
[...]

W T F. Daar zijn ze voor. Door juist consistent te gebruiken voorkom je ook problemen als er mysql reserverd words bijkomen.

Dan verzin je tabellen zonder keywords. Het kan verwarrend werken in een grote query. En de query ziet er ook niet mooi uit met backticks, het oog wil ook wat he.

Dan heb je een glazen bol. Dat is niet verwarrend. Queries worden vooral leesbaar door whitespace, backticks maken daarvoor niet veel uit.

quote:

Voutloos schreef op woensdag 09 juli 2008 @ 14:41:
[...]

W T F. Daar zijn ze voor. Door juist consistent te gebruiken voorkom je ook problemen als er mysql reserverd words bijkomen.

En die namen moet je dus niet gebruiken.

Bovendien is je query 80x langzamer.
ongeveer dan

quote:

pedorus schreef op woensdag 09 juli 2008 @ 15:28:
Dat kan natuurlijk simpeler
SQL:

1 2	sum(rv.vote=1) as aantal_positief,  sum(rv.vote=-1) as aantal_negatief

of leuker:
SQL:

1 2	sum((rv.vote*rv.vote+rv.vote)/2) as aantal_positief,  sum((rv.vote*rv.vote-rv.vote)/2) as aantal_negatief

Voor de rest: backticks, select * en het niet gebruiken van inner joins maken het niet leesbaarder.

elegante oplossing! Khad geen idee dat dit kon .

De backticks gebruik ik eigenlijk standaard om snel het onderscheid tussen mysql functies, tabel- en kolomnamen en strings te zien.

quote:

TheBorg schreef op woensdag 09 juli 2008 @ 15:50:
Bovendien is je query 80x langzamer.
ongeveer dan

Met of zonder backticks? Of met backticks ge-escapete mysql reserved namen?

Met backticks is langzamer.

quote:

TheBorg schreef op woensdag 09 juli 2008 @ 16:40:
Met backticks is langzamer.

Ah dit wist ik niet , had het een beetje overgenomen van phpMyAdmin, er van uitgaande dat daar wel de best-practices zouden worden gebruikt. Ging er ook van uit dat het een beetje hetzelfde idee was als de ' ipv " in php, waardoor dingen letterlijk worden overgenomen ipv te proberen te parsen , guess not.

quote:

TheBorg schreef op woensdag 09 juli 2008 @ 16:40:
Met backticks is langzamer.

Ik kan me eerlijk gezegd niet voorstellen dat het merkbaar langzamer is op de gemiddelde tabel. De query analyzer zal hier echt niet veel meer moeite mee hebben dan het weglaten van de backtick en het resultaat van de query analyzer wordt vaak nog gechached ook zodat er met de daadwerkelijke code van de de query niks meer gebeurd maar direct het executie plan uit de cache kan worden gevist.
Heb je dit gecontroleerd of is het meer een gut feeling?

quote:

TheBorg schreef op woensdag 09 juli 2008 @ 15:50:
Bovendien is je query 80x langzamer.
ongeveer dan

Ehm... 80x? Elke query is 80x langzamer als je backticks gebruikt voor je table/kolomnamen?

Riiiiiiiiiiiiight.

Graag even een fikse onderbouwing + testresultaten, want hier geloof ik echt helemaal niets van. Lijkt me als dit namelijk ook echt zo was, MySQL er zelf ook wel wat over zou zeggen, maar ik kan er niets over vinden (performance verbetering/verslechtering met of zonder backticks) op de website, noch via Google.

Cavorka wijzigde dit bericht 09-07-2008 17:35 (21%)

Blaten over de performance invloed van backticks bij queries.
Wat is de bottleneck van menig app? De DB. Oplossing: micro-optimalisaties in de query syntax.

80% van de statistieken wordt ter plekke verzonnen he.
Mijn vermoeden is dat backticks nauwelijks meer opslagruimte kost en nauwelijks sneller/langzamer is. Afhankelijk van de implementatie kan het zelfs sneller zijn.

Backticks heeft als voordeel dat je geen last gaat krijgen van toekomstige MySQL keywords(vb: schemas als tabelnaam). Het nadeel is leesbaarheid(persoonlijk) en compatibiliteit met andere databases (het is geen ANSI SQL).

Ik ben eruit. Backticks maar achterwege gelaten vanwege de onduidelijkheid met de performance en vooral om wat meer compactibiliteit met andere databases te krijgen (geen idee trouwens of het verder ANSI SQL is maar gezien de omvang van de applicatie is het ook niet zo'n probleem om wat query's langs te lopen, het meeste word toch door Zend opgesteld).
SQL:

1 2 3 4 5 6 7 8 9 10 11

SELECT review.*,      SUM(reviewVotes.vote = -1) neg_votes,      SUM(reviewVotes.vote = 1) pos_votes,      (SUM(reviewVotes.vote = 1) - SUM(reviewVotes.vote = -1)) score FROM review LEFT JOIN (reviewVotes, word_review)      ON( review.reviewId = reviewVotes.reviewId      AND review.reviewId = product_review.reviewId      AND product_review.productId = ?) GROUP BY reviewId ORDER BY score

Zoals je ziet ook meteen met ordening volgens de totale score. Misschien nog even testen of COUNT() sneller is dan SUM want het komt in de huidige constructie op hetzelfde neer, maar het zal wel niet veel opleveren. (In geval van échte performance problemen is het waarschijnlijk toch sneller om de boel wat te denormaliseren en het aantal votes ook bij de reviews zelf op te slaan, maar dit zie ik wel als de applicatie draait.)

Als je rekening wilt houden met andere databases, let er dan ook op dat 'SELECT review.*' in combinatie met een 'GROUP BY reviewid' niet ANSI SQL compatible is: alle velden in je SELECT die niet in je GROUP BY zitten dienen aggregates te zijn (MIN, MAX, SUM, COUNT, etc.). MySQL is bij mijn weten de enige DB die die syntax pikt, maar ook niet kan garanderen dat 't resultaat klopt.

En volgens mij is 'SUM(<column> = <value>)' ook niet echt ANSI SQL, normaal los je dat op in WHERE clauses of in CASE WHEN switches.

Deze heb ik zelf eens gebruikt:
SQL:

1 2 3 4 5 6 7 8

select    rv.reviewId,          ifnull(sum(rv.vote),0) as sumVotes,          ifnull(sum(abs(rv.vote)),0) as totalVotes from reviewVotes rv inner join product_review pr on pr.reviewId = rv.reviewId where pr.productId = ? group by rv.reviewId order by sumVotes desc

het aantal positieve votes is dan: posVotes == (totalVotes + sumVotes) / 2
het aantal negatieve votes is dan: negVotes == posVotes - sumVotes

En in dit geval waarin elke vote even zwaar weegt is sum(abs(rv.vote)) te vervangen door count(*).

quote:

sky- schreef op woensdag 09 juli 2008 @ 15:45:
[...]

En die namen moet je dus niet gebruiken.

Wat een onzin. User is ook een protected keyword in bijvoorbeeld SQL Server, maar ik ga daarvoor m'n User table echt niet renamen naar een random minder verbose andere name. Tis niet alsof het trager is om er even blokhaken cq backticks omheen te zetten (laat staan 80x, waar haal je het vandaan ).

quote:

Afterlife schreef op woensdag 09 juli 2008 @ 20:23:
Als je rekening wilt houden met andere databases, let er dan ook op dat 'SELECT review.*' in combinatie met een 'GROUP BY reviewid' niet ANSI SQL compatible is: alle velden in je SELECT die niet in je GROUP BY zitten dienen aggregates te zijn (MIN, MAX, SUM, COUNT, etc.). MySQL is bij mijn weten de enige DB die die syntax pikt, maar ook niet kan garanderen dat 't resultaat klopt.

Volgens mij voldoet dat al een jaar of 9 aan de ANSI SQL standaard.

De ANSI SQL standaard vereist dat alle velden in de select waar geen groepsfunctie op wordt toegepast functioneel afhankelijk zijn van een veld in de group by.

Alle velden van review.* zijn functioneel afhankelijk van de primary key van review, namelijk reviewid.

Niets mis mee dus.

Als je dat even als waarheid aanneemt moet de database ook de niet geaggregeerde velden uit de row met de betreffende primary key halen. MySQL echter doet documented random data erin frotten.