COUNT funksjon i LEFT JOIN gir produktet av countene...

[Jonhoo]

Heisann folkens =)

 

Har støtt på et merkelig problem med MySQL her..

Jeg har tre tabeller: Den ene inneholder informasjon om brukere, den andre inneholder forskjellige typer innhold som brukerene har lagt til i forskjellige events, og den tredje inneholder innhold som brukerene har lagt til i clubs.

 

Jeg ønsker å hente ut litt info om brukerene, samt antall elementer de har lagt til i hver av de to siste tabellene, og prøver å gjøre det med følgende query:

SELECT
   users.user_id,
   users.username,
   users.posts,
   users.date_added AS `time`,
   COUNT(eR.itemId) AS eventsR,
   COUNT(cR.itemId) AS clubsR
FROM `internat_phorum`.`phorum_users` users
   LEFT JOIN events_related eR ON (eR.itemAuthor = users.user_id)
   LEFT JOIN clubs_related cR ON (cR.itemAuthor = users.user_id)
GROUP BY users.user_id;

 

Det rare med resultatet av denne queryen er at jeg får tilbake både eventsR og clubsR lik produktet av antallet elementer brukeren har i de to tabellene events_related og clubs_related... Altså, hvis en bruker har 10 elementer i clubs_related, og 5 elementer i events_related, så vil både eventsR og clubsR returneres som 50...

Dersom en av dem er 0, så blir riktig tall returnert i den andre.

 

Er det noen som kan se hva som forårsaker denne merkelige feilen?

 

Håper at noen kan mer om MySQL enn det jeg kan her ^^

Jon(hoo)

[Kul drittunge]

Eh, ja, du joiner.

 

Du får da det kartesiske produktet, eller kombinasjonen av radene, som du teller.

 

Du må nok telle hver ting for seg.

[Jonhoo]

Joda, skjønner at jeg får kombinasjonen, men jeg trodde at den fortsatt separerte kolonnene som hører til en tabell fra de som hører til en annen?

Er det ingen mulighet for å hente ut antallet rader fra hver tabell i en query?

[Kul drittunge]

Hum, trodde dette var enklere enn det var.

For at det skal være kompatibelt med MySQL, blir det litt plunder.

 

Dette er nå uansett et forslag:

 

select u.*
, sub.*
from
users u
, (
	select uid, count (*) as n, 'foo' as src
	from foo
		, (
			select u.userid
			from tblUser u
		) as sub
	where 0 = 0
		and uid = sub.userid
	group by uid

	union all

	select uid, count (*) as n, 'bar' as src
	from bar
		, (
			select u.userid
			from tblUser u
		) as sub
	where 0 = 0
		and uid = sub.userid
	group by uid
) as sub
where 0 = 0
and sub.uid = u.userid

 

Eventuelt kan du sette paranteser rundt hver del av union, og kalle resultatsettet ett eller annet, og så gi count fra hvert av disse resultatsettene et eget navn.

Endret 23. april 2008 av zY8pKPhR8XLJ

[Jonhoo]

Oi! For en spørring...

Greia er at jeg skal sortere etter summen av de to countene, det er derfor jeg trenger dem i en rad... Rart at dette skal være så komplisert?

 

Er det virkelig ikke mulighet for å gjøre det lettere?

Takk uansett ihvertfall =)

Endret 23. april 2008 av Jonhoo

[Kul drittunge]

Nå har jo ikke jeg tabellene dine, men jeg forestiller meg at dette skal funke:

select users.user_id
, users.username
, users.posts
, users.date_added AS `time`
, eR.n as eventsR
, cR.n as clubsR
FROM `internat_phorum`.`phorum_users` as users
, (
	select eR.itemAuthor
		, count (*) as n
	from events_related eR
		, (
			select users.user_id
			from `internat_phorum`.`phorum_users` as users
		) as events
	where 0 = 0
		and eR.itemAuthor = events.user_id 
	group by eR.itemAuthor 
) as eR
, (
	select cR.itemAuthor
		, count (*) as n
	from clubs_related cR
		, (
			select users.user_id
			from `internat_phorum`.`phorum_users` as users
		) as events
	where 0 = 0
		and cR.itemAuthor = events.user_id 
	group by cR.itemAuthor 
) as cR		
where 0 = 0
and eR.itemAuthor = users.user_id			
and cR.itemAuthor = users.user_id

[Jonhoo]

Uff.. Stygg query, men jaja.. Må kanskje bli sånn..

 

 

Tusen takk for hjelpen ihvertfall =)

 

BTW: Hvorfor bruker du 0=0 AND?

[Kul drittunge]

Fordi:

1. Da står alle betingelsene greit oppstillt

2. Jeg slipper maset med at hvis jeg må utkommentere den første betingelsen så må jeg trekke fra and / or på den første neste betingelsen i tillegg

3. Jeg kan enkelt kortslutte det hele hvis det er en subquery, ved å si 0 = 1

[Jonhoo]

Ah, smart..

 

Ville du forresten si at dette er en query som er tung for MySQL å kjøre? Bare lurer på om jeg bør cache den siden hvor resultatet skal printes ut (skal brukes til en liste over de brukerene som har lagt ut mest innhold)..

 

Ville det forresten hjulpet noe på kompleksiteten til queryen dersom kolonnene events_related og clubs_related hadde forskjellige kolonnenavn? Nå har begge kolonnene itemAuthor, itemId, itemValue etc...

Endret 24. april 2008 av Jonhoo

[Kul drittunge]

Ville du forresten si at dette er en query som er tung for MySQL å kjøre? Bare lurer på om jeg bør cache den siden hvor resultatet skal printes ut (skal brukes til en liste over de brukerene som har lagt ut mest innhold)..

 

Ville det forresten hjulpet noe på kompleksiteten til queryen dersom kolonnene events_related og clubs_related hadde forskjellige kolonnenavn? Nå har begge kolonnene itemAuthor, itemId, itemValue etc...

 

Nei, kan ikke se hvorfor det skulle være det. Nå kjenner ikke jeg noe særlig til MySQL, men det er antagelig en primary key på disse nøklene. Jeg forutsetter også at du har referanseintegriteten i behold, og uthenting av data burde derfor være kjapt.

Men dette er jo noe du kan måle selv, med select explain eller noe.

 

For å tenke litt høyt:

Hvis data ikke har endret seg, så vil enhver databasemotor med respekt for seg selv (uvisst om MySQL har det), cache spørringer.

På applikasjonsnivå vil jeg anta at du kan sette caching for hver enkelt query, gjerne definert i forhold til hyppigheten for endring av data, og din interesse av å få returnert til enhver tid kurrant data som kan ha endret seg, som "volatile", "frequent", "often", "seldom" etc..

Det avhenger av bruken for ting og tang, og hva du selv synser om. Skader ikke å cache ikke-interaktiv data i fem minutter.

 

Den siste delen av spørsmålene dine har igrunnen ingen mening, da en databasemotor (eller for den saks skyld, enhver kompilator) ikke forholder seg til navnene på ting, men har en oppslagstabell som returnerer id'er (eller pekere) til felter i tabeller. Du kan kalle ting nøyaktig hva du vil; det spiller overhodet ingen trille.

[kaffenils]

For å tenke litt høyt:

Hvis data ikke har endret seg, så vil enhver databasemotor med respekt for seg selv (uvisst om MySQL har det), cache spørringer.

 

Denne skjønte jeg ikke helt. Mener du at f.eks. SQL Server skal cache resultatet av en spesifikk spørring slik at hvis data ikke har endret seg og samme spørring kjøres igjen så hentes data fra denne spesifikke cachen? Jeg har aldri hørt at SQL Server har noe slikt, og jeg skal spise tøflene mine hvis det viser seg at det faktisk finnes.

Hvis det er procedure cache du mener så er ikke dens levetid basert på om data endres eller ikke, men av lazywriter som bruker er form for LRU algoritme for å vedlikeholde denne cachen.

 

Jeg vet at MySQL har noe slikt tull, mne jeg kan ikke skjønne hvilken praktisk nytte dette har.

[Kul drittunge]

Denne skjønte jeg ikke helt. Mener du at f.eks. SQL Server skal cache resultatet av en spesifikk spørring slik at hvis data ikke har endret seg og samme spørring kjøres igjen så hentes data fra denne spesifikke cachen?

Jau, og det er såre enkelt å ta en hash av spørringsstrengen og slå det hele opp mot en oppslagstabell inneholdende non-dirty oppføringer av tidligere spørringer.

 

Jeg har aldri hørt at SQL Server har noe slikt, og jeg skal spise tøflene mine hvis det viser seg at det faktisk finnes.

 

Jeg vet at MySQL har noe slikt tull, mne jeg kan ikke skjønne hvilken praktisk nytte dette har.

 

Tja, det kan riktignok være sunt med fiber i kosten, men elementært i ytelsestesting er dropcleanbuffers og freeproccache.

 

Du kan jo tenke deg selv hvilken nytte dette har, og for å gjøre det enkelt å se, la oss si at du har en read-only database hvor en spesifikk spørring tar 1 sekund.

Hvorfor skulle ikke en databasemotor cache resultatsettet? Det er jo det som er grunnen til at man putter så mye RAM man bare kan i en databaseserver.

 

Bemerk at vi her snakker om deterministiske spørringer - og at hele kluet med ordet deterministisk er å skille mellom ting som kan gi samme resultat når man gjør det om igjen, og ting som ikke gjør det.

Endret 27. april 2008 av zY8pKPhR8XLJ

[kaffenils]

Jau, og det er såre enkelt å ta en hash av spørringsstrengen og slå det hele opp mot en oppslagstabell inneholdende non-dirty oppføringer av tidligere spørringer.

 

For å unngå misforståelser. Du påstår altså at SQL Server cacher resultatet av hver spesifikke spørring? Og vi snakker ikke om Buffer Pool som cacher pages, eller Procedure Cache som cacher execution plans, men en egen cache som cacher det nøyaktige resultatet av en spørring? Så hvis jeg skriver select * from Employees where Lastname='Smith' så vil resultatet caches og gjenbrukes hvis jeg kjører nøyaktig samme spørring en gang til og data ikke er endret i mellomtiden? Du kødder nå, ikke sant? Og hvis ikke så er du hjertelig velkommen til å sende meg en link til msdn eller en annen seriøs side som beskriver denne oppførselen. Til gjengjeld skal jeg poste et bilde hvor jeg spiser tøffelen min.

 

Tja, det kan riktignok være sunt med fiber i kosten, men elementært i ytelsestesting er dropcleanbuffers og freeproccache.

 

Du kan jo tenke deg selv hvilken nytte dette har, og for å gjøre det enkelt å se, la oss si at du har en read-only database hvor en spesifikk spørring tar 1 sekund.

Hvorfor skulle ikke en databasemotor cache resultatsettet? Det er jo det som er grunnen til at man putter så mye RAM man bare kan i en databaseserver.

 

Bemerk at vi her snakker om deterministiske spørringer - og at hele kluet med ordet deterministisk er å skille mellom ting som kan gi samme resultat når man gjør det om igjen, og ting som ikke gjør det.

 

Enter har jeg misforstått noe radikalt eller så har du det (og jeg tipper det er du ). Du putter masse minne i serveren for at tabellenes data pages skal caches i buffer pool slik at du ikke trenger å lese fra disk hver gang en spørring trenger en page fra en index. dropcleanbuffers og freeproccache tømmer henholdsvis ikke-dirty sider (derav navnet dropcleanbuffer) fra buffer pool og procedure cache.

 

Mao. så har SQL Server oversikt over hvilke sider som er lastet og hvilke som ikke er lastet. Hvis siden en spørring trenger finnes i buffer pool så leses den derfra, uansett of den er dirty eller clean, hvis ikke så leses den fra disk og caches i buffer pool slik at den kan aksesseres raskere når det er bevhov for den igjen. Alle sider som en spørring trenger for å bygge opp resultatet må lages i buffer pool. Mengden med minne du har tilgjengelig for SQL Server er avgjørende hvor LENGE en side kan ligge laget i buffer pool.

Endret 27. april 2008 av kaffenils

[Kul drittunge]

For å unngå misforståelser. Du påstår altså at SQL Server cacher resultatet av hver spesifikke spørring? Og vi snakker ikke om Buffer Pool som cacher pages, eller Procedure Cache som cacher execution plans, men en egen cache som cacher det nøyaktige resultatet av en spørring? Så hvis jeg skriver select * from Employees where Lastname='Smith' så vil resultatet caches og gjenbrukes hvis jeg kjører nøyaktig samme spørring en gang til og data ikke er endret i mellomtiden? Du kødder nå, ikke sant?

 

Nei.

 

Jeg kan gi deg en mer utfyllende forklaring imorgen, hvis jeg har tid til det, men dette dreier seg ikke om tøffellspissing eller hvilket hjørne man velger å male seg opp i.

 

Egentlig, så må jeg si at det burde være selvinnlysenda at all den tid disk-accsess er tre størrelsesordener (1000x) tregere enn minne access, så burde begrepet rundt at en databasemotor ønsket å cache sine resultat-recordsett der det var mulig, være klart.

 

Er faktisk litt skuffet, kaffenils, over at du ikke har forståelse for slike grunnleggende begreper.

[j000rn]

Er faktisk litt skuffet, kaffenils, over at du ikke har forståelse for slike grunnleggende begreper.

 

Jeg må si meg enig med kaffenils, ihvertfall når det gjelder SQL Server. SQL Server cacher dataene i databasen, men den cacher ikke selve resultatsettet.

 

Å cache resultatsettet er det applikasjonene selv som må gjøre.

 

Synd egentlig...for jeg ville gjerne se kaffenils spise tøflene sine... :!:

Endret 27. april 2008 av jorn79

[blackbrrd]

zY8pKPhR8XLJ: Det er en forskjell på å cache de mest brukte delene av databasen og å cache resultatet av spesifike spørringer. Førstnevnte er en helt generisk måte å cache data på for å unngå å måtte lese fra disk, mens sistnevnte brukes for å senke cpu loaden på databaseserveren på bekostning av mer ram bruk.

 

Drifter til daglig en database på 50gb med et par tusen brukere og den har definitivt ikke noe problem med cpu loaden, men den har til tider hatt for lite minne (som er blitt oppgradert underveis). Minne har vært en ressurs det har vært større mangel på enn cpu tid.

Endret 27. april 2008 av blackbrrd

[Kul drittunge]

Ah, javel javel.

 

Da ser det ut til at det er jeg som har misforstått. Kikker litt nærmere på det ikveld.

 

Når jeg tenker meg om så har nok kaffenils helt rett. All den tid serveren til vanlig er bundet på io og ikke cpu, så er det mer hensiktsmessig (og enklere) å cache sidene enn resultatene.

Jeg forvekslet da data-cachen med spørringscachen.

Endret 28. april 2008 av zY8pKPhR8XLJ

[kaffenils]

Synd egentlig...for jeg ville gjerne se kaffenils spise tøflene sine... :!:

 

Det kan jeg tenke meg du ville likt, din sadisk

Men det er jo enda håp for dine sadistpervertittiske fantasier hvis zY8pKPhR8XLJ klarer å komme opp med dokumentasjon på at SQL Server har et "query result cache".

[Manfred]

Venter i spenning. Det er for lite tøffelspising her inne hehe..

 

zY8pKPhR8XLJ har forresten et nick som er enkelt og greit huske. Lurer på hvor idéen kom fra

[j000rn]

Venter i spenning. Det er for lite tøffelspising her inne hehe..

 

zY8pKPhR8XLJ har forresten et nick som er enkelt og greit huske. Lurer på hvor idéen kom fra

 

Han bomma på username / password feltene i registreringsbildet, så passordet hans er sikkert Knut eller noe sånt...