[php/mysql] Trage Query

[Tinuske]

Het volgende

Voor een relatiebeheer programmaatje wil ik de id's ophalen uit de database:

De Volgende query duurt ongeveer 0,0007 sec (4000 records)

SELECT RelatieID, p.BedrijfID FROM relatie r, bedrijf p where r.BedrijfID = p.BedrijfID order by RelatieVoornaam

Bovenstaande query geeft echter geen bedrijven terug die geen relaties hebben.
Om die wel terug te krijgen komen we met de volgende query :

SELECT RelatieID, p.BedrijfID FROM relatie r RIGHT JOIN bedrijf p ON r.BedrijfID = p.BedrijfID ORDER BY RelatieVoornaam

Deze query duurt ongeveer 10 sec! (5000 records)

Dit is natuurlijk veels te traag voor een webpagina.
Hoe valt dit op te lossen ?

[Wolfboy]

Doe eerst eens een explain over de query en kijk of er uberhaupt indexen gebruikt worden, zo niet ga dan eens een paar indexen op de tabel zetten

edit:
Zie ook de faq hiervoor: P&W FAQ - SQL

[ Voor 30% gewijzigd door Wolfboy op 16-12-2005 09:33 ]

[frickY]

De juiste join gebruiken

SQL:

SELECT
  r.RelatieID, 
  p.BedrijfID
FROM
  relatie r 
LEFT JOIN 
  bedrijf p ON r.BedrijfID = p.BedrijfID 
ORDER BY r.RelatieVoornaam

En natuurlijk indexen op r.BedrijfID, p.BedrijfID en r.RelatieVoornaam

[ Voor 18% gewijzigd door frickY op 16-12-2005 09:34 ]

[Sybr_E-N]

Kun je ons ook iets vertellen over je datastructuur, heb je bijvoorbeeld primary keys / indexen ingesteld. Wat je ook kan doen is een EXPLAIN query opstellen, dan analyseert mysql jouw query. Heb je de optimalization guide van mysql zelf online doorgenomen?

[NMe]

Er staat me inderdaad iets bij van right outer joins die in MySQL trager zijn dan left outer joins, omdat right joins geen indexen konden gebruiken, maar hoezeer die informatie recent is weet ik niet. De oplossing van frickY zou dus inderdaad een aardige performanceboost op moeten leveren.

[Wolfboy]

-NMe- schreef op vrijdag 16 december 2005 @ 09:36:
De oplossing van frickY zou dus inderdaad een aardige performanceboost op moetenkunnen leveren.

Dat is inderdaad versie afhankelijk

[frickY]

Dat is niet de enige reden dat ik LEFT JOIN opperde... volgens mij werkt RIGHT JOIN namelijk verkeerd om voor het doel dat de TS wilt.
Tenzij ik het doel van de TS verkeerd gok, want dat laat hij in het midden.

Wil je alle relaties selecteren, met daarbij een eventueel bedrijf?
Of wil je alle bedrijven selecteren, met daarbij een eventuele relatie?

Je voorbeeld query doet namelijk het eerste vermoeden, maar "Bovenstaande query geeft echter geen bedrijven terug die geen relaties hebben" spreekt dit weer tegen

[ Voor 92% gewijzigd door frickY op 16-12-2005 10:12 ]

[edeboeck]

Tinuske schreef op vrijdag 16 december 2005 @ 09:31:
(snip)
Bovenstaande query geeft echter geen bedrijven terug die geen relaties hebben.
Om die wel terug te krijgen komen we met de volgende query :
(snip)

TS wil dus ook de bedrijven zonder relaties hebben.

frickY schreef op vrijdag 16 december 2005 @ 09:33:
De juiste join gebruiken

SQL:

SELECT
  r.RelatieID, p.BedrijfID
FROM
  relatie r LEFT JOIN bedrijf p ON r.BedrijfID = p.BedrijfID 
ORDER BY r.RelatieVoornaam

En natuurlijk indexen op r.BedrijfID, p.BedrijfID en r.RelatieVoornaam

Dit geeft de relaties ZONDER bedrijven ... is dus het omgekeerde van wat TS vraagt.
Wat het indexeren betreft, ben ik het wel met je eens.

frickY schreef op vrijdag 16 december 2005 @ 10:08:
Dat is niet de enige reden dat ik LEFT JOIN opperde... volgens mij werkt RIGHT JOIN namelijk verkeerd om voor het doel dat de TS wilt.
Maar ik moet daarbij zeggen dat ik de exacte verschillen tussen de left en right joins in MySQL niet helemaal zie.

Niet correct dus.

-NMe- schreef op vrijdag 16 december 2005 @ 09:36:
Er staat me inderdaad iets bij van right outer joins die in MySQL trager zijn dan left outer joins, omdat right joins geen indexen konden gebruiken, maar hoezeer die informatie recent is weet ik niet. De oplossing van frickY zou dus inderdaad een aardige performanceboost op moeten leveren.

Als er idd een performanceboost van LEFT tov RIGHT join is, dan zou de code er dus als volgt moeten uitzien:

SQL:

SELECT
  r.RelatieID, p.BedrijfID
FROM
  bedrijf p LEFT JOIN relatie r ON p.BedrijfID = r.BedrijfID 
ORDER BY r.RelatieVoornaam

[edeboeck]

frickY schreef op vrijdag 16 december 2005 @ 10:08:
Dat is niet de enige reden dat ik LEFT JOIN opperde... volgens mij werkt RIGHT JOIN namelijk verkeerd om voor het doel dat de TS wilt.
Tenzij ik het doel van de TS verkeerd gok, want dat laat hij in het midden.

Wil je alle relaties selecteren, met daarbij een eventueel bedrijf?
Of wil je alle bedrijven selecteren, met daarbij een eventuele relatie?

Je voorbeeld query doet namelijk het eerste vermoeden, maar "Bovenstaande query geeft echter geen bedrijven terug die geen relaties hebben" spreekt dit weer tegen

Zijn eerste query geeft enkel bedrijven met relaties terug (dus gesteld dat er relaties zijn zonder bedrijf, krijgt hij die niet terug). TS wil ook de bedrijven die geen relaties hebben (zonder dus). Zijn voorbeeld code (de right join) doet dit wel degelijk. Daar is enkel het performance-probleem.

[ Voor 6% gewijzigd door edeboeck op 16-12-2005 10:19 ]

[Tinuske]

Even een vereenvoudige weergave van de database :

BedrijfsTabel:
BedrijfID
BedrijfsNaam

RelatieTabel:
RelatieID
BedrijfID
RelatieAchternaam
RelatieVoornaam

De geopperde (eerste) left join, geeft geen bedrijven terug zonder relaties en die willen we dus ook hebben.
Er ligt al een index op RelatieVoornaam hij blijft echter traag.

Ik ga aan de gang met de eplain van mysql en probeer even de 2e geopperde left join

[ Voor 7% gewijzigd door Tinuske op 16-12-2005 10:42 ]

[frickY]

Als TS alle bedrijven wilt, ongeacht wel of geen relatie, snap ik niet waarom hij uit de relatie tabel selecteerd en de bedrijven tabel hierop joint
Dan zou hij de query van edeboeck moetenkunnen gebruiken.

edeboeck schreef op vrijdag 16 december 2005 @ 10:16:

SQL:

SELECT
  r.RelatieID, p.BedrijfID
FROM
  bedrijf p LEFT JOIN relatie r ON p.BedrijfID = r.BedrijfID 
ORDER BY r.RelatieVoornaam

[ Voor 42% gewijzigd door frickY op 16-12-2005 11:46 ]

[edeboeck]

frickY schreef op vrijdag 16 december 2005 @ 11:45:
Als TS alle bedrijven wilt, ongeacht wel of geen relatie, snap ik niet waarom hij uit de relatie tabel selecteerd en de bedrijven tabel hierop joint
Dan zou hij de query van edeboeck moetenkunnen gebruiken.

[...]

Ik kan geen gedachten lezen, maar het lijkt me best wel mogelijk dat hij hier wel enkel id's selecteert, maar in werkelijkheid een volledige lijst wil van alle bedrijven met per bedrijf de bijhorende relaties (in dat geval wil hij elk bedrijf in de lijst, ook als er voor dat bedrijf geen relaties zijn).

[whoami]

Heb je wel een index op bedrijfid in je relatie-tabel ?