[SQL / MySQL] Left join met sum erin *

[Verwijderd]

Hallo,

Ik zit met een vraag over een left join. Ik heb de volgende query:

PHP:

SELECT
    V.voorraad,
    A.merk,
    A.type,
    V.variatie
FROM
    TBLartikel A,
    TBLvariatie V
WHERE
    V.voorraad > 0 AND
    V.artikelid = A.artikelid
ORDER BY
    A.merk,
    A.type,
    V.variatie

Deze query haalt een aantal gegevens uit twee tabellen en toont bijvoorbeeld het volgende (merk - type - variatie - voorraad):
PowerCord - Voedingskabel Expert - 3 meter - 5
PowerCord - Voedingskabel Expert - 5 meter - 1
PowerCord - Voedingskabel Premium - 25 meter - 99

Per artikel is er minimaal 1 variatie zoals in bovenstaand overzicht ook te zien is. Er zijn nog twee andere tabellen, TBLbestelling waar alle bestellingen inkomen (NAW gegevens, datum o.a.) en TBLbestelling_artikel waar per bestelling de producten (met het variatie_id) worden ingezet.

Nu wil ik aan de hand van een LEFT JOIN twee stappen ondernemen:
- met een left join TBLbestelling_artikel koppelen en het totaal aantal keer dat een variatie is besteld met een sum() selecteren. Sommige variaties zullen nog nooit besteld zijn en dan zal er een NULL waarde uitkomen.
- in dezelfde left join ook TBLbestelling koppelen om zodoende alleen de bestellingen door te lopen die voldoen aan datum criteria. Aangezien de datum alleen in TBLbestelling en niet in TBLbestelling_artikel staat, dient TBLbestelling ook gekoppeld te worden.

Ik heb nu de volgende query die geen errors geeft, maar die er wel voor zorgt dat de MySQL daemon 100% CPU tijd gaat trekken en na 5 tot 10 minuten nog geen spoor van resultaat geeft zelfs met een LIMIT 1 op de query.

PHP:

SELECT
    V.voorraad,
    A.merk,
    A.type,
    V.variatie,
    sum(BA.aantal)
FROM
    TBLartikel A,
    TBLvariatie V
LEFT JOIN
    TBLbestelling_artikel BA
    ON
        (BA.variatieid = V.variatieid)
WHERE
    V.voorraad > 0 AND
    V.artikelid = A.artikelid AND
    (BA.variatieid > 0 OR BA.variatieid IS NULL)
GROUP BY
    BA.variatieid
ORDER BY
    A.merk,
    A.type,
    V.variatie

Heeft iemand een idee of ik uberhaupt een sum() kan doen op een tabel die ik via een left join heb toegevoegd? Of iemand die een idee heeft wat ik in godsnaam fout doe?

edit:
De group by stond per ongeluk op aantal, dit moet uiteraard variatieid zijn.

[ Voor 5% gewijzigd door Verwijderd op 03-02-2005 10:00 . Reden: Er staat een fout in de topictitel, kan een modje dat veranderen? Count moet Sum zijn.. ]

[NMe]

P&W FAQ - Hoe werkt dat GROUP BY nou eigenlijk?

[Verwijderd]

Bedankt voor de link, maar dat lost mijn probleem niet op. Ik had een klein foutje staan in het voorbeeld in mijn openingspost, overtikken is best moeilijk

Het werkt dus niet, heeft iemand anders nog een idee?

[NMe]

edit:
De group by stond per ongeluk op aantal, dit moet uiteraard variatieid zijn.

Ik ben bang dat je die link niet helemaal goed bekeken hebt. Je moet groeperen op elk veld dat je in je select-lijst staat, behalve velden die vloeien uit aggregate functies. In jouw geval dus:

code:

GROUP BY
    V.voorraad, 
    A.merk, 
    A.type, 
    V.variatie

[curry684]

-NMe- schreef op donderdag 03 februari 2005 @ 10:25:
[...]

Ik ben bang dat je die link niet helemaal goed bekeken hebt.

* curry684 wijst vooral op het stukje:

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

Dat heb ik niet voor niets zo lomp geschreven

[Verwijderd]

Oke mijn fout, die group by klopte niet helemaal. Shame on me
Ik voer de query nu uit op een dedicated hosting server (dual cpu volgens mij) en dan duurt de query zo'n 5 minuten. Het probleem is denk ik dat er zo'n 3000 tot 6000 variaties zijn, 30.000 bestellingen en minimaal iets van 80.000 records in TBLbestelling_artikel.

Een workaround zou zijn om TBLbestelling en TBLbestelling_artikel uit de query te halen, dan hou ik dus de originele query over. Als ik dan in mijn PHP script door het result heenloop kan ik voor alle variaties apart een query doen in TBLbestelling en TBLbestelling_artikel. Zou het sneller zijn om zo'n 3.000 queries los te laten ipv een ingewikkelde query met join?

[Verwijderd]

curry684 schreef op donderdag 03 februari 2005 @ 10:39:
[...]

* curry684 wijst vooral op het stukje:
[...]

Dat heb ik niet voor niets zo lomp geschreven

Dat snap ik, maar dat is ook weer afhankelijk van de query. In mijn originele query krijg ik al unieke resultaten terug en geen dubbele rijen. Als ik dan een group by uitvoer op een tabel die ik via een left join erbij voeg, hoeft hij alleen in die tabel te groupen.

Of denk ik nou krom?

[NMe]

Verwijderd schreef op donderdag 03 februari 2005 @ 11:58:
Of denk ik nou krom?

Ja.

Een group by heeft gewoon geen betekenis als je niet op alle velden groepeert die geen aggregate functie zijn. Om te beginnen betekent die SUM namelijk niets. Waar is het een som van? Welke velden moet ie nemen? Welke niet? MySQL ziet dat niet.

Zou het sneller zijn om zo'n 3.000 queries los te laten ipv een ingewikkelde query met join?

Normaal niet, lijkt me. Maar je zou het even kunnen uitproberen en benchmarken. Een join zou in ieder geval makkelijkere code opleveren.

Gaat de aangepaste query eigenlijk nog steeds traag?

[curry684]

Mag ik vragen op welke velden je indexen liggen?

[Verwijderd]

-NMe- schreef op donderdag 03 februari 2005 @ 14:20:
[...]
Normaal niet, lijkt me. Maar je zou het even kunnen uitproberen en benchmarken. Een join zou in ieder geval makkelijkere code opleveren.

Gaat de aangepaste query eigenlijk nog steeds traag?

De aangepaste query gaat nog steeds traag ja, daarom heb ik gekeken hoe lang het duurde om zo'n 3000 queries uit te laten voeren. Dat bleek sneller te zijn dan die join en dus laat ik het voorlopig wel zo.

Het probleem van die database is dat hij zo'n 4 a 5 jaar oud en totaal niet geoptimaliseerd is. Indexen zitten alleen standaard op de id velden (in de meeste gevallen onzinnig) en dus zal MySQL niet bepaald snel door die database heensurfen.

Gelukkig zijn we al bezig met het plannen van een complete remake, incl innodb tables, goede indexen en betere queries

curry684 schreef op donderdag 03 februari 2005 @ 14:23:
Mag ik vragen op welke velden je indexen liggen?

Zoals hierboven al gezegd, op zo'n beetje geen enkele tabel zit een goede index. Dat is dan ook gelijk het grootste snelheidsprobleem bij de queries.

Bedankt voor alle hulp trouwens!

[ Voor 18% gewijzigd door Verwijderd op 04-02-2005 13:05 ]