[SQL] Unieke selectie uit 2 tabellen zonder distinct

[Swerfer]

Als ik onderstaande SQL uitvoer, dan krijg ik keurig alle recepten te zien waar 'kaas' in de receptnaam zit of in één van de ingrediënten. Maar als ik de DISTINCT weglaat, dan heb ik zo'n 13000 rijen.

SQL:

SELECT DISTINCT
    Recipes.*
FROM
    Recipes,
    Ingredients
WHERE
    Recipes.Name LIKE '%kaas%'
OR
    Ingredients.Name LIKE '%kaas%'
AND
    Ingredients.RecipeID = Recipes.ID

Hoe kan ik er voor zorgen dat ik het gewenste resultaat krijg, zonder dat de query lang duurt omdat hij 13000 rijen als resultaat geeft waar hij een DISTINCT over heen gooit?

Ik heb al met joins lopen kl$%#ten maar ik krijg niet wat ik wil...

[Gomez12]

Wat is het probleem met de distinct? Je kan wel een group by gaan gebruiken of desnoods het opsplitsen in sub-query's. Maar uiteindelijk komt het allemaal op hetzelfde neer.

Je wilt alle recepten met in de naam kaas en je wilt alle recepten waarin een ingredient zit met de naam kaas (dat zijn er dus schijnbaar 13.000) en dan wil je daar de unieke uit.

Wil je zonder de laatste stap werken dan moet je je hele vraag omgooien, want je krijgt momenteel hoe dan ook minimaal resultaten uit recepten en uit ingredienten en daar wil je enkel de unieke uit. Dus ook al zou je met een sub-query en een max etc uit je ingredienten alleen unieke recept-id's krijgen dan alsnog heb je een distinct nodig om je resultaten uit recepten en je resultaten uit ingredienten te ontdubbelen.

Als je enige reden traagheid is dan zou ik de oorzaak ergens anders zoeken (* weghalen bijv (micro-optimalisatie), like %kaas% weghalen bijv). En dan zou ik ook gewoon de tabellen posten.

[ Voor 10% gewijzigd door Gomez12 op 12-04-2013 16:04 ]

[Swerfer]

Er zijn geen 13000 recepten met de 'kaas' of met 'kaas' in de ingrediënten.

De query geeft als resultaat het aantal ingrediënten * het aantal recepten met 'kaas' in de naam + alle recepten met 'kaas' in de ingrediënten.

Als 'kaas' in meerdere ingrediënten van één recept voorkomt, dan komen die er ook nog eens bij.

De query zit gewoon niet goed in elkaar, maar ik krijg die niet goed...

Wil je zonder de laatste stap werken dan moet je je hele vraag omgooien, want je krijgt momenteel hoe dan ook minimaal resultaten uit recepten en uit ingredienten en daar wil je enkel de unieke uit. Dus ook al zou je met een sub-query en een max etc uit je ingredienten alleen unieke recept-id's krijgen dan alsnog heb je een distinct nodig om je resultaten uit recepten en je resultaten uit ingredienten te ontdubbelen.

Dit is opzich niet een groot probleem, omdat dat maar over een beperkt aantal dubbelen gaat.

[edeboeck]

Ik zou voorstellen dat je eerst inderdaad eens controleert hoeveel records je mag verwachten:
1) tel het aantal recepten met 'kaas' in de naam
2) tel het aantal recepten waar 'kaas' één van de ingrediënten is
Dan heb je al een idee of het klopt.

Maar voor alle duidelijkheid: je hebt wel degelijk die distinct nodig, want een recept waar 'kaas' in de naam voorkomt én in de ingrediënten zal anders dubbel voorkomen.

Moest ik van jou zijn, ik zou ook haakjes plaatsen in de WHERE... nu zou het weleens verkeerd kunnen lopen. Dus: haakjes rond de OR-factoren.

[Swerfer]

7 Recepten met 'kaas'
43 Ingrediënten met 'kaas'

Dus 50 in totaal en geen 13000...

1948 Ingrediënten totaal...

7*1948=13636 <- Dit is wat er fout gaat

De haakjes maken in dit geval niet uit, maar is wel netter en overzichtelijker...

[ Voor 50% gewijzigd door Swerfer op 12-04-2013 16:14 ]

[NMe]

Swerfer schreef op vrijdag 12 april 2013 @ 16:10:
7 Recepten met 'kaas'
43 Ingrediënten met 'kaas'

Dus 50 in totaal en geen 13000...

Je vergeet dat je aan het joinen bent en er dus fictieve rijen gemaakt worden waarin die combinatie gemaakt kan worden.

Ik begrijp nog steeds niet waarom distinct hier nou het probleem is?

Je query duurt trouwens ook vooral lang omdat een LIKE-search met een wildcard vooraan geen effectief gebruik van indexes kan maken.

Maak er verder eens een expliciete join van in plaats van een impliciete met een extra where?

[ Voor 22% gewijzigd door NMe op 12-04-2013 16:16 ]

[highandstoned]

zoiets zou kunnen werken, eventueel afhankelijk van welke database maar het zou algemeen genoeg moeten zijn.

SQL:

SELECT 
    Recipes.*
FROM
    Recipes rec
WHERE
    Recipes.Name LIKE '%kaas%'
OR
    exists (select 1 from Ingredients where Ingredients.Name LIKE '%kaas%' AND  Ingredients.RecipeID = Recipes.ID)

[Swerfer]

NMe schreef op vrijdag 12 april 2013 @ 16:14:
[...]

Je vergeet dat je aan het joinen bent en er dus fictieve rijen gemaakt worden waarin die combinatie gemaakt kan worden.

Ik begrijp nog steeds niet waarom distinct hier nou het probleem is?

Stel zoals in het voorbeeld in mijn vorige post:

Er zijn 100 recepten met 'kaas' en 10000 ingrediënten totaal.

Dan geeft de query zonder distinct 100*10000 = 1000000 resultaten.

Als daar dan een distinct overheen moet, dan is de server bij 1 miljoen resultaten aan het kijken of er dubbele in voorkomen. Dat lijkt mij nogal een grote impact op de server. Laat staan als de zoektekst geen '%kaas%' is, maar '%e%', dan kunnen er exponentieel veel resultaten naar boven komen waar een distinct over heen moet.

De '%kaas%' komt namelijk van een zoekfunctie waar de gebruiker een zoekterm intikt.

[Swerfer]

highandstoned schreef op vrijdag 12 april 2013 @ 16:17:
zoiets zou kunnen werken, eventueel afhankelijk van welke database maar het zou algemeen genoeg moeten zijn.

SQL:

SELECT 
    Recipes.*
FROM
    Recipes rec
WHERE
    Recipes.Name LIKE '%kaas%'
OR
    exists (select 1 from Ingredients where Ingredients.Name LIKE '%kaas%' AND  Ingredients.RecipeID = Recipes.ID)

Deze geeft 40 resultaten. Ik denk dat dat wel klopt omdat hier alle dubbele al uit zijn. Dit komt namelijk overeen met mijn eerste query.

[NMe]

Begrijp je ook waarom zijn query veel minder resultaten oplevert dan de jouwe?

[highandstoned]

Stel zoals in het voorbeeld in mijn vorige post:

Er zijn 100 recepten met 'kaas' en 10000 ingrediënten totaal.

Dan geeft de query zonder distinct 100*10000 = 1000000 resultaten.

dat klopt natuurlijk ook niet. Ten eerste filter je ook de ingredienten op '%kaas%', dus van die 10000 blijven er dan nog bijv 100 over. Ten tweede hangt niet ieder ingredient aan elke recept met Ingredients.RecipeID = Recipes.ID
Ten derde is dit een wat vreemde opzet voor een database met deze inhoud, volgens mij is er namenlijk een N:N verband tussen recepten en ingredienten, terwijl je hier een 1:N hebt wat je waarschijnlijk "oplost" door een ingredient vele malen op te slaan....

[Swerfer]

NMe schreef op vrijdag 12 april 2013 @ 16:28:
Begrijp je ook waarom zijn query veel minder resultaten oplevert dan de jouwe?

Ja, ik zat zelf ook al met een Exist te proberen, maar kreeg dat niet goed. Ik blijf SQL lastig vinden. Waarschijnlijk omdat ik te weinig query's nodig heb waar joins en group by en dergelijke nodig zijn. Ik krijg er gewoon geen ervaring in.

Maar elke keer leer ik weer wat bij...

highandstoned schreef op vrijdag 12 april 2013 @ 16:37:
[...]


dat klopt natuurlijk ook niet. Ten eerste filter je ook de ingredienten op '%kaas%', dus van die 10000 blijven er dan nog bijv 100 over. Ten tweede hangt niet ieder ingredient aan elke recept met Ingredients.RecipeID = Recipes.ID
Ten derde is dit een wat vreemde opzet voor een database met deze inhoud, volgens mij is er namenlijk een N:N verband tussen recepten en ingredienten, terwijl je hier een 1:N hebt wat je waarschijnlijk "oplost" door een ingredient vele malen op te slaan....

Die 10000 klopt wel:

SQL:

SELECT DISTINCT 
    Recipes.* 
FROM 
    Recipes, 
    Ingredients 
WHERE 
    Recipes.Name LIKE '%kaas%'
AND 
    Ingredients.RecipeID = Recipes.ID

Ik heb even de OR weggelaten. Je krijgt dan dus ook bij elk gevonden recept alle ingrediënten omdat die in de FROM vermeld staat waar geen WHERE op de ingrediëntnamen wordt toegepast.. Je krijgt dan dus 10000en dubbele recepten als resultaat.

De tabellen zien er als volgt uit:

Recipes:

ID
Name
nog wat kolommen die er niet toe doen

Ingredients:

ID
RecipeID
Name
nog wat kolommen die er niet toe doen

Maar met de query van highandstoned ben ik tevreden.

[ Voor 13% gewijzigd door Swerfer op 12-04-2013 16:58 ]

[NMe]

[quote]Swerfer schreef op vrijdag 12 april 2013 @ 16:49:
[...]

Die 10000 klopt wel:

SQL:

Ik heb even de OR weggelaten. Je krijgt dan dus ook bij elk gevonden recept alle ingrediënten omdat die in de FROM vermeld staat waar geen WHERE op de ingrediëntnamen wordt toegepast.. Je krijgt dan dus 10000en dubbele recepten als resultaat.

Dat klopt niet en kan niet kloppen. Die query geeft als het goed is alleen de recepten terug waarin het woord kaas voorkomt in de naam van het recept, én alle ingrediënten die daarbij horen.

Uiteindelijk waren haakjes hier trouwens wel degelijk de juiste oplossing. Als dat voor jou niet werkte heb je ze geheid op de verkeerde plek gezet. Als je de join netjes uitgeschreven had met een JOIN tabelnaam ON ... dan had je óók dit probleem niet gehad.

[Gomez12]

NMe schreef op vrijdag 12 april 2013 @ 17:06:
[quote]Swerfer schreef op vrijdag 12 april 2013 @ 16:49:
[...]
Dat klopt niet en kan niet kloppen. Die query geeft als het goed is alleen de recepten terug waarin het woord kaas voorkomt in de naam van het recept, én alle ingrediënten die daarbij horen.

Perceptieprobleempje denk ik.

Hij krijgt (bij 1 recept met 20 ingredienten) 20x de receptnaam terug,dat kan overkomen als heel erg veel dubbele (helemaal als je ook nog eens meerdere recepten hebt met dezelfde naam bijv)

[JaQ]

Swerfer schreef op vrijdag 12 april 2013 @ 15:18:
Hoe kan ik er voor zorgen dat ik het gewenste resultaat krijg, zonder dat de query lang duurt omdat hij 13000 rijen als resultaat geeft waar hij een DISTINCT over heen gooit?

Het helpt als je opschrijft wat je daadwerkelijk wilt zien SQL wordt een stuk gemakkelijker als je je vraag correct en volledig omschrijft.

Door deze post lezend denk ik dat je vraag is "selecteer alle recepten waar kaas in de naam voorkomt, of een ingredient van het recept kaas in de naam heeft". Klopt dat? Zo ja, de oplossing met exists van highandstoned is een mogelijke oplossing. Je zou ook nog een query kunnen maken met een union:

SQL:

SELECT r.naam
  FROM recepten r
 WHERE r.naam LIKE '%kaas%'
UNION
SELECT r.naam
  FROM recepten r,
       ingredienten i
 WHERE r.id = i.recept_id
   AND i.naam LIKE '%kaas%'

Deze laatste performed waarschijnlijk minder goed (alhoewel dat van allerlei zaken afhankelijk is die we nog niet weten, zoals datadistributie, indexering, fysieke opslag etc.), maar is misschien beter te begrijpen voor je? (UNION maakt van de twee datasets een unieke verzameling. Automatisch ontdubbelen dus)

Gomez12 schreef op vrijdag 12 april 2013 @ 17:10:
Perceptieprobleempje denk ik.

Hij krijgt (bij 1 recept met 20 ingredienten) 20x de receptnaam terug,dat kan overkomen als heel erg veel dubbele (helemaal als je ook nog eens meerdere recepten hebt met dezelfde naam bijv)

Dat is geen perceptieprobleem, de TS krijgt het passende antwoord op de vraag die hij stelt. De dataset die TS opstelt bestaat immers uit ingrediënten en recepten, niet enkel uit recepten.

[shdx]

De originele query bevat geen haakjes, wat de leesbaarheid niet ten goede komt. NMe heeft daarin helemaal gelijk. Hoe de query, volgens mij, geinterpreteerd wordt is als volgt:

SQL:

SELECT DISTINCT 
    Recipes.* 
FROM 
    Recipes, 
    Ingredients 
WHERE 
    Recipes.Name LIKE '%kaas%' 
OR 
    (Ingredients.Name LIKE '%kaas%' 
    AND 
    Ingredients.RecipeID = Recipes.ID)

Dat lijkt misschien wel goed, maar zoals NMe al aangaf, je bent fictieve rijen aan het maken. En die filtering wordt dus uitgevoerd op je fictieve rijen, waardoor de query een ietwat andere betekenis krijgt dan wat je voor ogen had. Het is ook handig om te weten dat deze JOIN een cartesian product van de tabellen oplevert: elk item in recipe wordt gekoppeld aan elk item in ingredients. Ofwel in een voorbeeld:

Recipes  
------------- 
| ID | Name | 
-------------
| 10 | KAAS |
| 11 | ASDF |
-------------

Ingredients
------------------------
| ID | RecipeID | Name |
------------------------
| 1  |       10 | STUV |
| 2  |       11 | KAAS |
| 3  |       11 | 1234 |
------------------------

Join
-------------------------------------
| ID | Name | ID | RecipeID | Name |
------------------------------------
| 10 | KAAS | 1  |       10 | STUV |
| 10 | KAAS | 2  |       11 | KAAS |
| 10 | KAAS | 3  |       11 | 1234 |
| 11 | DEFG | 1  |       10 | STUV |
| 11 | DEFG | 2  |       11 | KAAS |
| 11 | DEFG | 3  |       11 | 1234 |
------------------------------------

Als we de originele query erbij pakken, zie je dus dat alle recepten met kaas in de naam geselecteerd worden en ook alles recepten waar het ingredient kaas inzit (het ingredient moet wel bij het juiste recept staan). We krijgen in dit voorbeeld dus 4 resultaten:

-------------------------------------
| ID | Name | ID | RecipeID | Name |
------------------------------------
| 10 | KAAS | 1  |       10 | STUV |
| 10 | KAAS | 2  |       11 | KAAS |
| 10 | KAAS | 3  |       11 | 1234 |
| 11 | DEFG | 2  |       11 | KAAS |
------------------------------------

In jouw geval betekent dat dus dat je een grote resultset krijgt waarin je gaat filter. In je orginele query (zonder de haakjes) wordt gefilterd op receptnamen met kaas erin, OF ingredienten met kaas erin die toevallig ook aan het juiste recept zijn gekoppeld. Dat wordt dus een aardig lange lijst. Zoals je zelf al aangeeft zijn dat alle recepten met kaas in de naam (ofwel 7*1948) plus alle ingredienten van recepten zonder kaas in de naam, die wel kaas als ingredient hebben. Uiteindelijk gaat daar een DISTINCT overheen en krijg je wel je juiste uitkomst. Je hebt echter gelijk dat dit niet een heel mooie manier is.
Zet je de haakjes goed neer, dan wordt de resultset al een stuk kleiner:

SQL:

SELECT 
    Recipes.* 
FROM 
    Recipes, 
    Ingredients 
WHERE 
    (Recipes.Name LIKE '%kaas%' 
OR 
    Ingredients.Name LIKE '%kaas%')
AND 
    Ingredients.RecipeID = Recipes.ID

Nu pak je dus alle recepten met kaas erin of alle ingredienten met kaas erin en koppel je de ingredienten netjes aan hun recept. Deze resultset heeft echter nog steeds een DISTINCT nodig, want nu krijg je elk recept zo vaak terug als dat het ingredienten heeft, plus elk kaas ingredient dat nog niet bij een recept was gevonden.

De query van highhandstoned haalt ook de noodzaak voor een DISTINCT weg, aangezien daarin geen dubbele elementen geselecteerd worden.