[PHP/MySQL] Join optimaliseren en uitlezen met PHP

[2]

Ik heb deze database structuur voor een site:

PRODUCTEN
id
titel
omschrijving

SUBCATS
id
product_id
titel
omschrijving

PRIJZEN
id
subcats_id
aantal
prijs


Hoop dat het een beetje duidelijk is hoe de tabellen samenhangen wat betreft foreign keys:

SUBCATS.product_id -> PRODUCTEN.id
PRIJZEN.subcats_id -> SUBCATS.id


Het gaat dit om dit soort producten:

code:

Product

    subcategorienaam1  aantal1
                 aantal2
                 aantal3

    subcategorienaam2  aantal1
                 aantal2
                 aantal3

    subcategorienaam3  aantal1
                 aantal2
                 aantal3

Ok hopelijk is dit een beetje duidelijk.

Wat ik dus wil, is dmv een join met 1 query alle info ophalen van product èn alle subcategorien mèt de daarbij behorende aantallen/prijzen. Dit is wat ik heb:

code:

  "SELECT
     A.titel,
     A.omschrijving,
     B.id,
     B.titel,
     B.info,
     C.id,
     C.aantal,
     C.prijs
   FROM
     prod_gg A,
     subcat B,
     prijzen C
   WHERE
     C.subcat_id = B.id AND B.prod_id = A.id AND A.id = '$id'";

Dat werkt in principe prima, alleen vraag ik me af of dit efficienter kan. Ik ben nogal een n00b op het gebied van joins, maar volgens mij krijg ik nu veel meer output dan ik nodig heb. Want nu staat toch in iedere row weer bijvoorbeeld de titel van het product en de omschrijving en de bijzonderheden + van de subcategorie de titel en de omschrijving etc.

code:

+=====+=====+===+=====+=====+===+=====+=====+
| foo | bar | 1 | baz | bo1 | 1 | 100 | 200 |
+-----+-----+---+-----+-----+---+-----+-----+
| foo | bar | 1 | baz | bo2 | 2 | 200 | 400 |
+-----+-----+---+-----+-----+---+-----+-----+
| foo | bar | 1 | baz | bo3 | 3 | 300 | 600 |
+-----+-----+---+-----+-----+---+-----+-----+
| foo | bar | 2 | bay | bo4 | 4 | 100 | 300 |
+-----+-----+---+-----+-----+---+-----+-----+
| foo | bar | 2 | bay | bo5 | 5 | 200 | 600 |
+-----+-----+---+-----+-----+---+-----+-----+
| foo | bar | 2 | bay | bo6 | 6 | 300 | 900 |
+-----+-----+---+-----+-----+---+-----+-----+
| foo | bar | 3 | bax | bo7 | 7 | 100 | 400 |
+-----+-----+---+-----+-----+---+-----+-----+
| foo | bar | 3 | bax | bo8 | 8 | 200 | 800 |
+-----+-----+---+-----+-----+---+-----+-----+
| foo | bar | 3 | bax | bo9 | 9 | 300 | 1200|
+=====+=====+===+=====+=====+===+=====+=====+

En dat terwijl ik zoiets als de titel van het product maar 1x hoef te weten. Vandaar dat ik me afvraag of het efficienter kan, en of mijn query een beetje handig is.

En op welke manier zou ik dit nou het snelst uit kunnen lezen in PHP?

Ik hoop dat het allemaal een beetje duidelijk is, thnx in advance.

[D2k]

ik ga mezelf ff quoten

Op zaterdag 03 november 2001 17:37 schreef D2k het volgende:
zoals ik het laatste ook aan een n00b in php heb uitgelegd (en wat ACM ook duidelijk probeert te maken)

analyse is het belangijkste
als je niet weet hoe je iets moet maken heeft de code geen nut.
coden is het eenvoudige deel van programmeren
typen kan iedereen en de syntax is ook niet moeilijk.
maar de analyse dus eeeeerst doorkrijgen wat je wil en welke stappen dat je er voor moet doorlopen moet je het coden maar uit je hoofd zetten.
Dus beschrijf nu gewoon eens op papier (ja met een pen) hoe je dit probleem kan oplossen
en ga het dan omzetten in code
dus niet eerst coden en dan denken hoe zal ik het eens gaan doen

tot zover
D2k teaches programming part 1

[2]

Thnx, ik had het al gelezen in een ander topic. Maar eerlijk gezegd is dat iets wat mij al een tijdje duidelijk is. Het punt is juist dat ik goed weet wat ik wil, en het in principe ook werkende heb, maar dat ik het wel belangrijk vind dat het zo efficient mogelijk gebeurt allemaal. Vandaar dit topic.

[D2k]

Op dinsdag 06 november 2001 12:47 schreef 2 het volgende:
Thnx, ik had het al gelezen in een ander topic. Maar eerlijk gezegd is dat iets wat mij al een tijdje duidelijk is. Het punt is juist dat ik goed weet wat ik wil, en het in principe ook werkende heb, maar dat ik het wel belangrijk vind dat het zo efficient mogelijk gebeurt allemaal. Vandaar dit topic.

k
dan zal je iets van benchmarks moeten doen
neem gewoon 2 mogelijk heden en 10000 keer en time ze gewoon

[2]

Eh,

Ik wil dus graag weten of de methode die ik gebruik goed is, oftewel hoe het eventueel beter zou kunnen. Ik vermoed namelijk van wel gezien hetgeen ik in mijn eerste post omschrijf. En daarbij dus op welke manier ik dit het beste zou kunnen uitlezen met PHP. Niet zozeer welke van deze 2 is het efficientst, dus.

[dusty]

Ohhh... database modelletjes

beginnen we met je ID's .. als je ipv. ID netjes "productID" , "subcatID" en "prijzenID"

Slaan we even over dat jouw database model NIET goed genormaliseerd is.

En dat terwijl ik zoiets als de titel van het product maar 1x hoef te weten. Vandaar dat ik me afvraag of het efficienter kan, en of mijn query een beetje handig is.

Dat zal ook gebeuren, aangezien elke row dezelfde soort informatie zal bevatten.

Echter kan je query wel geoptimaliseerd worden.

code:

  "SELECT
     A.titel,
     A.omschrijving,
     B.id,
     B.titel,
     B.info,
     C.id,
     C.aantal,
     C.prijs
   FROM
     prod_gg A,
     subcat B,
     prijzen C
   WHERE
     C.subcat_id = B.id AND B.prod_id = A.id AND A.id = '$id'";

Ten eerste gefeliciteerd, je bent een van de weinigen die meteen de query in een leesbaar formaat neerzet, als je de and's ook nog in aparte rijen zou plaatsen zou het nog leesbaarder worden

we gaan even een kijkje nemen naar je WHERE statement, aangezien we daar het kunnen optimaliseren.

code:

     C.subcat_id = B.id   (1)
AND 
     B.prod_id = A.id     (2)
AND 
     A.id = '$id'";  (3)

Wat jij in je code de database laat doen zijn tabellen samen voegen en dan een selectie erop maken.

Bij (1) voeg je tabel C samen met tabel B.
Stel dat tabel C 100 records heeft en tabel B 100 waar ze gelijk aan elkaar zijn. Krijg je dus 100*100 resultaten what resulteert in een grote tabel met 10.000 resultaten.

Bij (2) voeg je de "nieuwe" tabel samen met tabel A.
Stel dat Tabel A 100 resultaten heeft die bij tabel B ook voorkomen. Krijg je dus een grote tabel met 10.000 * 100 resultaten. is een hele grote tabel van 1.000.000 resultaten.

Bij (3) Leg je de restrictie op dat bij Tabel A de ID juist moet zijn.
Stel dat er maar 5 resultaten bij die ID horen. Krijg je dus een selectie over 1.000.000 rows die 5 resultaten oplevert.

Stel we gooien even de verschillende statements door elkaar.

code:

     A.id = '$id'";  (3)
AND 
     A.id = B.prod_id     (2)
AND 
     B.id = C.subcat_id   (1)

Door de restrictie eerst te doen op tabel A (3) krijg je een hele kleine tabel terug van resultaten.

Hier zoek je de resultaten op die door de voorwaarde 2 wordt opgelegd, wat dus ook een kleine tabel blijft opleveren.

Hierbij zoek je de laatste voorwaarde (1) op. en geeft uiteindelijk dus ook de 5 resultaten terug zoals in de eerste geval.

Voorzover deze les "query optimalisatie".

[edit: een 0 teveel bij 10.000]

[2]

*respect*

Ja zoals jij het uitlegt kan ik me wel voorstellen dat dat van mijn query een hoop verschil maakt.

Maar kun je me misschien vertellen waarom dit model niet goed genormaliseerd is? Ik zie namelijk niet wat beter zou kunnen. Moet ik er wel misschien bij vertellen dat ik er rekening mee gehouden heb dat de verschillende aantallen waarin produkten verkocht worden kunnen verschillen per subcategorie.

[tomato]

Wat als er geen aantallen onder een subcat voorkomen, of geen subcats onder een produkt? Met je huidige query zie je deze subcat of dit produkt dan niet, is dat de bedoeling?

[2]

In principe heeft ieder produkt minstens 2 subcategorien, en iedere subcategorie minstens 1 aantal-categorie. Dus dat is niet erg.

Dat de aantallen kunnen verschillen is vooral vanwege het feit dat sommige aantallen in bepaalde subcategorien niet verkrijgbaar zijn.

[dusty]

Op dinsdag 06 november 2001 14:50 schreef 2 het volgende:
Maar kun je me misschien vertellen waarom dit model niet goed genormaliseerd is? Ik zie namelijk niet wat beter zou kunnen. Moet ik er wel misschien bij vertellen dat ik er rekening mee gehouden heb dat de verschillende aantallen waarin produkten verkocht worden kunnen verschillen per subcategorie.

1) Aanwijzing.

De prijs van een product zal altijd hetzelfde blijven, wat wel waarschijnlijk veranderd is de "percentage/whatever" korting die men krijgt als men meerdere aantallen tegelijk koopt. Het verschil zit hem hier in de onderhoudbaarheid. Als je namelijk een product hebt van fl 200,- en heeft 5 subcategorien en in elke subcategorie zitten 10 verschillende "aantallen". Betekent dat als het product opeens fl 100,- gaat kosten dat je opeens 1 * 5 * 10 = 50 prijzen moet gaan aanpassen. Terwijl dit bij een juiste methode maar EEN keer aangepast hoeft te worden. (tenzij de kortings eenheid veranderd.)

2) Aanwijzing.

Je hebt een ID in de PRIJS tabel zitten, Deze ID doet NIETS. En is dus overbodig DUS is je database model ALTIJD fout

[D2k]

<ot>

Codito, Ergo Sum

ik codeer dus ik ben

leuk verzonnen
</ot>

[2]

Op dinsdag 06 november 2001 15:10 schreef dusty het volgende:
1) Aanwijzing.

De prijs van een product zal altijd hetzelfde blijven, wat wel waarschijnlijk veranderd is de "percentage/whatever" korting die men krijgt als men meerdere aantallen tegelijk koopt. Het verschil zit hem hier in de onderhoudbaarheid. Als je namelijk een product hebt van fl 200,- en heeft 5 subcategorien en in elke subcategorie zitten 10 verschillende "aantallen". Betekent dat als het product opeens fl 100,- gaat kosten dat je opeens 1 * 5 * 10 = 50 prijzen moet gaan aanpassen. Terwijl dit bij een juiste methode maar EEN keer aangepast hoeft te worden. (tenzij de kortings eenheid veranderd.)

Ik weet niet precies of ik begrijp wat je bedoelt, maar misschien wordt het wat duidelijker als ik een voorbeeld geef met daadwerkelijke producten (en bogus prijzen):

code:

   Kaarten

   Visitekaartjes   1000 stuks  -  f175,-
              2000 stuks  -  f325,-
              3000 stuks  -  f475,-

   Kaarten A6    2000 stuks  -  f325,-
              3000 stuks  -  f475,-

   Kaarten A5    1000 stuks  -  f175,-
              2000 stuks  -  f325,-
              3000 stuks  -  f475,-

2) Aanwijzing.

Je hebt een ID in de PRIJS tabel zitten, Deze ID doet NIETS. En is dus overbodig DUS is je database model ALTIJD fout

Die ID gebruik ik om op de website d.m.v. linkies en sessionvariables naar een bepaald aantal/prijs klasse van het product te verwijzen bij een bestelling. Dus 2000 Kaarten A5 zou zijn 1/3/8.

Hm nu ik dit schrijf begin ik een beetje door te krijgen wat je bedoelt volgens mij: 1/3/2000.

Sorry als ik irritant ben hoor maar dit is zo leerzaam.

[dusty]

Voorbeeld: de productie van visite kaartjes worden opeens gigantisch goedkoper en je kan 80% op je kosten ervan besparen. Dit wil je uiteraard terug laten komen in je prijzen, hoeveel prijzen moet jij aanpassen ?

[2]

Een hoop.

Ik begrijp denk ik wel wat je bedoelt. In plaats van prijzen meer een soort indexgetal waarmee je rekent. Maar ik weet niet in hoeverre dat echt in mijn geval nodig is. Het zou wel beter/netter zijn, maar deze producten komen nogal vaak te vervallen en er worden dan weer nieuwe toegevoegd. Maar inderdaad, zo had ik er nog niet over nagedacht.

[bigtree]

Op dinsdag 06 november 2001 15:10 schreef dusty het volgende:

[..]

2) Aanwijzing.

Je hebt een ID in de PRIJS tabel zitten, Deze ID doet NIETS. En is dus overbodig DUS is je database model ALTIJD fout

Het alternatief is een primaire sleutel die uit 2 velden bestaat. Er zijn redenen om dat niet te doen in sommige gevallen. In dit geval maakt het niet zo veel uit. De derde normaalvorm is niet zaligmakend hoor.

[dusty]

Op dinsdag 06 november 2001 23:40 schreef bigtree het volgende:
Het alternatief is een primaire sleutel die uit 2 velden bestaat. Er zijn redenen om dat niet te doen in sommige gevallen. In dit geval maakt het niet zo veel uit. De derde normaalvorm is niet zaligmakend hoor.

Bij de vierde normaal vorm ga je weer items terug halen die je weg had gehaald, vaak doe je dit voor de performance, echter is dit NIET met de ID. Je gaat nog steeds in de hogere normaal vormen geen informatie halen die overbodig is. Er wordt niets gedaan met een ID van de prijs, Gewoon weg omdat het geen nut heeft, als jij geregeld verder gaat dan de derde normaal vorm zou je dat moeten weten.

Daarnaast had je van alle tabellen samen kunnen afleiden dat de derde normaal vorm nog niet behaald is geweest.

voor 80% van de mensen hier zal de 3e normaal vorm goed genoeg zijn, pas als men verder gaat ontwikkelen en echte grote databases gaat gebruiken is het verstandig om verder te gaan dan de 3e normaal vorm. Vooral als je MYSQL gebruikt is het juist helemaal aan te bevelen om nooit verder te gaan dan de 4e normaal vorm. Als er hier 2% van de mensen zijn die ooit naar de 5e normaal vorm moeten gaan is dat veel.