[SQL] Zoeken over meerdere rijen

[Spiral]

Hieronder staat een tabel met keyvalue pairs zowel key als value zijn tekst velden. Dit is een deel van de tabel, maar dit is het belangrijkste deel.

De bedoeling is dat in de userinterface een of meerdere keys selecteerd wordt en dan vult de gebruiker daar een vakue voor in. Het is een zoekfunctie. bv.

Ik zoek op alles met een lengte van 100. Resultaat moet dan id 1 en 3 zijn. Nu wil ik dit uitbreiden.
Ik zoek op een lengte van 100 en kleur groen. Resultaat id 3. 3 is namelijk kleur groen en lengte van 100.

Id	Key	Value
1	lengte	100
1	kleur	oranje
1	hoogte	358
3	lengte	100
3	kleur	groen
3	temperatuur	31
1	datum	22-02-08
8	naam	DHS323

Ik heb dit opgelost mbv een INTERSECT, maar als er gezocht dient te worden op meerdere keys dan krijg je voor elke key een INTERSECT.
Mijn vraag: Is dit een goede oplossing is qua perfomance? Of is er een makkelijkere manier. Ik weet zo niet hoe ik dit anders moet oplossen.

SQL:

SELECT id FROM mytable WHERE Key = 'lengte' AND Value = '100'
INTERSECT
SELECT id FROM mytable WHERE Key = 'kleur' AND Value = 'groen'
--INTERSECT n voor elke key/value pair waarop nog meer gezocht moet worden.

DB = postgreSQL
Aan de tabel structuur kan helaas niets veranderd worden.

[Verwijderd]

SQL:

SELECT id FROM mytable WHERE ( Key = 'lengte' AND Value = '100' ) AND ( Key = 'kleur' AND Value = 'groen' ) 

Zoiets? Voor elke zoekwaarde plak je er een AND achter

[Spiral]

AND gaat helaas niet werken, omdat allereerst alles met lengte is 100 wordt geselecteerd, en daarop weer een selectie van kleur is groen. Maar die zullen er nooit inzitten, omdat je de keys hebt met lengte. OR zal ook niet helpen.

De vraag is dus is INTERSECT de juiste oplossing voor het probleem in deze situatie

[Voutloos]

Bovenstaande query is onzin, want Key kan niet 2 waardes tegelijk zijn.

In setterminologie is het gewoon in een intersect. Maar je kan dat uiteraard ook bereiken met joins of in() clauses.

[Spiral]

Ik zit me af te vragen hoe het met joins zou moeten omdat het zoeken op keys in theorie oneiding groot zou kunnen zijn. Krijg je dan niet heel veel joins op dezelfde tabel? Wat ook weer veel tijdelijke tabellen aanmaakt?

[Standeman]

Een andere manier (wat volgens mij niet zo veel scheelt qua performance) zijn subselects. (De in() clause van Voutloos).

[Voutloos]

Je joint gewoon tegen dezelfde tabel op basis van Id. Bij de 1e tabelalias check je de 1e voorwaarde, en bij de 2e de 2e.
Is uiteraard een van de mogelijkheden, geen idee wat voor postgres het efficients is. Met mysql wint de join aanpak het 99 vd 100 keer van een dependant IN() subquery.

[Standeman]

Ah, weer wat geleerd. Ik ben een aardige db performance noob. Het enige wat ik kan is wat indexjes aanmaken! (Scheelt wel een boel)

[Spiral]

Voutloos schreef op vrijdag 05 september 2008 @ 09:49:
Je joint gewoon tegen dezelfde tabel op basis van Id. Bij de 1e tabelalias check je de 1e voorwaarde, en bij de 2e de 2e.

Ok bedankt voor de input allemaal. Ik blijf gebruik maken van INTERSECT. Als je nl. vele JOINS moet genereren op een onbekend aantal keys dan wordt de query onoverzichtelijk en wordt de code ook onoverzichtelijk. Qua performance maakt het, voor zover mij nu bekend is, niet zoveel uit en niemand heeft aangegeven dat het beter is om INTERSECT in deze situatie niet te gebruiken. Tevens maakt het de code en query overzichtelijk

[Voutloos]

Meten is weten, maak het gewoon op de mooie manier en meet of performance acceptabel is.

Mijn hints zijn wellicht teveel geinpireerd door mysql welke qua subquery performance compleet zuigt.

[pedorus]

Als het om de 'mooiheid' gaat ziet zo'n in er nog best mooi uit:

SQL:

select id from mytable 
where (key,value) in (('lengte','100'),('kleur','groen'))
group by id
having count(*)=2;

Ondersteund postgreSQL dit ook? (En hoe is de snelheid?)

(En verder is deze databasestructuur niet echt geweldig, dit ziet eruit als het heruitvinden van een tabel)

[ Voor 17% gewijzigd door pedorus op 05-09-2008 10:30 ]

[Spiral]

aha! Nu snap ik beter wat Voutloos bedoelt door het voorbeeld van pedorus. Ik dacht bij een subselect aan een subquery á la

SQL:

SELECT * FROM x WHERE id = (SELECT id FROM Y)

Dat werd niet bedoelt merk ik nu. De syntax van IN is wel begrijpelijker en door de HAVING laat je alleen items laat zien die ook het aantal keys daadwerkelijk bevatten. De keys zelf krijg ik nog wel via wat joins binnen. En ik moet even kijken als ik case sensitive kan toepassen met IN.

qua snelheid lijkt IN op het eerste gezicht sneller dan INTERSECT. Dan maak ik geen gebruik van JOINS en case insentivity bij beide queries. Ik gebruik pgAdmin 1.8.4 en ik ben niet zo bekend met analyseren van queries hiermee. (eerste maal dat ik iets met postgreSQL doe)