[MySQL] Multiple INNER JOIN i.c.m. LIKE

[DeepFreeze.NL]

Ik zit nu al een tijdje te stoeien met een MySQL zoek query (LIKE) in combinatie met een INNER JOIN.
Het probleem waar ik tegen aanloop is dat alleen de nummers in "tabel1" gevonden worden, maar niet die in "tabel2".

Zie hieronder voor een vereenvoudigd voorbeeld (met fictieve tabelnamen).

MySQL:

SELECT tabel_klant.id, tabel_klant.achternaam, tabel1.nummer AS nummer_1, tabel2.nummer AS nummer_2
FROM tabel_klant
INNER JOIN tabel1 ON ( tabel_klant.id = tabel1.klant_id )
INNER JOIN tabel2 ON ( tabel_klant.id = tabel2.klant_id )
WHERE tabel_klant.actief = 'Ja'
AND (
tabel_klant.achternaam LIKE '%query%'
OR tabel1.nummer LIKE '%query%'
OR tabel2.nummer LIKE '%query%'
)

De nummers in tabel1 en tabel2 kunnen hetzelfde zijn.

Wat doe ik fout?

[Verwijderd]

De INNER JOIN van tabel1 levert waarschijnlijk geen resultaat op, waardoor die hele row komt te vervallen.

In deze situatie kun je beter LEFT JOIN doen met dan eventueel een WHERE-clause die de lege rows elimineert:

SQL:

WHERE tabel1.nummer IS NOT NULL OR tabel2.nummer IS NOT NULL

Oftewel:

SQL:

SELECT tabel_klant.id, tabel_klant.achternaam, tabel1.nummer AS nummer_1, tabel2.nummer AS nummer_2
FROM tabel_klant
LEFT JOIN tabel1 ON ( tabel_klant.id = tabel1.klant_id )
LEFT JOIN tabel2 ON ( tabel_klant.id = tabel2.klant_id )
WHERE tabel_klant.actief = 'Ja'
AND (
   tabel_klant.achternaam LIKE '%query%'
   OR tabel1.nummer LIKE '%query%'
   OR tabel2.nummer LIKE '%query%'
)
AND (
   tabel1.nummer IS NOT NULL
   OR tabel2.nummer IS NOT NULL
)

Dergelijke LIKE queries zijn overigens niet erg geschikt voor enorme tabellen, omdat ze geen indexen ondersteunen. Dus als je het over meer dan 100 klanten hebt, zou ik toch een andere implementatie overwegen. Performance wise zul je nog wel uit kunnen met 5.000 klanten op een gemiddelde VPS, maar toch... Het is niet echt een lekkere query.

[ Voor 62% gewijzigd door Verwijderd op 10-05-2012 13:27 ]

[DeepFreeze.NL]

Verwijderd schreef op donderdag 10 mei 2012 @ 13:20:
De INNER JOIN van tabel1 levert waarschijnlijk geen resultaat op, waardoor die hele row komt te vervallen.

In deze situatie kun je beter LEFT JOIN doen met dan eventueel een WHERE-clause die de lege rows elimineert:

SQL:

WHERE tabel1.nummer IS NOT NULL OR tabel2.nummer IS NOT NULL

Oftewel:

SQL:

SELECT tabel_klant.id, tabel_klant.achternaam, tabel1.nummer AS nummer_1, tabel2.nummer AS nummer_2
FROM tabel_klant
LEFT JOIN tabel1 ON ( tabel_klant.id = tabel1.klant_id )
LEFT JOIN tabel2 ON ( tabel_klant.id = tabel2.klant_id )
WHERE tabel_klant.actief = 'Ja'
AND (
   tabel_klant.achternaam LIKE '%query%'
   OR tabel1.nummer LIKE '%query%'
   OR tabel2.nummer LIKE '%query%'
)
AND (
   tabel1.nummer IS NOT NULL
   OR tabel2.nummer IS NOT NULL
)

Je hebt gelijk! Ik ben er zojuist zelf achter gekomen dat een LEFT JOIN voor het gewenste resultaat zorgt. Bedankt in ieder geval!

[DeepFreeze.NL]

Dergelijke LIKE queries zijn overigens niet erg geschikt voor enorme tabellen, omdat ze geen indexen ondersteunen. Dus als je het over meer dan 100 klanten hebt, zou ik toch een andere implementatie overwegen. Performance wise zul je nog wel uit kunnen met 5.000 klanten op een gemiddelde VPS, maar toch... Het is niet echt een lekkere query.

De komende jaren kom ik hier nog wel mee uit de voeten. Zijn er eenvoudige methodes waarmee het beter kan? Of moet ik een aparte tabel maken voor deze zoekopdracht? Mocht er geen eenvoudig methode zijn dan wacht ik het wel al totdat ik performance problemen ervaar en dan kijk ik er nog wel naar .

[Verwijderd]

DeepFreeze.NL schreef op donderdag 10 mei 2012 @ 13:40:
[...]


De komende jaren kom ik hier nog wel mee uit de voeten. Zijn er eenvoudige methodes waarmee het beter kan? Of moet ik een aparte tabel maken voor deze zoekopdracht? Mocht er geen eenvoudig methode zijn dan wacht ik het wel al totdat ik performance problemen ervaar en dan kijk ik er nog wel naar .

Indexing is wel mogelijk als je de LIKE niet met een wildcard begint: LIKE 'query%'

[Gomez12]

DeepFreeze.NL schreef op donderdag 10 mei 2012 @ 13:40:
[...]
De komende jaren kom ik hier nog wel mee uit de voeten. Zijn er eenvoudige methodes waarmee het beter kan? Of moet ik een aparte tabel maken voor deze zoekopdracht? Mocht er geen eenvoudig methode zijn dan wacht ik het wel al totdat ik performance problemen ervaar en dan kijk ik er nog wel naar .

Even geen idee wat die andere tabellen zijn, maar mits die achternaam geen text-kolom of een blob is (maar bijv een verstandig uitgekozen varchar lengte veld) dan kan je heel erg ver gaan met een like %query%.

Er zijn alternatieve manieren om like %query% te doen, maar die zijn ofwel met nadelen (standaard fulltext indexering zoekt enkel woorden geen delen van woorden) of het zijn "erg grote" dingen om op te zetten (solr / sphinx / lucene etc) maar die worden veelal eerder ingezet om stukken tekst door te zoeken dan om achternamen door te zoeken.

Hoeveel achternamen ga je nu exact krijgen in 10 jaar? 17 miljoen qua NL? Als het een zinnige veldlengte heeft past het alsnog snel binnen die query.
Het zijn geen tekst-velden van 64k die oneindig in aantal kunnen zijn.