[MYSQL] 1 of 2 tabellen

[rmfloris]

Ik heb een vraag m.b.t. performance van de database. Ik twijfel tussen twee opzetten.

De achtergrond is een prijs database, zoiets als de price watch. In de huidige opzet is er een tabel is voor de huidige prijs van het artikel. Dit is dus 1 record per leverancier/item combinatie.
Nu zit is er over na te denken om ook de prijs historie bij te gaan houden, zodat je de prijswijzigingen kan volgen. Hier komen bij mij de vragen naar boven hoe dit het beste aan te pakken. Hopelijk kunnen jullie mij hiermee helpen met jullie inzichten.
Ik had zelf de volgende mogelijke oplossingen in gedachte:

1. De huidige prijstabel (id|leverancier_id|product_id|prijs) uit te breiden met een datum veld. Hierdoor zou je in theorie kunnen selecteren op de laatste datum. Een lastig punt is gelijk, dat de max(datum) niet automatisch de laatste datum hoeft te zijn voor alle producten. Sommige kunnen een update eerder hebben gehad. Hier zal dus eventueel een subquery aan te pas moeten komen om de laatste datum voor ieder product te selecteren (is dat eigenlijk wel mogelijk ?).
   Ook zal de DB relatief groot worden, terwijl deze alleen wordt gebruikt voor de prijs historie en niet voor de ‘normale’ queries op de website voor de producten. Voorzichtig reken levert je 300 producten x 365 dagen x 10 aanbieder = 1.100.000 records op per jaar.
2. Als alternatief hierop zat ik te denken om naast de huidige tabel een extra tabel te plaatsen voor de historie. Hierdoor krijg je wel dat je dezelfde data in twee tabellen hebt staan, namelijk de laatste toevoeging aan de historie tabel moet overeenkomen met de waarde in de “prijzen” tabel. Je blijft de grote tabel van 1.100.000 regels houden, maar die benader slechts een enkele keer.

Wat heeft jullie voorkeur, of als er design fouten inzitten, waar zouden er eventueel verbeteringen in bovenstaand model kunnen worden geplaatst?

P.s. mocht iemand kunnen helpen met de subquery voor optie 1, dan hoor ik dat uiteraard ook graag.

[Verwijderd]

een artikel heeft heef meerdere prijzen. Voor een goed database ontwerp kies je dan voor 2 tabellen.

product_id | leverancier_id | product_id |

prijs_id | prijs | datum

Lees maar eens wat over database normalization.

[Redshark]

Al gedacht aan een kolom datum vanaf en een kolom datum tot? Je hoeft dan niet voor iedere dag een record toe te voegen maar alleen records af te sluiten en toe te voegen als je prijs daadwerkelijk wijzigt. Eventueel kun je met het toevoegen van een extra kolom 'ind_actueel' snel je actuele prijzen naar boven halen.

Over het aantal records zou ik me niet zo druk maken, dat moet wel lukken als je je DB een goed hebt ingericht.

[SvMp]

Ik weet niet om wat voor soort produkt het gaat, maar als je de Tweakers pricewatch als voorbeeld neemt, is een dagelijkse wijziging overschat. Een datum opslaan waarop een prijs in gaat, is dan voldoende.

Het mooiste is 1 tabel: prijs, vanaf_datum, item, leverancier
Dat zou ik aanhouden als ik jou was.

Met having en group by kun je prima de laatste pakken:
SELECT ....................
GROUP BY (item, leverancier)
HAVING vanaf_datum=MAX(vanaf_datum)


Performance problemen kun je oplossen dmv. caching.
- De hele lijst cachen die je met group by en having heb gelezen, dmv. bijv. Memcached. Of je cached de complete pagina met bijv. Varnish.
- Elk item afzonderlijk lezen. Aan de hand van eventuele zoek-opties van de gebruiker haal je een lijst item ID's + leverancier ID's op. Je schrijft een functie die de huidige prijs geeft. Via afzonderlijke queries inefficient, maar als je deze cached heb je daar geen last meer van en profiteer je van flexibiliteit.

[rmfloris]

@Ethanol mee eens, de database is ook zo opgezet. Leveranciers in een aparte tabel, artikelen in een aparte tabel en de prijzen in een aparte tabel. Alles gelinkt met ID'tjes.

@Redshark/@SvMP, de vanaf datum is natuurlijk een veel logischer oplossing. Hier had ik zelf niet aan gedacht. Hierdoor ben je eigenlijk al een groot deel van het probleem kwijt! Ook het aantal records zal hierdoor een stuk kleiner worden! Super.

Meer tips zijn uiteraard welkom.

[DaRKie]

Ik persoonlijk zou mijn data apart houden van mijn historiek om er zo voor te zorgen dat je je business code/queries niet overbelast met extra's om de historiek eruit te filteren.

Ik zou die extra tabel dan ook meer als een journaling tabel beschouwen en er extra informatie insteken over de aanpassingen die er uitgevoerd worden: door wie, wanneer, etc. (wat altijd wel handig kan zijn om fouten op te sporen in een multi-user omgeving)

Dit is wel in de veronderstelling dat het puur historiek is dat je wil bijhouden. Als de datum ook echt in je business code gebruikt moet worden, dan volg ik Redshark zijn idee.

[rmfloris]

SvMp schreef op dinsdag 16 november 2010 @ 15:01:
Met having en group by kun je prima de laatste pakken:
SELECT ....................
GROUP BY (item, leverancier)
HAVING vanaf_datum=MAX(vanaf_datum)

Dit werkt perfect voor 1 leverancier/product combinatie. Wat als ik het aantal prijzen wil tellen (welke geldig zijn)? Dit hoeft namelijk niet dezelfde vanaf_datum te zijn en group by leverancier wil je dan niet hebben. Dit was mijn initiële probleem ook bij deze opzet.

@DaRKie de tabel gaat wel worden gebruikt om de historie laten zien op de website. Dus per product is het wel interessant om deze informatie te laten zien.

[ Voor 12% gewijzigd door rmfloris op 16-11-2010 15:35 ]

[SvMp]

rmfloris schreef op dinsdag 16 november 2010 @ 15:35:
[...]


Dit werkt perfect voor 1 leverancier/product combinatie. Wat als ik het aantal prijzen wil tellen (welke geldig zijn)? Dit hoeft namelijk niet dezelfde vanaf_datum te zijn en group by leverancier wil je dan niet hebben. Dit was mijn initiële probleem ook bij deze opzet.

@DaRKie de tabel gaat wel worden gebruikt om de historie laten zien op de website. Dus per product is het wel interessant om deze informatie te laten zien.

Aantal prijzen per produkt? SELECT COUNT(DISTINCT leverancier) FROM tabel WHERE produkt=?

Over geldige prijzen is nog niet gesproken. In het model wat ik eerder voorstelde is niet voorzien in een situatie waarin een produkt niet meer in het assortiment zit.
Je zou kunnen doen dat de prijs dan 0 is. Dus vanaf 1-12-2010 niet meer in assoriment, is entry met datum 1-12-2010 met prijs 0.

SQL:

SELECT prijs
WHERE (item=?) AND (prijs>0)
GROUP BY (item, leverancier)
HAVING vanaf_datum=MAX(vanaf_datum)

SELECT COUNT(prijs)
WHERE (item=?) AND (prijs>0)
GROUP BY (item, leverancier)
HAVING vanaf_datum=MAX(vanaf_datum)

(niet getest)

[Janoz]

Wanneer je begin en eind datum bij een prijs opslaat wordt alleen het updaten en toevoegen iets ingewikkelder. Bij het uitvragen kun je gewoon een datum meegeven waarop je de prijzen wilt hebben. Een simpele

WHERE date_from <= %date AND (date_until > %date OR date_until IS NULL)

is dan genoeg om alle huidige prijzen op te halen. (Zoals je ziet wordt NULL geïnterpreteerd als onbekende einddatum ergens in de toekomst). De querie wordt een stuk simpeler en je hoeft ook geen rare dingen met prijswaarden van 0 te doen. Prijs van 0 is sowieso absoluut geen goed idee. Het zorgt ervoor dat al je queries ruk worden. Je kunt niet eens meer op een fatsoenlijke max avg en min querie vertrouwens zonder uitgebreide niet intuïtieve where toe te voegen.

[ Voor 27% gewijzigd door Janoz op 16-11-2010 16:16 ]

[rmfloris]

@SvMP, met geldig bedoelde ik de laatste prijs. Het idee is dat wanneer een artikel niet meer wordt verkocht, deze geheel uit de database gaat.

@Janoz dit is een simpele, maar makkelijke oplossing. Het updaten/wegschrijven heeft iets meer voeten in de aarde.

[Janoz]

Wegschrijven is maar marginaal lastiger. Bij het toevoegen met je je vorige record afsluiten (set date_until = vandaag where date_until is null) en de nieuwe toevoegen met einddatum null. Wanneer je jezelf niet vertrouwt zou je eventueel enkele sanity scriptjes kunnen schrijven waarmee je (handmatig) de consistentie kunt controleren.

[DaRKie]

rmfloris schreef op dinsdag 16 november 2010 @ 15:35:
[...]
@DaRKie de tabel gaat wel worden gebruikt om de historie laten zien op de website. Dus per product is het wel interessant om deze informatie te laten zien.

Je hebt het dus enkel nodig om op 1 pagina de historiek te laten zien, voor alle andere queries is het gewoon extra 'balast' die je moet uitfilteren, het is immers geen bedrijfskritische informatie.

In mijn ogen maak je het jezelf gewoon lastiger door alles in 1 tabel te steken.

En natuurlijk is dat uitfilteren in een query vrij triviaal, maar dan moet je dit in elke query waar je de prijs opvraagt, joint, etc ook doen terwijl dat gewoon niet nodig is.

Dus gewoon 2 tabellen gebruiken. En op je prijstabel zet je een trigger die bij een row insert/update, de vorige waarde in de 2de (journaling) tabel zet. Hoef je je ook nergens zorgen om te maken in je code bij het aanpassen of toevoegen van prijzen.

[ Voor 15% gewijzigd door DaRKie op 17-11-2010 10:32 ]

[funz]

Goeiendag,

Ik ben programmeur van een ERP-pakket, en als je daar iets hebt is het VEEL tabellen met heul veul regels .

De "mooiste" oplossing is om de prijzen in een aparte tabel te plaatsen, je kan ze dan ophalen mbv onderstaande query:
select *, (select prijs from prijzen order by datum desc limit 1,1) as prijs
from artikelen.
eventueel met toevoeging van een where clause zodat je de toekomstige prijzen eruit filtert.

Echter als je puur naar performance kijkt kan je het beste gewoon een veldje met de actuele prijs bijhouden in de tabel artikelen. Je heb dan wel wat dubbele informatie in je database staan, maar dat weegt bij lange na niet op tegen de performance winst.

Vaak heb je te maken met een omslagpunt, boven een bepaald aantal records / leesacties wordt het interessanter om bovenstaande te gebruiken. Het kan zelfs het verschil betekenen tussen 1 server, of moeten uitbreiden naar meerdere servers. (Hyves is een goed voorbeeld hoe men zichzelf in de vingers kon snijden).

Verder:
Caching: uiteraard, maar als het onderliggende ontwerp bagger is...
Triggers: Zou ik in eerste instantie proberen te vermijden, ze zijn niet heel complex, maar je kan ze zo makkelijk vergeten na een update/migratie van de database. Het gevolg is dat je programma niet meer werkt, en dit nergens in je broncode is terug te vinden.

[Freeaqingme]

Funz, als performance je zo lief is (hoewel ik niet overtuigd kan worden dat 1 simpele join een performance killer kan zijn voor een RDBMS (wat mysql is)). Als je 't dan ook netjes doet neem je twee losse tabellen eentje met producten (zonder prijzen), eentje met prijzen, en vervolgens een indexed view waarin je de join uitvoert.

[FragFrog]

Funz geeft ook een voorbeeld met een subquery in plaats van een JOIN. In mijn ervaring zijn dat soort "keuzes" veel desastreuzer voor je performance dan wel of niet je data opsplitsen in meerdere tabellen - al zal een te complexe structuur er uiteraard wel voor zorgen dat je op een gegeven moment geen andere keuze meer hebt.

Triggers en andere database-based 'code' zijn gewoon onderdeel van je database-structuur. Ze vermijden omdat je ze kan vergeten over te zetten? Hoe zet jij een database over, door alle tabellen handmatig opnieuw aan te maken? Gebruik je ook geen views en SP's omdat je die ook kan vergeten?

[ Voor 5% gewijzigd door FragFrog op 17-11-2010 13:21 ]

[sopsop]

Ik weet niet of het ook in MySQL kan, maar je zou kunnen overwegen om de prijzen (huidige en history) in een tabel op te slaan en daar vervolgens een indexed view op te maken. In bijvoorbeeld SQL Server kan dat zorgen voor een enorme performance verbetering (als in: vergelijkbaar met een aparte tabel met alleen de courante prijzen).

[cariolive23]

funz schreef op woensdag 17 november 2010 @ 11:33:
De "mooiste" oplossing is om de prijzen in een aparte tabel te plaatsen, je kan ze dan ophalen mbv onderstaande query:
select *, (select prijs from prijzen order by datum desc limit 1,1) as prijs
from artikelen.

Hoe zeg je? Voor ieder resultaat een aparte subquery uitvoeren? Als er één manier is om de performance terug te brengen tot dramatisch niveau, dan is dit het wel... Hopelijk wordt dit door de optimizer nog weggewerkt, dit is echt een voorbeeld van hoe het _niet_ moet.

Triggers: Zou ik in eerste instantie proberen te vermijden, ze zijn niet heel complex, maar je kan ze zo makkelijk vergeten na een update/migratie van de database.

Hoe kun je ze dan vergeten? Ze staan in je database en dus ook in je backup en worden dus ook automatisch meegenomen in de migratie en/of update. Of maak jij backups waar je niks aan hebt?

Daarnaast bestaat er nog zoiets als source control, waar je uiteraard ook de database code en dus ook de triggers in hebt staan.

Voor het "vergeten" van triggers bij een migratie/update, moet je echt je best doen. Wanneer je niks doet, gaat het namelijk vanzelf goed.

Ps. MySQL heeft/had er een handje van om database objecten zoals views, triggers en stored procedures niet in de backup te zetten, maar dat zegt meer over MySQL, dan over databases in het algemeen. Dit staat overigens in de handleiding van MySQL en de eerste de beste test van de backup toont het ook al aan.

Edit: Je weet trouwens dat je met LIMIT 1,1 het tweede record uit de resultaten ophaalt? En dat wanneer er geen tweede resultaat is, je een leeg resultaat gaat krijgen? Tip, en dat is niet lullig bedoelt: Ga je eens verdiepen in SQL, dat voorkomt veel bugs en problemen.

[ Voor 8% gewijzigd door cariolive23 op 17-11-2010 14:15 ]

[YopY]

DaRKie schreef op woensdag 17 november 2010 @ 10:30:
En natuurlijk is dat uitfilteren in een query vrij triviaal, maar dan moet je dit in elke query waar je de prijs opvraagt, joint, etc ook doen terwijl dat gewoon niet nodig is.

Je zou ook een view kunnen maken met daarin het product, leverancier en huidige prijs (waar de huidige prijs uit de 2e tabel gehaald wordt, maar dan zonder dat je dat in elke query uit hoeft te schrijven), ipv je database te denormaliseren met premature optimalisaties ('huidige prijs' is een afleidbaar feit, om een schoolterm over te nemen). Dat heeft (tenminste mijns insziens) de voorkeur over dingen als triggers en dergelijke die de huidige prijs in de producttabel moeten updaten, dat een extra laag complexiteit toevoegt en aldus voor fouten kan zorgen.

Een view heeft ook de voorkeur (wederom imho) boven Funz zijn suggestie van een subquery in de select.

SQL:

select *, vCurrentPrices.price
from products
inner join vCurrentPrices on vCurrentPrices.product_id = products.id
where products.id = ?

Overigens zul je bij Funz zijn subquery-in-select voor elk record een query laten afvuren op de prijzentabel - da's suboptimaal op z'n best.

* YopY heeft dat ook in 'zijn' database gedaan, view gemaakt op prijzentabel met daarin een prijs en een geldig van en geldig tot datum die de huidige prijs eruitvist.

quote: funz

Echter als je puur naar performance kijkt kan je het beste gewoon een veldje met de actuele prijs bijhouden in de tabel artikelen. Je heb dan wel wat dubbele informatie in je database staan, maar dat weegt bij lange na niet op tegen de performance winst.

Hoeveel performance winst hebben we het hier over dan? En in welke relatie staat dat met de extra ontwikkeltijd / kosten en mogelijke bugs (en daaruitvolgende problemen die je kunt hebben met leveranciers)? Advies: Zorg eerst dat het goed werkt (op een normale manier), kijk dan naar performance, en als het echt heel zwaar is kun je kijken naar databasedenormalisatie (maar pas als je zaken als een query result cache of gewoon een paginacache al afgestreept hebt).

[FragFrog]

YopY schreef op donderdag 18 november 2010 @ 09:38:
Hoeveel performance winst hebben we het hier over dan? En in welke relatie staat dat met de extra ontwikkeltijd / kosten en mogelijke bugs (en daaruitvolgende problemen die je kunt hebben met leveranciers)?

Performance winst ten opzichte van zijn voorbeeld met een subquery in de select zal, zeker bij grotere resultsets, nog aanzienlijk zijn denk ik. In dat opzicht is het geen verkeerde suggestie. Bij een normale JOIN zal je het verschil niet merken nee

[ Voor 42% gewijzigd door FragFrog op 18-11-2010 12:11 ]

[DaRKie]

YopY schreef op donderdag 18 november 2010 @ 09:38:
[...]...Dat heeft (tenminste mijns insziens) de voorkeur over dingen als triggers en dergelijke die de huidige prijs in de producttabel moeten updaten, dat een extra laag complexiteit toevoegt en aldus voor fouten kan zorgen.

Mijn trigger gaat dan natuurlijk niet de product tabel maar de bijbehorende journaling tabel populeren --> hiermee kan ik gewoon een 1 simpele update doen van de prijs en de vrij eenvoudig te schrijven trigger zal dan de journaling tabel opvullen.
Dit zorgt er dan ook voor dat het minder complex is want ik ga in mijn queries nergens rekening moeten houden met allerlei datums om de actuele prijs te krijgen (al dan niet versimpelt via een view) en het aanpassen van de prijzen is ook gewoon 1 kolom van 1 record aanpassen ipv een nieuwe toe te voegen en de einddatum van de vorige prijs te gaan updaten.

Maar goed, het zijn duidelijk 2 standpunten, ieder heeft zo zijn eigen idee over welk het beste is, nu is het aan de TS om te kiezen welke argumenten voor hem het sterkst zijn

[sopsop]

Triggers voor journaling doeleinden zijn prima. In dit geval kan ik me echter voorstellen dat je een prijs kan invoeren die pas in de toekomst courant worden. In dat geval is de triggermanier een no-go.

Wat betreft het gebruik van een view: het is code-technisch wat duidelijker (en flexibeler), maar qua snelheidswinst marginaal (het blijft feitelijk een subselect). Pas bij het gebruik van een indexed view zal de performance toenemen, ik heb even gekeken, maar volgens mij ondersteund MySQL dit niet.

Het toevoegen van een bitveld om aan te geven of een prijs de actuele prijs van een product is, met daarop een index kan wel voor flinke performancewinst zorgen. Zodra je de resultaten daarop filtert ben je de group by kwijt.

[DaRKie]

sopsop schreef op donderdag 18 november 2010 @ 12:40:
Triggers voor journaling doeleinden zijn prima. In dit geval kan ik me echter voorstellen dat je een prijs kan invoeren die pas in de toekomst courant worden. In dat geval is de triggermanier een no-go.

Dat is inderdaad een pro om niet met journaling te werken.

Het komt er dus op neer wat de TS wilt:
- wilt hij enkel historiek
- of wilt hij toch iets meer doen met die datum(s) (prijs in de toekomst bepalen)

[Verwijderd]

Hier zal dus eventueel een subquery aan te pas moeten komen om de laatste datum voor ieder product te selecteren (is dat eigenlijk wel mogelijk

view vMx :
id,max(datum)
group op product, maar dan heb je nog geen prijs

view vprijs :
select prijs from vMx, tprijs where vmx.datum=tprijs.datum and vmx.id=tprijs.id

[ Voor 21% gewijzigd door Verwijderd op 19-11-2010 16:13 ]