[MySQL] Onverklaarbaar traag en onjuist resultaat

[StephanVierkant]

Ik ben geen expert op het gebied van SQL en ben nu aan het experimenteren met wat queries. Ik heb twee werkende queries gedraaid met een join, maar een combinatie van deze queries maakt hem traag en ik krijg een onverklaarbaar resultaat.
Nummer 1:

SQL:

 SELECT username, count( stephan_fotos.id ) AS aantal_fotos
FROM stephan_profielen
LEFT JOIN stephan_fotos ON stephan_fotos.userid = stephan_profielen.id
GROUP BY stephan_profielen.id
LIMIT 0 , 30

Deze duurde 0.0111 sec en gaf netjes het resultaat: het aantal geuploade foto's per gebruiker.


Nummer 2:

SQL:

SELECT username, count( stephan_fotos_votes.id ) AS aantal
FROM stephan_profielen
LEFT JOIN stephan_fotos_votes ON stephan_fotos_votes.userid = stephan_profielen.id
GROUP BY stephan_profielen.id
LIMIT 0 , 30 

Deze duurde 0.0052 sec en gaf netjes het resultaat: het aantal stemmen per gebruiker.

Nummer 3:

SQL:

SELECT username,
 count(stephan_fotos_votes.id) AS aantal_stemmen,
 count(stephan_fotos.id) AS aantal_fotos
FROM stephan_profielen
    LEFT JOIN stephan_fotos ON stephan_fotos.userid = stephan_profielen.id
    LEFT JOIN stephan_fotos_votes ON stephan_fotos_votes.userid = stephan_profielen.id
GROUP BY stephan_profielen.id
LIMIT 0 , 30 

Pas na 3.9845 sec gaf deze resultaat, en er klopt geen reet van. Ik zou 1440936 foto's hebben geplaatst, en evenveel stemmen. In werkelijkheid zijn het 953 foto's, en 1512 stemmen. Dit keer elkaar is inderdaad 1440936, maar waarom dat werkt weet ik niet. Wie helpt mij verder?

[StephanVierkant]

Dankzij dit topic heb ik nu een goed resultaat. DISTINCT toevoegen werkt.

De query is echter nog steeds erg traag. Meer dan 5 sec. doet 'ie erover. Aangezien ik nog meer joins wil maken (die ik voor het gemak even weglaat) in deze query, is zo'n tijd natuurlijk niet te doen.

Kan iemand mij nóg verder helpen?

SQL:

SELECT username,
 count(DISTINCT stephan_fotos_votes.id) AS aantal_stemmen,
 count(DISTINCT stephan_fotos.id) AS aantal_fotos
FROM stephan_profielen
    LEFT JOIN stephan_fotos ON stephan_fotos.userid = stephan_profielen.id
    LEFT JOIN stephan_fotos_votes ON stephan_fotos_votes.userid = stephan_profielen.id
GROUP BY stephan_profielen.id
LIMIT 0 , 30

[ Voor 37% gewijzigd door StephanVierkant op 12-04-2008 14:28 . Reden: Voor de volledigheid nieuwe query toegevoegd ]

[Verwijderd]

Goede indexen toevoegen kan een hoop schelen, en kijk met EXPLAIN 's wat MySQL ervan bakt.

[CyBeRSPiN]

Misschien een index op fotos.userid en fotos_votes.userid zetten? Dat wil soms nogal helpen.
Ondersteunt MySQL ook EXPLAIN ANALYZE? Zo ja, zet dat eens voor je query in de console, dan geeft het DBMS netjes aan welke stappen ondernomen worden.
Een sequential scan over een grote tabel wil je zoveel mogelijk vermijden, en dat kan mbv indexes.

edit: doh, te lang gewacht met posten

[ Voor 6% gewijzigd door CyBeRSPiN op 31-03-2008 01:19 ]

[StephanVierkant]

@Afterlife & CyBeRSPiN:

Dank voor jullie antwoord. Ik heb inderdaad op beide fotos.userid of fotos_votes.userid een INDEX staan.

Analyze kent 'ie overigens niet.

Een trage query snap ik, maar hoe kan het dat het pas traag wordt, als ik twee queries combineer?

[GlowMouse]

Stephan4kant schreef op maandag 31 maart 2008 @ 01:25:
Een trage query snap ik, maar hoe kan het dat het pas traag wordt, als ik twee queries combineer?

Je weet dat veel MySQL versies maar één index per table per query kunnen gebruiken?

[StephanVierkant]

GlowMouse schreef op maandag 31 maart 2008 @ 01:32:
[...]

Je weet dat veel MySQL versies maar één index per table per query kunnen gebruiken?

Mijn naam is Haas. Versie 4.1.21-standard draai ik.
Het werkt trouwens wel, maar erg traag.

[StephanVierkant]

Ik heb de index beetje heen en weer geschoven, maar mocht allemaal niet baten. Tijd blijft steeds tussen 4 en 13 seconden. Noem het progressie..

offtopic:
eerst maar even lekker slapen...

[ Voor 3% gewijzigd door StephanVierkant op 12-04-2008 14:28 ]

[Verwijderd]

Ben je geïnteresseerd in wie welke foto's niet heeft gezien, of wie op welke foto niet heeft gestemd? Zo nee, waarom dan een LEFT JOIN, zo ja, waarom?

En hopelijk begrijp je dat bij een left join indexen een stuk minder winst geven, omdat de DBMS sowieso alle rijen langs moet om alle waarden op te halen, waar bij een inner join de DBMS alleen die waarden hoeft op te halen waarvan hij de informatie echt nodig heeft.

Maar begin eens met een EXPLAIN query. Dan zie je dus welke indexen gebruikt worden.

[StephanVierkant]

Explain:

+---+-------------+---------------------+-------+---------------+---------+---------+--------------------------------------+------+
|id | select_type | table               | type  | possible_keys | key     | key_len | ref                                  | rows |
+---+-------------+---------------------+-------+---------------+---------+---------+--------------------------------------+------+
| 1 | SIMPLE      | stephan_profielen 	| index | NULL          | PRIMARY | 4       | NULL                                 | 218  |
| 1 | SIMPLE      | stephan_fotos 	| ref   | userid        | userid  | 4       | stephan_stephan.stephan_profielen.id | 171  |
| 1 | SIMPLE      | stephan_fotos_votes | ref   | userid        | userid  | 4       | stephan_stephan.stephan_profielen.id | 24   |
+---+-------------+---------------------+-------+---------------+---------+---------+--------------------------------------+------+

Ik kom hier helaas zelf niet verder mee. Met een inner join slaat 'ie users over (die geen foto's hebben geplaatst) en dat wil ik niet. Ik heb de FAQ al doorgelezen, maar weet nog niet welke join ik met hebben.

[Voutloos]

Doe die query eens zonder de count en group by en je ziet vanzelf het foute gedrag?

[StephanVierkant]

Voutloos schreef op maandag 31 maart 2008 @ 09:40:
Doe die query eens zonder de count en group by en je ziet vanzelf het foute gedrag?

Ik krijg dat 30 keer de gebruikersnaam van user 1.

[simon]

Is je MySQL installed niet gewoon borked? Zo klinkt 't een beetje.

[Voutloos]

Stephan4kant schreef op maandag 31 maart 2008 @ 09:48:
[...]
Ik krijg dat 30 keer de gebruikersnaam van user 1.

Kijk eens wat verder dan dat... Je krijgt gewoon aantal votes keer een bepaalde username, foto id combinatie.

Hopelijk snap je nu waarom die distinct nodig is en waarom je eerst op votes*fotos aantallen kwam (cartesisch product).

simon schreef op maandag 31 maart 2008 @ 09:52:
Is je MySQL installed niet gewoon borked? Zo klinkt 't een beetje.

Niets mis mee, alhoewel het wel een update kan gebruiken.

Poster onder me noemt het ook, op basis van de in de startpost genoemde getallen moet je gewoon binnen 5 seconden denken 'o, fok, een cartesisch product'. En in > 99% vd topics hier moet je uitgaan van een fout in de query ipv een fout in mysql.

edit:
@urk_forever: die tijd heb je dan verspild, want je hebt het niet juist gespeld.

[ Voor 21% gewijzigd door Voutloos op 31-03-2008 09:59 ]

[urk_forever]

Waar je tegen aan loopt heet het carthetisch product. Door de beide joins wordt je resultaat aantal rijen tabelx * aantal rijen tabely groot en krijg je daar het resultaat van. Dat wil je niet.

Waarom gebruik je niet beide queries apart??

Potver, net te laat was even aan het zoeken naar de spelling van carthetisch

[ Voor 15% gewijzigd door urk_forever op 31-03-2008 09:56 ]

[StephanVierkant]

Dat de fout niet in MySQL, maar in MijnSql zat, dat had ik al begrepen. Meerdere queries draaien is natuurlijk een mogelijkheid (dit is de huidige situatie), maar nog geen oplossing.

Ik begrijp dat DINSTINCT symptoombestrijding is, en er beter uit kan? Ik ben er nu nog niet achter hoe ik mijn query kan verbeteren.

[Confusion]

Stephan4kant schreef op maandag 31 maart 2008 @ 00:15:
Deze duurde 0.0111 sec en gaf netjes het resultaat: het aantal geuploade foto's per gebruiker.

Maar dat is meer geluk dan wijsheid, want die query zou op andere database systemen dan MySql een foutmelding opleveren. Daar hoort een 'GROUP BY username' te staan. Voor je andere queries geldt hetzelfde. De resultaten die je nu krijgt horen onbepaald/onvoorspelbaar te zijn, aangezien je query onvolledig specificeert wat er uit moet komen.

[Voutloos]

Nee, hoewel het een fout is dat niet dezelfde fout. Netjes met group by omgaan (enkel kolommen waarop gegroepeerd is of welke resultaat zijn van een aggragate function selecteren) voorkomt nog geen cartesisch product hier.

_{En aangezien id de primary key is en username uit die tabel komt, heb je hier nog geen last van random data.}

[StephanVierkant]

Confusion schreef op maandag 31 maart 2008 @ 10:10:
[...]

Maar dat is meer geluk dan wijsheid, want die query zou op andere database systemen dan MySql een foutmelding opleveren. Daar hoort een 'GROUP BY username' te staan. Voor je andere queries geldt hetzelfde. De resultaten die je nu krijgt horen onbepaald/onvoorspelbaar te zijn, aangezien je query onvolledig specificeert wat er uit moet komen.

SQL:

 SELECT username, count( stephan_fotos_votes.id ) AS aantal_stemmen, count( stephan_fotos.id ) AS aantal_fotos
FROM stephan_profielen
LEFT JOIN stephan_fotos ON stephan_fotos.userid = stephan_profielen.id
LEFT JOIN stephan_fotos_votes ON stephan_fotos_votes.userid = stephan_profielen.id
GROUP BY username
ORDER BY `stephan_profielen`.`username` DESC
LIMIT 0 , 30 

Dit werkt wel (0.0031 sec), ik krijg ook een goed resultaat. Ik wil echter de tabel sorteren op profielen.id en de id ook meegeven. Hoe los ik dit op?

[ACM]

Je kan sowieso kijken of je een gecombineerde index op (userid, id) kan maken bij die twee tabellen en beoordelen of dat sneller is.

Ik zie zo gauw iig twee mogelijkheden om je query om te zetten naar een variant met subqueries, of die efficienter zijn weet ik niet (met MySQL 5.0 zouden ze sowieso al beter gaan dan MySQL 4.1):

SQL:

SELECT p.id, username,
 (SELECT COUNT(*) FROM stephan_fotos_votes v WHERE v.userid = p.id) AS aantal_stemmen,
 (SELECT COUNT(*) FROM stephan_fotos f WHERE f.userid = p.id) as aantal_fotos
FROM stephan_profielen p
ORDER BY p.id
LIMIT 0 , 30

SQL:

SELECT p.id, username,
 v.aantal_stemmen,
 f.aantal_fotos
FROM stephan_profielen p
    LEFT JOIN (SELECT userid, COUNT(*) as aantal_fotos FROM stephan_fotos GROUP BY userid)
            AS f ON  f.userid = p.id
    LEFT JOIN (SELECT userid, COUNT(*) as aantal_stemmen FROM stephan_fotos_votes GROUP  BY userid)
            AS v ON v.userid = p.id
ORDER BY p.id
LIMIT 0 , 30

Deze tweede query kan je ook aanpakken door twee temporary tables te maken waar je de tussenresultaten in opvangt (vergeet niet er een index op userid bij te zetten als je veel users hebt) en waarmee je de uiteindelijke query een stuk sneller mee kan maken, wat zeker voor MySQL 4.1 aardig kan schelen.

[ Voor 3% gewijzigd door ACM op 31-03-2008 10:24 ]

[StephanVierkant]

ACM, mijn dank is groot. Je eerst oplossing is het snelste, en geeft ook '0' terug als er geen stemmen zijn, in tegenstelling tot de 2e die 'NULL' aangeeft. Hulde!

[ACM]

Stephan4kant schreef op maandag 31 maart 2008 @ 10:29:
in tegenstelling tot de 2e die 'NULL' aangeeft. Hulde!

Dat is natuurlijk nog wel met COALESCE(v.aantal_stemmen, 0) op te lossen

[curry684]

***HINT***.