[mysql] left join icm koppel tabel

[Martine]

Hoihoi Tweakers!

Onderaan deze post staat de uitleg welke velden de verschillende tabellen allemaal gebruiken, het probleem is als volgt.

Op mijn website staan allemaal foto's, elke mediabase-item bevat een titel, mediainfo (omschrijving) en minimaal een foto. De filename van de foto of meerdere foto's staan in de table mediabase_items.

Elk mediabase-item wordt voorzien van een label (tag), deze tag staat in de tabel tags, het kan voorkomen als een mediabase-item meer dan een label heeft, daarom heb ik er een tags_koppel bij geplaatst.

Nu heb ik een zoekfunctie gemaakt, werkt prima, maar is nog niet helemaal naar wens.

Voorbeeld
Er is een mediabase-item met als titel 'jongen valt in het water', en heeft als verhaaltje 'de jongen viel met fiets en al in het water vlakbij zijn school'.

De labels van dit item worden: fiets, water, zwemmen

Er wordt gezocht op 'zwemmen', dan zal hij dit item niet vinden, omdat zwemmen niet in het titel en niet in de tekst voorkomt. Daarom heb ik het onderstaande query bedacht, nu werkt het prima, maar zit ik gewoon te wachten voordat er resultaat uit komt.

Wat doe ik verkeerd en hoe kan dit opgelost worden?

SQL:

SELECT
  mediabase.id,
  mediabase.title,
  mediabase.mediainfo,
  mediabase_items.filename
FROM (
  mediabase,
  (SELECT itemid FROM tags_koppel LEFT JOIN tags ON tags_koppel.tagsid =
  tags.id WHERE tags.name LIKE '%zwemmen%') AS t
)
LEFT JOIN
  mediabase_items ON mediabase.id = mediabase_items.mediaid
WHERE
  mediabase.active = 1
  AND mediabase.id = t.itemid
  OR
  mediabase.active = 1
  AND (mediabase.title LIKE '%zwemmen%' OR mediabase.mediainfo
  LIKE '%zwemmen%')
GROUP BY mediabase.id
ORDER BY mediabase.post_date DESC

table: mediabase
id
active (zichtbaar of niet zichtbaar voor bezoekers)
title
mediainfo
post_date

table: mediabase_items
id
mediaid
filename

table: tags
id
name

table: tags_koppel
itemid (verwijzing naar mediabase.id)
tagsid

[dev10]

Tip: indexen.

En mag ik vragen waarom een GROUP BY doet op een mediabase.id? Het lijkt mij dat mediabase.id uniek is en dat je daar dan ook niet op hoeft te groeperen.

[Voutloos]

Je probleem is dus met name hoe lang de query duurt? Explain is je vriend.

Alvast nogwat tips: vermijd een wildcard aan met name het begin van een LIKE zoekterm en join tags_koppel netjes in 1x met fatsoenlijke on clause. Wellicht wil je extra controleren of je where clause met z'n combinatie van AND en OR clauses wel helemaal klopt, of om dit gewoon voor de lezer makkelijker te maken, misschien een setje haakjes uit de kast trekken.

Union ipv OR kan ook veel schelen, met name als je mysql < 5 gebruikt. Vermeld dus altijd de mysql versie en explain output bij performance vragen.

letinon schreef op dinsdag 13 mei 2008 @ 22:09:
En mag ik vragen waarom een GROUP BY doet op een mediabase.id? Het lijkt mij dat mediabase.id uniek is en dat je daar dan ook niet op hoeft te groeperen.

Met zoeken op %e% kunnen heel goed meerdere tags matchen, maar dat moet je iets anders oplossen (betere group by, een ordinaire distinct of een andere query opbouw).

[ Voor 7% gewijzigd door Voutloos op 13-05-2008 22:22 ]

[Gomez12]

Doe eens een explain...
Geen like %...% gebruiken, dit betekent geen indexen.
Zie je search als iets aparts. Wat je nu gewoon zegt is zoek alle data zonder indexen naar de volgende tekst... Of gebruik een fulltext-engine ( dan valt het nog redelijk in te dammen ) of gebruik een standaard zoekmachine, of maak zelf een zoekmachine door alle relevante woorden in een losse tabel te zetten en daar een koppeltabel aan te verbinden die dan aangeeft wat getoond moet worden...
Beperk je zoekwaardes door alleen in bepaalde gedeeltes te zoeken, of alleen je tags of alleen je posts. Scheelt je toch weer 2 tabellen doorzoeken zonder index.
Schrijf je inner joins uit.... ( stukje persoonlijke frustratie, maar is imho veel en veel duidelijker dan komma's
Doorzoek alleen de tags die active zijn ( is een inner join op een int ( neem ik aan ) en scheelt je al een gedeelte van je tags te doorzoeken )
Waarom een left_join op mediabase_items, ik neem aan dat er altijd een item moet zijn dus gewoon een inner join.
Persoonlijk zou ik bijv. mediabase.title en mediabase.mediainfo strippen en in een losse kolom zetten met een fulltext index erop. Scheelt je een OR, scheelt je een like %...%
Waar is die group by voor nodig? je mediabase heeft toch maar 1 id, alle inner joins geven toch ook maar 1 mediabase.id terug. Dus waarom die group by...

Btw, wat gebeurt er als je als zoekwaarde alleen een e opgeeft?

En als laatste, maar dit is puur gokwerk zonder de onderliggende databasegrootte te kennen, soms kan het nuttiger zijn om je itemid's uit je tags eerst op te halen in een losse query en daarna in php door te geven aan een je 2e query ( dus niet in een foreach loop, maar puur 1e query uitvoeren, 2e query uitvoeren, resultaat tonen ) maar dit hangt van je mysql geheugen af, gaat het om een paar duizend tags dan is het niet de moeite waard.

Voutloos schreef op dinsdag 13 mei 2008 @ 22:12:
[...]
Met zoeken op %e% kunnen heel goed meerdere tags matchen, maar dat moet je iets anders oplossen (betere group by, een ordinaire distinct of een andere query opbouw).

meerdere tags kunnen matchen, maar als je het goed opzet krijg je maar 1 media-id terug per mediaid uit de mediabase tabel.
Uberhaupt klopt de group by volgens sql al niet ( volgens mysql wel, maar gooi eens meerdere filename bij 1 mediaid en je loopt tegen undefined behaviour aan )
Of je groupt by op alle velden die je niet met aggegrated functies naar boven haalt of je gebruikt group by niet ( ik vind dit nog steeds een van de smerigste dingen van mysql, het haalt imho het hele idee van een relationele database onderuit, je stopt er iets in, je haalt er iets uit met een involledige group by en het is niet hetzelfde, je query is ook niet fout volgens mysql )

[ Voor 20% gewijzigd door Gomez12 op 13-05-2008 22:34 ]

[Martine]

Er wordt gebruik gemaakt van mysql versie 5.0.37

En mag ik vragen waarom een GROUP BY doet op een mediabase.id? Het lijkt mij dat mediabase.id uniek is en dat je daar dan ook niet op hoeft te groeperen.

Omdat de filename opgehaald moet worden worden alle items (mediabase.id = mediabase_items.mediaid) aan elkaar gelinkt en worden meer van dezelfde items weergegeven.

Btw, wat gebeurt er als je als zoekwaarde alleen een e opgeeft?

Niets! Er wordt gekeken als het zoekwoord langer is dan 3 tekens, er kan ook niet gezocht worden op woorden zoals, woorden zoals hij, wij, kip, tok zijn ook uitgesloten.

Nu bedenk ik me als ik ook nog wel locaties aan het hele zootje toe wil voegen, hoe kan ik dat dan het beste doen? Op dezelfde manier als ik met de tags heb gedaan?

Verder, super reactie, ik ga me er morgen even beter in verdiepen, iig bedankt!

[ Voor 36% gewijzigd door Martine op 13-05-2008 23:16 . Reden: helft vergeten.. ]

[Gomez12]

Martine schreef op dinsdag 13 mei 2008 @ 23:01:
[...]
Omdat de filename opgehaald moet worden worden alle items (mediabase.id = mediabase_items.mediaid) aan elkaar gelinkt en worden meer van dezelfde items weergegeven.

En welke filename wordt nu dan getoond? De 1e, de laatste, een "willekeurige"?

Nu bedenk ik me als ik ook nog wel locaties aan het hele zootje toe wil voegen, hoe kan ik dat dan het beste doen? Op dezelfde manier als ik met de tags heb gedaan?

Dan vertraag je het waarschijnlijk nog eens met een factor 3 of 4.
Doe nu eerst maar een explain, en onthoud dan dat je geen filesorts wil hebben, je intermidiaire results wil je zo laag mogelijk hebben ( hoe meer results hoe meer geheugen het kost )

[Martine]

Nu wordt steeds de eerste foto weergegeven. Ik heb er een printscreen van gemaakt, explain query

[Voutloos]

Geen keys en een filesort. Lees wat over indexen en over hoe je de output van explain moet interpreteren. Zoals je explain nu is, heb je uberhaupt geen enkele optimalisatie of fatsoenlijke index aangebracht, dus mag je eerst wat aan je kennis op dat gebied gaan doen.

Martine schreef op woensdag 14 mei 2008 @ 01:55:
Nu wordt steeds de eerste foto weergegeven.

Dat berust tot nu toe op pure toeval, en dat wil je niet. Zie P&W FAQ - SQL en de note over mysql bij de sectie group by voor de uitleg wat er fout gaat.

[ Voor 39% gewijzigd door Voutloos op 14-05-2008 08:05 ]

[Martine]

Bedankt voor jullie hulp, ik laat het eerst voorwat het is. Een beetje database uitlezen lukt wel maar dit gaat me te ver.

Als iemand zich geroepen voelt om mij te helpen met de search of voor mij dit zoekertje wil maken, mag hij/zij zich melden, desnoods tegen betaling.