[PHP/MySQL] Datamodel optimaal benutten

[Verwijderd]

Ik heb een "probleem" met mijn datamodel in MySQL en de applicatie in PHP...
Het probleem is dat de performance beter kan dan nu.

Nu haal ik dmv group_concat alle procuten en hun eigenschappen op, waarna ik de eigenschappen in PHP explode.

SQL:

 SELECT p.id, p.category, p.title, p.description as p_description, p.actief,
 GROUP_CONCAT(
    CONCAT(
        o.varname,
        '~',
        po.value,
        o.postfix
    )
    ORDER BY o.description
    SEPARATOR '|'
 ) options
 FROM products$postfix p
 INNER JOIN product_options po ON po.productid = p.id
 INNER JOIN options o ON o.id = po.optionid
 WHERE p.category = '$category' AND p.actief = 1
 GROUP BY p.id
 ORDER BY p.title

Dit lijkt me behalve ingewikkelder dan nodig, ook langzamer dan optimaal. Ik krijg het echter niet beter dan dit; wanneer ik het namelijk in zoveel mogelijk SQL probeer wordt het enkel langzamer.

Dit is mijn datamodel:



Het lijkt me perfect als ik een aantal producten gewoon volledig op kan halen, zonder dat het gelijk 0,5 sec duurt of een vieze oplossing is.

Een product uit categorie 1 heeft andere eigenschappen dan categorie 2. Dit is een voordeel voor het onderhouden maar een nadeel voor de performance, tot nu toe dan.

Wanneer ik het zo probeer duurt het extreem lang om bv alle nokia's op te halen:

SQL:

SELECT p.id, p.category, p.title, p.description, p.actief,prodopts.* FROM products p 
INNER JOIN
(
    SELECT productid FROM product_options po 
    INNER JOIN `options` o ON o.id = po.optionid 
    WHERE (po.value = 'Nokia' AND o.varname = '_merk') 
    GROUP BY po.productid HAVING COUNT(*) = 1 
) AS s1 ON s1.productid = p.id 
INNER JOIN
(
    SELECT * FROM product_options po 
    INNER JOIN `options` o ON o.id = po.optionid 
    GROUP BY po.productid,po.optionid
) AS prodopts ON prodopts.productid = p.id 
WHERE p.category = 1 ORDER BY p.title

[ Voor 20% gewijzigd door Verwijderd op 13-04-2010 12:05 ]

[Verwijderd]

Oops, kan de titel nog gewijzigd worden naar "[php/mysql] datamodel optimaal benutten"?

[Mike2k]

Kun je ons een case geven waarin het bovenstaande gebruikt wordt? Wellicht schort er iets aan je database opzet...

[Verwijderd]

Mike2k schreef op dinsdag 13 april 2010 @ 12:03:
Kun je ons een case geven waarin het bovenstaande gebruikt wordt? Wellicht schort er iets aan je database opzet...

Bij het ophalen van alle telefoons van het merk Nokia, waarbij ik ook gelijk de inkoopsprijzen, verkoopprijzen en marges nodig heb...
Ik kan het zo doen:

SQL:

SELECT p.id, p.category, p.title, p.description, p.actief,prodopts.* FROM products p 
INNER JOIN
(
    SELECT productid FROM product_options po 
    INNER JOIN `options` o ON o.id = po.optionid 
    WHERE (po.value = 'Nokia' AND o.varname = '_merk') 
    GROUP BY po.productid HAVING COUNT(*) = 1 
) AS s1 ON s1.productid = p.id 
INNER JOIN
(
    SELECT * FROM product_options po 
    INNER JOIN `options` o ON o.id = po.optionid 
    WHERE (o.varname IN  ('_merk','_inkoop','_verkoop')) 
    GROUP BY po.productid,po.optionid
) AS prodopts ON prodopts.productid = p.id 
WHERE p.category = 1 ORDER BY p.title

.. Dat kost echter al 0,1 seconde, indexes zitten op alle nodige kolommen, keys staan goed etc.....
Daarna moet het nog geparsed worden, dan ben je dus zo op 0,2 seconde... wat ik lang vind voor het tonen van 87 telefoons.

[ Voor 54% gewijzigd door Verwijderd op 13-04-2010 12:08 ]

[Mike2k]

Waarvoor heb je dan bijv de tabel product options nodig ?

[Verwijderd]

Mike2k schreef op dinsdag 13 april 2010 @ 12:08:
Waarvoor heb je dan bijv de tabel product options nodig ?

Voor inkoopsprijzen, verkoopprijzen en marges.
Maar dit zou voor een andere productcategorie iets anders kunnen zijn.

Ik wil ook kunnen zoeken in de eigenschappen, net zoals de pricewatch.

[ Voor 11% gewijzigd door Verwijderd op 13-04-2010 12:12 ]

[NMe]

Zet eens explain voor je query en voer hem uit in PHPMyAdmin.

Ik begrijp overigens niet waarom je group_concat gebruikt hier, maar ik vermoed dat daar in elk geval wat winst te behalen valt.

[Mike2k]

ja, maar je hebt nu options en product_options..

Volgens mij kan je de koppel tabel product_options weglaten...
Overigens, hoe weet je dat je sql query lang duurt ?
Heb je een explain gedaan?

[ Voor 26% gewijzigd door Mike2k op 13-04-2010 12:13 ]

[Verwijderd]

De explain

code:

id       select_type    TABLE       TYPE       possible_keys                    KEY                          key_len    ref                     ROWS     Extra
1        PRIMARY        <derived2>  ALL        (NULL)                           (NULL)                       (NULL)     (NULL)                  87       USING TEMPORARY; USING filesort
1        PRIMARY        <derived3>  ALL        (NULL)                           (NULL)                       (NULL)     (NULL)                  905      USING WHERE; USING JOIN buffer
1        PRIMARY        p           eq_ref     PRIMARY,NewIndex1                PRIMARY                      98         prodopts.productid      1        USING WHERE
3        DERIVED        o           RANGE      PRIMARY,varname                  varname                      98         (NULL)                  6        USING WHERE; USING TEMPORARY; USING filesort
3        DERIVED        po          ref        FK_product_options-OPTIONS       FK_product_options-OPTIONS   4          xxx_test.o.id           58        
2        DERIVED        po          ref        VALUE,FK_product_options-OPTIONS VALUE                        767                                86       USING WHERE; USING INDEX
2        DERIVED        o           ref        PRIMARY,varname                  PRIMARY                      4          xxx_test.po.optionid1            USING WHERE

Hoe ik weet dat het langzaam is, de tijd die het kost kan ik zien in SQLyog.

En, het is echt nodig om product_options te behouden, lijkt me, want de options definiëert hoe een optienaam is, bijvoorbeeld "Breedte", en dan een postfix van "CM".
Dat is per categorie verschillend.

[ Voor 30% gewijzigd door Verwijderd op 13-04-2010 12:22 ]

[Verwijderd]

NMe schreef op dinsdag 13 april 2010 @ 12:12:
Zet eens explain voor je query en voer hem uit in PHPMyAdmin.

Ik begrijp overigens niet waarom je group_concat gebruikt hier, maar ik vermoed dat daar in elk geval wat winst te behalen valt.

Ik doe het nu zo met php met een loopje, omdat die query zoals hierboven, zo langzaam is.

PHP:

preg_match_all('/_([^~]*)~([^|]*)\\|?/', $prod->options, $result, PREG_PATTERN_ORDER);
foreach ($result[1] as $index=>$varname)
{
    $prod['options'][$varname] = $result[2][$index];
}

[ Voor 4% gewijzigd door Verwijderd op 13-04-2010 12:26 ]

[NMe]

USING TEMPORARY; USING filesort
USING WHERE; USING TEMPORARY; USING filesort

Daar ga je je winst kunnen halen. .

Verwijderd schreef op dinsdag 13 april 2010 @ 12:26:
[...]

Ik doe het nu zo met php met een loopje, omdat die query zoals hierboven, zo langzaam is.

PHP:

preg_match_all('/_([^~]*)~([^|]*)\\|?/', $prod->options, $result, PREG_PATTERN_ORDER);
foreach ($result[1] as $index=>$varname)
{
    $prod['options'][$varname] = $result[2][$index];
}

Je plakt het eerst aan elkaar vast en trekt het dan weer uit elkaar? Doe eens niet. Schrijf die group_concat eens als een normale group by?

[ Voor 70% gewijzigd door NMe op 13-04-2010 12:28 ]

[Verwijderd]

NMe schreef op dinsdag 13 april 2010 @ 12:26:
USING TEMPORARY; USING filesort
USING WHERE; USING TEMPORARY; USING filesort

Daar ga je je winst kunnen halen. .

Snap ik, maar hoe los ik dat dan op? Dat gedeelte ontgaat me volledig...
Ik doe en inner join en heb overal indexes op, hoe kan het nu beter?

[ Voor 16% gewijzigd door Verwijderd op 13-04-2010 12:29 ]

[Verwijderd]

NMe schreef op dinsdag 13 april 2010 @ 12:26:

Je plakt het eerst aan elkaar vast en trekt het dan weer uit elkaar? Doe eens niet. Schrijf die group_concat eens als een normale group by?

Ja, dat deed ik net:

code:

SELECT p.id, p.category, p.title, p.description, p.actief,prodopts.* FROM products p 
INNER JOIN
(
    SELECT productid FROM product_options po 
    INNER JOIN `options` o ON o.id = po.optionid 
    WHERE (po.value = 'Nokia' AND o.varname = '_merk') 
    GROUP BY po.productid HAVING COUNT(*) = 1 
) AS s1 ON s1.productid = p.id 
INNER JOIN
(
    SELECT * FROM product_options po 
    INNER JOIN `options` o ON o.id = po.optionid 
    WHERE (o.varname IN  ('_merk','_inkoop','_verkoop')) 
    GROUP BY po.productid,po.optionid
) AS prodopts ON prodopts.productid = p.id 
WHERE p.category = 1 ORDER BY p.title

Dan duurt het dus zo lang, dat is van die explain van net.

[Mike2k]

Gebruik voortaan even de edit knop aub...

Dan nog, voor die prefix. Waarom niet een optional veld in de options table met prefixes zoals euro, cm enz.
Scheelt je een tabel en dus een join.
Laat eens zien hoe je velden gedefinieerd zijn.

[Verwijderd]

Mike2k schreef op dinsdag 13 april 2010 @ 12:30:
Gebruik voortaan even de edit knop aub...

Dan nog, voor die prefix. Waarom niet een optional veld in de options table met prefixes zoals euro, cm enz.
Scheelt je een tabel en dus een join.
Laat eens zien hoe je velden gedefinieerd zijn.

Volgens mij snap je het datamodel niet helemaal; de opties zijn definities van een productcategorie, voorbeeld:

code:

categories
id, title
77 gsm
88 papier

products 
id, title
1   nokia 3310
2   pak a4-papier

options
id, catid, varname, postfix
1   77      merk
2   77      inkoop    €
3   88      dikte      mm
4   88      formaat  

product_options
productid, optionid, value
1           1            Nokia
1           2            66
2           3            1
2           4            a4

[ Voor 11% gewijzigd door Verwijderd op 13-04-2010 12:37 ]

[Compuhair]

je kan het product joinen met product_options, en vervolgens die tabel joinen met de options tabel. Op die manier krijg je voor iedere optie van een product een record terug.
Dan hoef je niks te concatten in SQL, en te exploden in php.

Heb je dat al geprobeerd?

[Janoz]

Ah, een mooie database in database oplossing onder het motto "Dan zijn we zo lekker flexibel".

Als eerste opmerking zie ik een referentie via twee verschillende paden. Waarom hebben opties ook een category? In principe ligt dat al vast doordat een optie bij een product hoort die een bepaalde category heeft. Daarnaast lijkt het me dat een optie niet category specifiek hoeft te zijn.

Ten tweede lijkt het mij handiger om iig een deel van het database in database anti-pattern weg te halen. Ik neem aan dat er best wel meerdere opties te vinden zijn die bij alle producten wel voorkomen. Door die gewoon in de product tabel op te nemen maak je de boel een stuk efficienter.

Tot slot moet je niet gaan lopen concatten in de query zelf. Gewoon sorteren op product en dan kun je in je fetch lusje keurig alle opties 1 voor 1 uitlezen.

[Verwijderd]

Compuhair schreef op dinsdag 13 april 2010 @ 14:51:
je kan het product joinen met product_options, en vervolgens die tabel joinen met de options tabel. Op die manier krijg je voor iedere optie van een product een record terug.
Dan hoef je niks te concatten in SQL, en te exploden in php.

Heb je dat al geprobeerd?

Vreemd genoeg is dat dus langzamer

code:

SELECT p.id, p.category, p.title, p.description, p.actief,o.varname,o.chain,o.postfix,po.value FROM products p 
INNER JOIN
(
    SELECT productid FROM product_options po 
    INNER JOIN `options` o ON o.id = po.optionid 
    WHERE (po.value = 'Nokia' AND o.varname = '_merk') 
    GROUP BY po.productid HAVING COUNT(*) = 1 
) AS s1 ON s1.productid = p.id 
INNER JOIN product_options po ON po.productid = p.id
INNER JOIN OPTIONS o ON o.id = po.optionid
WHERE p.category = 1
ORDER BY p.title

Is langzamer (duurt 0,5s) dan:

code:

SELECT p.id, p.category, p.title, p.description AS p_description, p.actief,
GROUP_CONCAT(
CONCAT(
o.varname,
'~',
po.value,
o.postfix
)
ORDER BY o.description
SEPARATOR '|'
) OPTIONS
FROM products p
INNER JOIN product_options po ON po.productid = p.id
INNER JOIN OPTIONS o ON o.id = po.optionid
WHERE p.category = 1 AND p.actief = 1
GROUP BY p.id
ORDER BY p.title

Deze duurt 0,14s, en haalt de volledige data op voor alle gsm's, terwijl de query hierboven alleen nog maar nokia ophaalde.

[Verwijderd]

Janoz schreef op dinsdag 13 april 2010 @ 15:04:
Ah, een mooie database in database oplossing onder het motto "Dan zijn we zo lekker flexibel".

Als eerste opmerking zie ik een referentie via twee verschillende paden. Waarom hebben opties ook een category? In principe ligt dat al vast doordat een optie bij een product hoort die een bepaalde category heeft. Daarnaast lijkt het me dat een optie niet category specifiek hoeft te zijn.

Klopt, dat is een foutje, echter deze kolom wordt nooit gebruikt dus heeft het ook geen performance hit eigenlijk. Kan er idd uit.

Ten tweede lijkt het mij handiger om iig een deel van het database in database anti-pattern weg te halen. Ik neem aan dat er best wel meerdere opties te vinden zijn die bij alle producten wel voorkomen. Door die gewoon in de product tabel op te nemen maak je de boel een stuk efficienter.

Daar zat ik net ook aan te denken, dat is een van de beste dingen die ik kan doen lijkt me.

Tot slot moet je niet gaan lopen concatten in de query zelf. Gewoon sorteren op product en dan kun je in je fetch lusje keurig alle opties 1 voor 1 uitlezen.

Ja, das dus langzamer momenteel, en zolang dat zo is.... waarom dan niet concatten
[edit]
Hmmz... het uitvoeren was toch sneller, enkel het ophalen vanaf SQLyog duurde wel 0,4 sec..

Ik ga nog eens testen en post het resultaat dan later hier.

[ Voor 5% gewijzigd door Verwijderd op 13-04-2010 15:11 ]

[Verwijderd]

Wat vinden jullie van 0,15 seconde voor een pagina met alle Nokia GSM's.... erg langzaam of niet?
Het is dual quadcore 2,5ghz met 4 gb geheugen, die ook al productie draait...
[edit]
zal de edit knop voortaan gebruiken, maar voor een nieuw bericht moet ik toch niet editen?.

[ Voor 21% gewijzigd door Verwijderd op 13-04-2010 16:17 ]

[Mike2k]

Shroomy, gebruikt aub de Edit knop...
Zie ook de melding van het forum...telkens opnieuw een bericht posten is niet nodig.

[RobIII]

Shroomy: Gebruik de edit knop ( ) als je iets toe te voegen hebt; je topic herhaaldelijk omhoogschoppen is niet nodig. Zie ook topickick binnen 24 uur.

Mike2k schreef op dinsdag 13 april 2010 @ 16:14:
Shroomy, gebruikt aub de Edit knop...
Zie ook de melding van het forum...telkens opnieuw een bericht posten is niet nodig.

Mike2k: laat dit aan moderators over en doe een TopicReport () voortaan

[ Voor 48% gewijzigd door RobIII op 13-04-2010 16:19 ]

[Fiander]

zou je ook dit stuk eens kunnen ujitvoeren???

code:

 SELECT productid FROM product_options po  
    INNER JOIN `options` o ON o.id = po.optionid  
    WHERE (po.value = 'Nokia' AND o.varname = '_merk')  
    GROUP BY po.productid HAVING COUNT(*) = 1

ik heb zo een onderbuik gevoel dat je HAVING hier overbodig is, en alle tijd opslokt bij deze query.

[Janoz]

Trouwens, als je een overzicht wilt maken van alle nokia's, dan is het join technisch gezien misschien makkelijker om te beginnen in de opties tabel en daaraan dan de rest te joinen.

[GlowMouse]

voor je oorspronkelijke query: alter table products add key(category,actief,id)

code:

SELECT p.id, p.category, p.title, p.description, p.actief,o.varname,o.chain,o.postfix,po.value FROM products p 
INNER JOIN
(
    SELECT productid FROM product_options po 
    INNER JOIN `options` o ON o.id = po.optionid 
    WHERE (po.value = 'Nokia' AND o.varname = '_merk') 
    GROUP BY po.productid HAVING COUNT(*) = 1 
) AS s1 ON s1.productid = p.id 
INNER JOIN product_options po ON po.productid = p.id
INNER JOIN OPTIONS o ON o.id = po.optionid
WHERE p.category = 1
ORDER BY p.title

hiervoor:
alter table product_options add key(value,productid)
alter table products add key(category,title)