[MySQL] Exclusive UNION?

[Oberon]

Functioneel: een gebruiker kan in een zoekvak door middel van een kernwoord/en organisaties opzoeken. Dit kernwoord kan de naam van de organisatie zijn, maar ook een rubriek/specialisatie waar de organisatie aangelinked is, eventuele aliassen (denk aan meervoudsvormen/synoniemen/andere spelling/...) van deze rubriek, beroepen gelinked aan deze rubrieken en aliassen van deze beroepen

De gebruiker kan bijvoorbeeld in hetzelfde zoekvak zoeken op "Garage Janssens", "reparaties", "garagist", enz...

Na lang zoeken en benchmarken heb ik er uiteindelijk een zeer performante query voor geschreven:

SQL:

(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON O.OrgID = OI.OrgID
INNER JOIN tblInterest I ON OI.InterestID = I.InterestID
WHERE I.Name LIKE '<%zoektekst%>'
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblInterest_Alias IA ON IA.InterestID = OI.InterestID
INNER JOIN tblAlias A ON A.AliasID = IA.AliasID
WHERE A.Name LIKE '<%zoektekst%>'
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblInterest I ON I.InterestID = OI.InterestID
INNER JOIN tblOccupation_Interest OCI ON OCI.InterestID = I.InterestID
INNER JOIN tblOccupation OC ON OC.OccupationID = OCI.OccupationID
WHERE OC.Name LIKE '<%zoektekst%>'
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblOccupation_Interest OCI ON OCI.InterestID = OI.InterestID
INNER JOIN tblOccupation_Alias OCA ON OCA.OccupationID = OCI.OccupationID
INNER JOIN tblAlias A ON A.AliasID = OCA.AliasID
WHERE A.Name LIKE '<%zoektekst%>'
)
UNION
(
SELECT * FROM tblOrganisation O
WHERE Name LIKE '<%zoektekst%>'
) ORDER BY Name;

MAAR nu zou ik graag hebben dat de gebruiker ook een postcode kan opgeven met een radius waarin gezocht kan worden, omdat de basisquery zoveel organisaties teruggeeft die niet relevant zijn voor de gebruiker.

Het verkrijgen van de postcodes binnen een bepaalde radius is vrij eenvoudig:

SQL:

SELECT x2.PostalCode
FROM tblplace x1, tblplace x2
WHERE x1.PostalCode = '<postcode>'
AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>

Het opzoeken van organisaties binnen een bepaalde radius is dus ook niet veel moeilijker:

SQL:

SELECT * FROM tblOrganisation
WHERE PostalCode IN (
SELECT x2.PostalCode
FROM tblplace x1, tblplace x2
WHERE x1.PostalCode = '<postcode>'
AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>)

Idealiter was er zoiets dat een echte UNIE (enkel de rijen overhouden die beide tabellen gemeen hebben) doet tussen twee queries want UNION doet eigenlijk niet veel meer dan twee queries mergen en de dubbels eruithalen (by default). Een soort van EXCLUSIVE UNION dus.

Ik heb dus de volgende query geschreven om dit te omzeilen. Het werkt, maar het is lelijk (maar dat vind ik niet zo erg) en traag (dat vind ik véél erger). Dan weer niet verschrikkelijk traag (tussen de 1 à 2 seconden), maar ik heb het gevoel dat dit sneller moet kunnen.

Mijn uiteindelijke query:

SQL:

(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON O.OrgID = OI.OrgID
INNER JOIN tblInterest I ON OI.InterestID = I.InterestID
WHERE I.Name LIKE '<%zoektekst%>' AND O.PostalCode IN (
SELECT x2.PostalCode
FROM tblplace x1, tblplace x2
WHERE x1.PostalCode = '<postcode>'
AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>)
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblInterest_Alias IA ON IA.InterestID = OI.InterestID
INNER JOIN tblAlias A ON A.AliasID = IA.AliasID
WHERE A.Name LIKE '<%zoektekst%>' AND O.PostalCode IN (
SELECT x2.PostalCode
FROM tblplace x1, tblplace x2
WHERE x1.PostalCode = '<postcode>'
AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>)
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblInterest I ON I.InterestID = OI.InterestID
INNER JOIN tblOccupation_Interest OCI ON OCI.InterestID = I.InterestID
INNER JOIN tblOccupation OC ON OC.OccupationID = OCI.OccupationID
WHERE OC.Name LIKE '<%zoektekst%>' AND O.PostalCode IN (
SELECT x2.PostalCode
FROM tblplace x1, tblplace x2
WHERE x1.PostalCode = '<postcode>'
AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>)
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblOccupation_Interest OCI ON OCI.InterestID = OI.InterestID
INNER JOIN tblOccupation_Alias OCA ON OCA.OccupationID = OCI.OccupationID
INNER JOIN tblAlias A ON A.AliasID = OCA.AliasID
WHERE A.Name LIKE '<%zoektekst%>' AND O.PostalCode IN (
SELECT x2.PostalCode
FROM tblplace x1, tblplace x2
WHERE x1.PostalCode = '<postcode>'
AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>)
)
UNION
(
SELECT * FROM tblOrganisation
WHERE Name LIKE '<%zoektekst%>' AND PostalCode IN (
SELECT x2.PostalCode
FROM tblplace x1, tblplace x2
WHERE x1.PostalCode = '<postcode>'
AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>)
);

Pfoe

[ Voor 1% gewijzigd door Oberon op 20-11-2009 21:52 . Reden: Kleine correctie ]

[RobertMe]

Eerste wat in mij op kwam, maak een temporary table om alle organisaties binnen de regio in te zetten, dan kun je altijd die tabel aanspreken in de plaats van tich keer filteren op de organisaties binnen de regio.

Hoe je dit precies moet doen moet je even uitzoeken, want ik heb ook nog nooit temporary tables gebruikt.

[Voutloos]

Pak je bovenste query, gooien er haken om heen en zet het als derived table neer en join het tegen tblplace. Als je daar dan de EXPLAIN van bekijkt, ben je al een hoop verder.

Overigens is een union een union, wat jij bedoelt heet een doorsnede.

[RobIII]

^ Wat hij zegt. En volgens mij kan je bovenste query nog stukken korter; zo op 't oog ben je met een like <bla> or like <bla> in de where clause en left join i.p.v. inner joins al stukken korter en voordeliger uit.

[ Voor 3% gewijzigd door RobIII op 20-11-2009 21:39 ]

[Oberon]

SQL:

SELECT *
FROM (
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON O.OrgID = OI.OrgID
INNER JOIN tblInterest I ON OI.InterestID = I.InterestID
WHERE I.Name LIKE '<%zoektekst%>'
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblInterest_Alias IA ON IA.InterestID = OI.InterestID
INNER JOIN tblAlias A ON A.AliasID = IA.AliasID
WHERE A.Name LIKE '<%zoektekst%>'
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblInterest I ON I.InterestID = OI.InterestID
INNER JOIN tblOccupation_Interest OCI ON OCI.InterestID = I.InterestID
INNER JOIN tblOccupation OC ON OC.OccupationID = OCI.OccupationID
WHERE OC.Name LIKE '<%zoektekst%>'
)
UNION
(
SELECT O.*
FROM tblOrganisation O
INNER JOIN tblOrganisation_Interest OI ON OI.OrgID = O.OrgID
INNER JOIN tblOccupation_Interest OCI ON OCI.InterestID = OI.InterestID
INNER JOIN tblOccupation_Alias OCA ON OCA.OccupationID = OCI.OccupationID
INNER JOIN tblAlias A ON A.AliasID = OCA.AliasID
WHERE A.Name LIKE '<%zoektekst%>'
)
UNION
(
SELECT * FROM tblOrganisation O
WHERE Name LIKE '<%zoektekst%>'
)
)
WHERE PostalCode IN (
SELECT x2.PostalCode
FROM tblplace x1, tblplace x2
WHERE x1.PostalCode = '<postalcode>'
AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>)

Werkt fantastisch!

Voutloos schreef op vrijdag 20 november 2009 @ 21:36:
Overigens is een union een union, wat jij bedoelt heet een doorsnede.

Daar heb je volledig gelijk in! Een INTERSECTION kon ik dus goed gebruiken

RobIII schreef op vrijdag 20 november 2009 @ 21:39:
^ Wat hij zegt. En volgens mij kan je bovenste query nog stukken korter; zo op 't oog ben je met een like <bla> or like <bla> in de where clause en left join i.p.v. inner joins al stukken korter en voordeliger uit.

Wel ik deed het eerst met OR's ipv UNIONs (en dan had ik idd een veel kortere en elegantere query), maar in de praktijk blijkt in dit geval met UNIONs werken veel sneller.

[cariolive23]

Tip: Gooi de berekeningen uit je query maar zet het resultaat van deze berekeningen in een aparte kolom. Deze kolom laat je onderhouden door een trigger. Dit heeft als enorm voordeel dat niet iedere query weer voor alle records de berekening moet uitvoeren. Dit kan flinke performance winst opleveren.

SQL:

AND ACOS(SIN(x1.LatitudeRadians) * SIN(x2.LatitudeRadians) + COS(x1.LatitudeRadians) * COS(x2.LatitudeRadians) * COS(x2.LongitudeRadians - x1.LongitudeRadians)) * 6378 <= <radius>)

Of:

SQL:

AND jouw_nieuwe_kolom_met_uitkomsten <= <radius>)

Ook MySQL kent triggers, sinds versie 5.0

[Voutloos]

Op zich leuke tip, maar in de topicstart staat dat postcode en radius user input zijn.

[cariolive23]

Voutloos schreef op zaterdag 21 november 2009 @ 13:50:
Op zich leuke tip, maar in de topicstart staat dat postcode en radius user input zijn.

Ja, en? De postcode staat elders in de WHERE en de radius staat in de vergelijking op de voorbewerkte data. Dat gaat dus helemaal goed.

Misschien heb je meerdere kolommen nodig om de voorbewerkte data in te zetten, maar je hoeft echt niet iedere keer opnieuw te gaan berekenen wat de uitkomst is van SIN(x1.LatitudeRadians), dat kun je prima vooraf doen. Dat scheelt je een hele berg berekeningen wanneer je de SELECT uitvoert.

[Voutloos]

In je 1e post wek je toch echt de indruk de resultaat van de gehele berekening op te willen slaan, ipv enkel de SIN() calls.