[MYSQL] Langzame query

[Verwijderd]

Er zijn uiteraard al tig topics over langzame queries, maar ik heb het volgende probleem nog niet op kunnen lossen:

Ik heb een query gemaakt die alle records uit de linkertabel haalt, die niet verbonden zijn aan een record uit de rechtertabel. In de linkertabel (GEGEVENS) staan e-mailadressen van gebruikers, in de rechtertabel (GEBOUW_OBJECT) staan woningen die door een gebruiker geplaatst zijn. Met andere woorden, ik wil alle e-mailadressen van gebruikers hebben die GEEN woning hebben geplaatst. Het gaat om de volgende query:

code:

SELECT DISTINCT email
FROM GEGEVENS
LEFT OUTER JOIN GEBOUW_OBJECT ON GEGEVENS.LID_id = GEBOUW_OBJECT.LID_id
WHERE GEBOUW_OBJECT.LID_id IS NULL
AND email IS NOT NULL AND email <> ''

De query neemt (op verschillende webservers!) een minuut of meer in beslag. Haal ik de DISTINCT weg, dan duurt de query plotseling nog maar +/- 2 seconden (tenminste, phpMyAdmin vertelt me dat, ik moet nog steeds een minuut of langer wachten op de resultpagina). Maar goed, ik heb die DISTINCT gewoon nodig, want ik wil aan elk e-mailadres natuurlijk maar één bericht sturen.

Ook als ik bijvoorbeeld de volgende query gebruik,

code:

SELECT DISTINCT email
FROM GEGEVENS
WHERE email IS NOT NULL
AND email <> ''

welke in feite hetzelfde is, maar dan zonder de JOIN, duurt de query nog maar een halve seconde. En dat is dus wel met DISTINCT (in dit geval krijg ik de resultpagina trouwens wel erg snel op m'n scherm).

Iemand een idee wat de oorzaak is van de trage verwerking van deze query? DISTINCT is dus prima, JOIN ook, maar samen duurt het een eeuwigheid. Maakt MySQL misschien eerst een resultaat waarin ALLE mogelijke combinaties staan (cartetisch heet dat volgens mij)?

Help!

[Rotjeknor]

Heb je in je Mysql database indexen aangemaakt op de velden waarop je zoekt? Dat wil wel eens een aardige slok op de borrel schelen...

[whoami]

Indexen zijn zowiezo een must.

Daarnaast vraag ik me af of je bovenstaande query niet sneller in 2 queries kunt doen:

- eerst ga je alle id's gaan ophalen van de gebruikers die een woning hebben, en daar maak je een comma-separated string van;
- dan ga je alle gebruikers ophalen die niet in dat lijstje voorkomen

dan bekom je zoiets:

code:

SELECT * FROM tabel WHERE id not in (1, 3 , 4, 5, 8, 11, 12, 37)

_{In normale DBMS'en kan je het ook in 1 query, maar goed.}

Ik vraag me trouwens af of indexen wel gebruikt worden bij een expressie als

code:

WHERE [veld] IS NOT NULL

edit:
In Oracle dus niet, en ik denk dat dat in SQL Server niet anders zal zijn.

[ Voor 18% gewijzigd door whoami op 29-12-2003 16:39 ]

[Verwijderd]

Ongelooflijk! Ik heb indices gemaakt op de vreemde sleutels (LID_id in beide tabellen) en op het veld email in GEGEVENS. Mijn query's duren nu nog maar 0,05 seconden (ipv 80-90!).

Ik heb de FAQ over indices hier op GoT doorgelezen, maar moet eerlijk zeggen dat ik het nog niet helemaal snap. Wat ik er uit begrijp is dat er niet echt vaste regels zijn voor het toevoegen van indices, en dat je er hier en daar maar wat indices in moet gooien. Op vreemde sleutels schijnt het in de meeste gevallen een goed idee te zijn.

Eigenlijk heb ik nu op elk veld wat in de query voor komt, een index gemaakt. Maar wat voor concrete nadelen zijn hier nu aan verbonden? Bepaalde query's kunnen er in performance dus op achteruit gaan, maar in welke mate? En hoe precies?

In ieder geval heel erg bedankt voor de reply's, het index-idee heeft me absoluut geholpen! Ik had niet gedacht dat het zóveel zou schelen.

Thanks!

[whoami]

Verwijderd schreef op 29 december 2003 @ 19:19:
Ik heb de FAQ over indices hier op GoT doorgelezen, maar moet eerlijk zeggen dat ik het nog niet helemaal snap. Wat ik er uit begrijp is dat er niet echt vaste regels zijn voor het toevoegen van indices, en dat je er hier en daar maar wat indices in moet gooien. Op vreemde sleutels schijnt het in de meeste gevallen een goed idee te zijn.

Kolommen waar je op zoekt/sorteert/joined zijn goede kandidaten om een index op te leggen.
Je zult wel goed moeten nagaan of een index op een bepaalde column wel nodig is, en je kan ook nagaan of het beter is om een 'composite index' (dwz een index die over meerdere velden ligt) of een 'single' index te leggen.

Eigenlijk heb ik nu op elk veld wat in de query voor komt, een index gemaakt. Maar wat voor concrete nadelen zijn hier nu aan verbonden? Bepaalde query's kunnen er in performance dus op achteruit gaan, maar in welke mate? En hoe precies?

Als je een INSERT/UPDATE/DELETE doet, worden er gegevens uit je tabel aangepast, verwijderd of toegevoegd. Dat wil dus zeggen dat het database-systeem zijn indexen moet aanpassen; dat aanpassen van de indexen (en statistiek informatie) kan een beetje op de performance wegen.
Daarom is het aan te raden dat je niet te veel (maar ook niet te weinig) indexen legt.

Verwijderd schreef op 29 december 2003 @ 19:19:
In ieder geval heel erg bedankt voor de reply's, het index-idee heeft me absoluut geholpen! Ik had niet gedacht dat het zóveel zou schelen.

Thanks!

Vergelijk het met een boek, of een atlas.
Als jij in een atlas een bepaalde stad / land wilt terugvinden op een kaart, dan ga je ook eerst in de index/register gaan kijken welke kaart je nodig hebt, en in welk vak op die kaart die stad of dat land zich bevindt. Dat gaat ook veel sneller dan als je ieder vak op iedere kaart in die atlas apart zou gaan bekijken.

[ Voor 19% gewijzigd door whoami op 29-12-2003 19:26 ]

[Verwijderd]

Ah kijk, nu wordt het een stuk duidelijker. Een index is dus letterlijk een inhoud in je database, een soort 'cache' waaruit je database sneller gegevens kan halen.

En als je dus gegevens op kolommen met een index gaat wijzigen, moet de index dus ook gewijzigd worden, wat performance kost.

Eigenlijk best simpel

[whoami]

Verwijderd schreef op 29 december 2003 @ 19:28:
Ah kijk, nu wordt het een stuk duidelijker. Een index is dus letterlijk een inhoud in je database, een soort 'cache' waaruit je database sneller gegevens kan halen.

Geen cache nee, eerder een 'lijst' met verwijzingen waar (op welke page in de tabel) de gegevens te vinden zijn

[Verwijderd]

Nouja goed, een gedeelte waarin gegevens gemakkelijker (sneller) voor de database te bereiken zijn. Een register van een atlas ja