[sql] query-joins probleem (WHERE clause loopt vast?)

[Verwijderd]

offtopic:
Had wat moeite om onderstaande situatie uit te leggen, dus succes met het `puzzelen` als u daar niet door afgeschrokken bent...

Ik heb een aardig complexe relationele database. Om het probleem te begrijpen zal ik even uitleggen welke data ik aan het selecteren ben;

- In eerste instantie zijn dit onderdelen (uit de tabel ap_ond_archief) uit het archief.
- Deze onderdelen zijn gerelateerd aan orginele onderdelen (uit de tabel ap_onderdelen) en ook daarvan hebben we enkele columns nodig.
- Deze onderdelen zijn op hun beurt gerelateerd aan een leverancier (uit de tabel ap_leveranciers) en ook daarvan hebben we enkele columns nodig.

Dit gaat allemaal prima; geen enkel probleem.
Totdat ik wat diepergaande informatie (columns) nodig heb.
Eerst even kort samengevat wat ik momenteel aan het selecteren ben:

Onderdelen uit het archief.
Deze onderdelen zijn gerelateerd aan een tekening uit het archief (deze relaties zijn vastgelegd in de tabel ap_relaties_tekarch_ondarch).

Dan komt het probleem; deze tekening (uit het archief) is op zijn beurt gerelateerd aan een subproject en dit subproject heeft een column genaamd `lev_datum_bevestigd`. En díe datum, die moet aan bepaalde voorwaarden voldoen. En dat levert het probleem op. Deze query werkt uitstekend:

code:

                SELECT
                    ondarch.org_ond_id AS ond_id,
                    lev.lev_id,
                    lev.lev_naam,
                    ondarch.aantal_nodig,
                    ondarch.aantal_gebruikt,
                    ond.voorraad,
                    ond.omschrijving,
                    ond.lev_nummer
                FROM
                    ap_ond_archief AS ondarch
                LEFT JOIN
                    ap_onderdelen AS ond
                ON
                    (ondarch.org_ond_id = ond.ond_id)
                LEFT JOIN
                    ap_leveranciers AS lev
                ON
                    (ond.lev_id = lev.lev_id)
                LEFT JOIN
                    ap_relaties_tekarch_ondarch AS reltekondarch
                ON
                    (ondarch.ond_arch_id = reltekondarch.ond_arch_id)
                LEFT JOIN
                    ap_subprojecten AS subpr
                ON
                    (reltekondarch.tek_arch_id = subpr.tek_arch_id)
                WHERE
                    ondarch.aantal_gebruikt < ondarch.aantal_nodig
                ORDER BY
                    lev.lev_naam ASC

Met bovenstaande query selecteer ik dus dat met de volgende relatie-structuur:
onderdelen > tekening > subproject (resultaat: koppeling met subproject).
Ik kan nu, dit levert geen probleem op, columns slecteren uit de tabel ap_subprojecten. Máár. Als ik vervolgens - iets wat mijn doel is - de datum aan bepaalde voorwaarden wil laten voldoen, dan loopt de database vast. Hij loopt bijvoorbeeld vast door de WHERE clausule uit te breiden naar:

code:

                WHERE
                    ondarch.aantal_gebruikt < ondarch.aantal_nodig
                AND
                    subpr.lev_datum_bevestigd != '0000-00-00'

Snapt iemand hoe dit komt?! Ik vind het namelijk vreemd dat ik wel data kan selecteren uit de subprojecten-tabel, maar er geen voorwaarden aan kan koppelen.
Ik vermoed dat er iets mis gaat met - of de manier van JOIN-en, of de manier van sorteren (ORDER BY moet misschien een GROUP BY worden).

[ Voor 4% gewijzigd door Verwijderd op 25-08-2005 09:59 ]

[whoami]

lev_datum_bevestigd is hoogstwaarschijnlijk een datetime veld ?
Als jij die quotes rond die ' ' zet, dan ziet ie dat niet meer als een datetime veld, en zullen er hoogstwaarschijnlijk geen indexen kunnen gebruikt worden.

Er ligt toch een index op lev_datum_bevestigd ?

ps: ben je zeker dat je overal left joins nodig hebt ?

Verwijderd schreef op donderdag 25 augustus 2005 @ 09:58:
Ik vermoed dat er iets mis gaat met - of de manier van JOIN-en, of de manier van sorteren (ORDER BY moet misschien een GROUP BY worden).

Wat heeft GROUP BY met sorteren te maken ?
GROUP BY is om te GROEPEREN, niet om te sorteren. En aangezien je geen aggregate functies gebruikt, moet je ook niet groeperen.

[ Voor 41% gewijzigd door whoami op 25-08-2005 10:02 ]

[P_de_B]

Wat bedoel je precies met "De database loopt vast", krijg je een foutmelding of duurt het uitvoeren van de query erg lang?

Welke database gebruik je?

[Verwijderd]

whoami schreef op donderdag 25 augustus 2005 @ 10:01:
lev_datum_bevestigd is hoogstwaarschijnlijk een datetime veld ?
Als jij die quotes rond die ' ' zet, dan ziet ie dat niet meer als een datetime veld, en zullen er hoogstwaarschijnlijk geen indexen kunnen gebruikt worden.
Er ligt toch een index op lev_datum_bevestigd ?
ps: ben je zeker dat je overal left joins nodig hebt ?
[...]
Wat heeft GROUP BY met sorteren te maken ?

lev_datum_bevestigd is een DATE-field.

Over de left-joins ben ik niet zeker, ik heb dat eerlijk gezegd nooit zo bijzonder goed gesnapt (RIGHT, LEFT, INNER; ect.) maar op deze manier bereik ik wel wat ik wil hebben. Wellicht heeft iemand goede argumenten om een andere JOIN-methode te gebruiken (ik leer graag). Begrijp me overigens niet verkeerd met die opmerking; laks ben ik zeer zeker niet.

Er ligt overigens geen index op lev_datum_bevestigd - raad je dit wel aan?

P_de_B schreef op donderdag 25 augustus 2005 @ 10:02:
Wat bedoel je precies met "De database loopt vast", krijg je een foutmelding of duurt het uitvoeren van de query erg lang?

Welke database gebruik je?

Ik voer de query uit in PhpMyAdmin (in de browser dus) en vervolgens bevriest IE. Krijg hem alleen weer aan de gang met CTRL-ALT-DEL.

De database is MySQL, 4.0.15.

[ Voor 25% gewijzigd door Verwijderd op 25-08-2005 10:08 ]

[whoami]

Verwijderd schreef op donderdag 25 augustus 2005 @ 10:05:
[...]

Over de left-joins ben ik niet zeker, ik heb dat eerlijk gezegd nooit zo bijzonder goed gesnapt (RIGHT, LEFT, INNER; ect.) maar op deze manier bereik ik wel wat ik wil hebben. Wellicht heeft iemand goede argumenten om een andere JOIN-methode te gebruiken.

klik

Er ligt overigens geen index op lev_datum_bevestigd - raad je dit wel aan?

Je zoekt erop.... Wat denk je zelf ?

[ Voor 13% gewijzigd door whoami op 25-08-2005 10:09 ]

[whoami]

Verwijderd schreef op donderdag 25 augustus 2005 @ 10:05:
[...]


lev_datum_bevestigd is een DATE-field.

Waarom vergelijk je 'm dan met een string ?

[Verwijderd]

whoami schreef op donderdag 25 augustus 2005 @ 10:11:
[...]

Waarom vergelijk je 'm dan met een string ?

Je hebt niets minder dan gelijk - stomme fout. Echter, ik had dit snel getypt in mijn POST. In het 'echt' vergelijk ik hem met een DATE_ADD waarde.

Daarbij, als ik de WHERE clause verander in de volgende code, gaat het ook mis. Ik snap nog steeds niet waar het echte probleem zit;

code:

                WHERE
                    ondarch.aantal_gebruikt < ondarch.aantal_nodig
                AND
                    subpr.subpr_id != 1

[whoami]

MySQL heeft toch een tooltje waarmee je het exec plan van de query kunt laten zien ?

En wat is je conclusie nu mbt die index ?

[P_de_B]

MySQL kan toch maar 1 index gebruiken met query, wat gebeurt er als je maar 1 statement in de WHRE clausule gebruikt?

Overigens kun je met EXPLAIN informtie over de gebruike index krijgen.

[whoami]

P_de_B schreef op donderdag 25 augustus 2005 @ 10:29:
MySQL kan toch maar 1 index gebruiken met query, wat gebeurt er als je maar 1 statement in de WHRE clausule gebruikt?

Ik weet nu niet of het 1 index per query is, of 1 index per tabel binnen die query.


_{anyway, da's wel een serieuze beperking natuurlijk}

[cowgirl]

Verwijderd schreef op donderdag 25 augustus 2005 @ 10:15:

code:

                WHERE
                    ondarch.aantal_gebruikt < ondarch.aantal_nodig
                AND
                    subpr.subpr_id != 1

Zorgt de != niet voor een tablescan? Kan je de query eens proberen met een waarde die in de subpr tabel voor moet komen met =? En inderdaad, zoals al gezegd, zorgen voor indexen op de velden waarop je wilt selecteren.

[P_de_B]

whoami schreef op donderdag 25 augustus 2005 @ 10:32:
[...]

Ik weet nu niet of het 1 index per query is, of 1 index per tabel binnen die query.


_{anyway, da's wel een serieuze beperking natuurlijk}

Nee, het is per tabel inderdaad. Maar dan nog, als hij hem al gebruikt om te joinen, zal er voor de WHERE geen index meer gebruikt worden. Je kunt vast wel specificeren welke index gebruikt moet worden, misschien dat dat nog kan schelen, maar dat weet ik zo niet uit m'n hoofd.

[Verwijderd]

Ik heb inmiddels een x-aantal verschillende dingen geprobeerd, maar de conclusie is dat zodra ik een column uit de subprojecten tabel in de WHERE -clause (al dan niet in combinatie met een andere clause - dus tevens geprobeerd als enigste voorwaarde) plaats, hij zeeer traag is.
Heb net nl. even lang gewacht, en de query duurt dan 100 sec.+.

Een afbeelding van de EXPLAIN query:
afbeelding

Ik kan daar - met mijn kennis - overigens niet wijs uit worden.
Er zit overigens een index op lev_datum_bevestigd inmiddels.

[ Voor 7% gewijzigd door Verwijderd op 25-08-2005 10:52 ]

[whoami]

Je ziet in dat plan welke delen er lang duren.
Zoals je ziet, worden er slechts 2 indexen gebruikt in die hele query.

je ond_arch tabel kan bv een index gebruiken op org_ond_id

Verder moet je eens zoeken hoe je aan mysql kunt zeggen welke index hij moet gebruiken (aangezien je slechts 1 per tabel kunt gebruiken).

[Verwijderd]

Het is gelukt!
Voor de duidelijkheid, het lag aan de INDEX-en die simpelweg ontbraken. Dit was ook te zien in de afbeelding (van de EXPLAIN-query) die ik geposted heb. Daar is namelijk uit af te lezen dat voor o.a. de tabellen ap_ond_archief en ap_subprojecten (de grootste, met duidende rijen tegenover de slechts honderden rijen van de andere tabellen) er geen indexen worden gebruikt.

Deze heb ik op de juiste plaatsen aangemaakt en nu werkt het als een gladde aal.
De query die ik nu gebruik:

code:

SELECT ondarch.org_ond_id AS ond_id, lev.lev_id, lev.lev_naam, ondarch.aantal_nodig, ondarch.aantal_gebruikt, ond.voorraad, ond.omschrijving, ond.lev_nummer
FROM ap_ond_archief AS ondarch
LEFT JOIN ap_onderdelen AS ond ON (
ondarch.org_ond_id = ond.ond_id
)
LEFT JOIN ap_leveranciers AS lev ON (
ond.lev_id = lev.lev_id
)
LEFT JOIN ap_relaties_tekarch_ondarch AS reltekondarch ON (
ondarch.ond_arch_id = reltekondarch.ond_arch_id
)
LEFT JOIN ap_subprojecten AS subpr ON (
reltekondarch.tek_arch_id = subpr.tek_arch_id
)
WHERE ondarch.aantal_gebruikt < ondarch.aantal_nodig
AND subpr.lev_datum_bevestigd !=0000 -00 -00
AND subpr.lev_datum_bevestigd <= DATE_ADD( CURDATE( ) , INTERVAL 40 
DAY ) 
ORDER BY lev.lev_naam ASC 
LIMIT 0 , 30

Overigens, @Whoami, je kunt forcereren dat een bepaalde index moet worden gebruikt:

code:

FROM tabelnaam FORCE INDEX (indexnaam)

Bedankt allen!

/me heeft geleerd dat indexen erg belangrijk zijn als je met grote tabellen werkt