[PHP/MySQL] Zoeken met veel verschillende tabellen

[SvMp]

Stel je hebt een database waar producten in staan.

Er is een product-tabel. Daar staan oa. artikelnummer en naam in, maar ook een id als primary key.

Het is vrij simpel om daar queries op uit te voeren.

Naast de product-tabel zijn er hulptabellen. Die hulptabellen hebben een verschillende opbouw.

Voorbeeld van een aantal tabellen:

- 'eigenschappen' met de velden product_id, eigenschapcode en waarde.
- 'prijs' met de velden product_id, datum en prijs (het record met de meest recente datum die niet in de toekomst ligt bevat de huidige prijs).
- 'voorraad' met de velden product_id, datum en voorraad (het record met de meest recente datum bevat de huidige voorraad)

Het is gemakkelijk om van een product een overzicht met eigenschappen weer te geven. Wil ik een prijs weten, dan kan ik die met een vrij simpele query uit de prijs-tabel halen.

Het wordt echter lastig als ik een lijst producten wil weergeven op grond van verschillende kenmerken en sortering.

Bijvoorbeeld sortering op prijs. Ik moet dan een subquery maken die de prijs ophaalt. Met een join kan dit niet, want ik moet alleen de meest recente prijs hebben. Tenzij ik die join combineer met een subquery die de datum van de meest recente prijs (die niet in de toekomst ligt) ophaalt.

Zoek-queries worden hierdoor complex. Er zijn nog veel meer verschillende tabellen, hierboven noemde ik enkele voorbeelden. Er moeten veel zoek-mogelijkheden zijn.

Hoe los ik het probleem op van de (te) complexe queries om een lijst producten weer te geven op basis van bepaalde criteria en een bepaalde sortering?

Mijn oplossing: Een index-tabel maken met voor elk beschikbare product een record met daarin de huidige prijs, huidige voorraad, bepaalde eigenschappen waar op gezocht kan worden, etc.. De lijst- en zoekfunctie werkt met die index-tabel. Index-tabel wordt periodiek opnieuw opgebouwd, mbv. een cronjob.

Zijn er betere oplossingen denkbaar?

[Verwijderd]

Ik mis een beetje de reden waarom je dit wil? Je zegt wel complexe queries, maar wat is daar het probleem mee? Een database kan dat soort queries prima aan, zodra jij je indexen goed hebt staan wil de database wel rap zoeken.

Nadeel van jou oplossing is dat je met de actualiteit van je database zit. Je introduceerd een nieuw probleem

Als je wel voor zo een oplossing kiest waarom voeg je dan niet extra kolommen toe, dan hoef je maar 1 tabel aan te spreken, anders zit je met hetzelfde probleem(nog steeds een join, met zoek parameters erbij)

[kwaakvaak_v2]

Of het zoeken gewoon over laten aan een zoeksysteem als solr

[Verwijderd]

kwaakvaak_v2 schreef op maandag 12 maart 2012 @ 16:44:
Of het zoeken gewoon over laten aan een zoeksysteem als solr

Lees ik goed dat je dan via http een request moet maken? Dat lijkt mij best veel overhead eerlijk gezegd..

[Freeaqingme]

Je hebt anders hele mooie libraries die dat netjes voor je encapsulaten. Bijv. middels Solarium

[YopY]

Verwijderd schreef op maandag 12 maart 2012 @ 19:58:
[...]


Lees ik goed dat je dan via http een request moet maken? Dat lijkt mij best veel overhead eerlijk gezegd..

Meer of minder overhead dan een nieuwe tabel maken en het zoekwiel opnieuw uitvinden?

Als je zoekqueries te zwaar en complex worden voor een gewone tabel, dan wordt het ook tijd voor een echte zoekmachine / indexeerservice, dwz solr. En http requests op dezelfde server zijn nagenoeg verwaarloosbaar. Waarschijnlijk zijn er ook wel andere interfaces.

[Verwijderd]

YopY schreef op maandag 12 maart 2012 @ 21:33:
[...]


Meer of minder overhead dan een nieuwe tabel maken en het zoekwiel opnieuw uitvinden?

Als je zoekqueries te zwaar en complex worden voor een gewone tabel, dan wordt het ook tijd voor een echte zoekmachine / indexeerservice, dwz solr. En http requests op dezelfde server zijn nagenoeg verwaarloosbaar. Waarschijnlijk zijn er ook wel andere interfaces.

Ja wat betreft die overhead heb je gelijk, maar dat was ook niet mijn optie

Maar een http request heeft meer overhead dan het direct over de mysql socket flikkeren(assumtion, i know its the mother of all fuckups). Geen zin om dat nu uit te zoeken

edit:

Sockets are generally a lighter-weight option over HTTP since the application controls when the end points communicate. HTTP, on the other hand, must make several trips between the end points to exchange header information as well as the data.

http://www.java-samples.com/showtutorial.php?tutorialid=740

[ Voor 18% gewijzigd door Verwijderd op 13-03-2012 00:04 . Reden: Toch maar even iets opgezocht ]

[Gomez12]

Verwijderd schreef op dinsdag 13 maart 2012 @ 00:02:
[...]
Ja wat betreft die overhead heb je gelijk, maar dat was ook niet mijn optie

Maar een http request heeft meer overhead dan het direct over de mysql socket flikkeren(assumtion, i know its the mother of all fuckups). Geen zin om dat nu uit te zoeken

Puur tussen socket / http wint http.
Maar het gaat hier ook over het achterliggende systeem, op het moment dat je het over een dedicated search-ding vs een RDMS gaat hebben weet ik wel welke er (in basis) op de lange termijn gaat winnen. Die extra overhead van http is peanuts op het grotere plaatje.

Solr enkel afkeuren omdat het misschien een puntje heeft wat minder presteert dan sockets lijkt me micro-optimalisatie niveau waarop je het kind met het badwater weggooit.

[Fiander]

Ik denk dat ik het met de eerste reaktie van altrincham eens ben.
in soon geval wanneer je echt te grote queries met joins en sub queries krijgt, kun je gecontroleerd de-normaliseren.

in jou voorbeeld:
als 95% of meer van je queries / requests de huidige prijs nodig hebben, kun je er soms beter voor kiezen om deze als kolom terug op te nemen in je producten tabel. In jou voorbeeld elimineer je daar een subquerie mee. Met een simpele aanname dat een prijs maar één keer per dag wijzigt, zou je de prijzen in je producten tabel met een cron-job uptodate kunnen houden.
wel een fail-save inbouwen ivm foute prijzen die je snel moet kunnen wijzigen.

Voorraad op deze manier elimineren zou ook kunnen (zonder de cron-job dan, voorraad lijkt mij te dynamisch), maar omdat deze vaker wijzigt, zullen je indexen ook meer wijzigen. En dit geeft weer een andere belasting waarmee je niet altijd beter af bent.

Verder, wat noem jij "te lang" als je zegt dat je querie te lang duren?
Wat heb je gemeten, welk gedeelte van je querie duurt het langst, en wat scheeld het wanner je een join / subquerie minder hebt? Hoe groot zijn je tabellen eigenlijks?

[Verwijderd]

Gomez12 schreef op dinsdag 13 maart 2012 @ 00:15:
[...]

Puur tussen socket / http wint http.
Maar het gaat hier ook over het achterliggende systeem, op het moment dat je het over een dedicated search-ding vs een RDMS gaat hebben weet ik wel welke er (in basis) op de lange termijn gaat winnen. Die extra overhead van http is peanuts op het grotere plaatje.

Solr enkel afkeuren omdat het misschien een puntje heeft wat minder presteert dan sockets lijkt me micro-optimalisatie niveau waarop je het kind met het badwater weggooit.

Ik snap dat het een prima systeem is, en zie de waarde er wel van in hoor . Vond het alleen vreemd waarom http gebruikt werd. Heb er op dit moment ook geen tijd voor om de docs door te lezen of er nog meer interfaces zijn.

Maar ik keur het zeker niet af, ik was alleen kritisch en dat is prima dacht ik zo Wist er alleen niets van, dus deze komt op mijn leeslijst te staan.

[kwaakvaak_v2]

Zou ik zeker doen.. want iets enkel afkeuren omdat het een HTTP socket heeft klinkt niet als een weloverwogen besluit

[storeman]

Ik betwijfel of solr nu echt een goede oplossing is voor het beschreven probleem. De TS houdt hetzelfde probleem met het regelmatig updaten van een of ander record, deze is niet dynamisch. Voordat je tot een goede oplossing kunt komen:

1. Hoe vaak zullen hier queries op gedraaid worden?
2. Wat is de verwachte performance?
3. Hoeveel records verwacht je dat het systeem heeft als het gevuld is?

Na het doorlezen van de TS dacht ik direct aan views, ik verwacht dat dit een prima oplossing is voor het genoemde probleem. Een solide RDBMS kan hier voor veel goeds zorgen.

Daarnaast kun je natuurlijk nog gaan denken in richting van NoSQL als front-end. Hierin kun je documenten gaan aanmaken en doorzoeken. De structurele data blijf je gestructureerd opslaan in conventionele database. Ook hierin zijn diverse variaties mogelijk.

[Yankovic]

select p.id, p.naam, pr.prijs, v.voorraad
from product p
left join eigenschappen e on e.product_id = p.id
left join prijs pr on pr.product_id = p.id
left join voorraad v on v.product_id = p.id
where pr.datum = (select max(datum) from prijs where product_id = p.id and datum <= current date)
and v.datum = (select max(datum) from voorraad where product_id = p.id)
and e.eigenschapcode in (CODE1, CODE2, CODE3)
order by pr.prijs;

Ziet er in mijn ogen niet zo complex uit... En volgens mij (ben niet zo thuis in de wereld van php) bestaan er prima frameworks die dit voor je doen.. Anders kan je er altijd nog een view van maken als je dit te complex vind.

[mjax]

Met Fiander eens, gecontroleerd de-normalizeren zou de beste aanpak met de minste impact zijn. I.p.v. via een cronjob kun je de velden in de producten tabellen ook via triggers op de eigenschappen tabel laten bijwerken. Dan weet je zeker dat de producten tabellen van (bepaalde) eigenschappen altijd de meest recente data bevat en kun je eenvoudig queries hierop uitvoeren.

[Verwijderd]

mjax schreef op vrijdag 16 maart 2012 @ 12:22:
Met Fiander eens, gecontroleerd de-normalizeren zou de beste aanpak met de minste impact zijn. I.p.v. via een cronjob kun je de velden in de producten tabellen ook via triggers op de eigenschappen tabel laten bijwerken. Dan weet je zeker dat de producten tabellen van (bepaalde) eigenschappen altijd de meest recente data bevat en kun je eenvoudig queries hierop uitvoeren.

Of een view maken? Het is onzinnig veel werk om dit te doen.

Het gevaar hier is dat mensen daadwerkelijk gaan geloven dat je bij een paar joins ineens een nieuwe tabel moet gaan maken. Dit brengt een hoop complexiteit, foutgevoeligheid en ontwikkeltijd met zich mee.

De vraag van de OP is duidelijk, het is gewoon nodig om complexe queries te schrijven, dit moet geleerd worden. Je kunt een probleem niet versimpelen door de oplossing te veranderen, het requirement blijft bestaan.

[ Voor 28% gewijzigd door Verwijderd op 27-03-2012 10:38 ]