[SQL] Query is traag

[bas.kb]

Goeiemorgen! Ik ben bezig met een zoekfunctie, alleen is deze zodra er meerdere joins in zitten erg traag. Deze zoekfunctie is geschreven in PHP, en krijg bij het uitvoeren van de query ook een mysql time-out error. Ik gebruik MySQL 4.0.20a.

De structuur van de database is als het volgt

// deze tabel is gevuld met studentengegevens
tblStudenten
Id (Int, 7 karakters)
Naam (Varchar, 50 karakters)
Etc.

// koppeltabel om per student meerdere studie richtingen aan te geven
tblStudierichting
Id (Int, 11 karakters)
Waarde (Int, 11 karakters)

// koppeltabel om per student meerdere talen die die student beheerst aan te geven
tblTalen
Id (Int, 11 karakters)
Waarde (Int, 11 karakters)

De query waarbij ik meerdere JOINs leg ziet er zo uit:

code:

SELECT * FROM tblStudenten m 
JOIN tblStudierichting s ON m.Id = s.Id 
JOIN tblTalen t ON m.Id = t.Id 
WHERE s.Waarde= 11 
AND t.Waarde= 1 
ORDER BY m.Id DESC 
LIMIT 0, 50

Deze query is erg traag, zo traag dat ik bij de uitvoer ervan een time-out krijg. Ik heb al geprobeerd om de attributen die ik nodig heb direct aan te geven, ipv SELECT * FROM..., maar dit heeft geen resultaat. Het ORDER BY... weglaten heeft ook geen zin gehad. Ik heb tevens geprobeerd om de JOINs in het FROM component te leggen, dus:

code:

SELECT * FROM tblStudenten, tblStudierichting, tblTalen 
WHERE m.Id = t.Id 
AND m.Id = s.Id

... alleen scheelde dit geen tijd in het uitvoeren van de zoekopdracht. Ik heb ook geprobeerd om een LEFT, RIGHT, INNER LEFT, etc. JOIN te gebruiken, maar je raad het al... dit had ook geen resultaat Ik heb op google, de search, en mysql.com doorgespit, maar ik loop vast.

Mijn vraag is dus: Wat kan ik eraan doen om deze query sneller te maken?

[ Voor 6% gewijzigd door bas.kb op 13-10-2004 11:47 ]

[whoami]

Indexen ?
(En dan meer specifiek op de velden waarop je zoekt/joint).

[ Voor 78% gewijzigd door whoami op 13-10-2004 11:19 ]

[justmental]

Bijzonder datamodel
Je joint alleen op 'id' en die zit in alle tabellen

Hoe veel records zitten er in elk van de tabellen?

[ Voor 31% gewijzigd door justmental op 13-10-2004 11:22 ]

[bas.kb]

justmental schreef op 13 oktober 2004 @ 11:20:
Bijzonder datamodel
Je joint alleen op 'id' en die zit in alle tabellen

Zolang ik die tabelnamen bij naam noem dan kan hij ze toch prima onderscheiden? Zou dit de query traag maken?

Ben op mysql.com aan het zoeken op indexen, ga ik even doorlezen!

[ Voor 7% gewijzigd door bas.kb op 13-10-2004 11:23 ]

[ACM]

En om hoeveel records gaat het?

[bas.kb]

De tabel Studenten bevat 18000 records

De tabel Talen bevat 30000 records, en Studierichting idem

[P_de_B]

Wat ik wel vreemd vind is dat je joint op de ID's van alle tabellen?

t.id join je op m.id en op s.id

[guanpedro]

lamaar ik lees niet goed....

[ Voor 88% gewijzigd door guanpedro op 13-10-2004 11:35 ]

[curry684]

bas.kb schreef op 13 oktober 2004 @ 11:23:
[...]

Ben op mysql.com aan het zoeken op indexen, ga ik even doorlezen!

Als je dat nog op moet zoeken is dat zeker het probleem

Zolang je niet aan complexe databases hoeft te werken: leg altijd een index op iedere primary en foreign key, niet eens bij nadenken En in dit geval zou ik er ook een leggen op de 'waarde'-velden, zeker als je daar vaker op zoekt.

[bas.kb]

P_de_B schreef op 13 oktober 2004 @ 11:31:
Wat ik wel vreemd vind is dat je joint op de ID's van alle tabellen?

t.id join je op m.id en op s.id

Voorbeeld: Als ik een student in de studierichting 'Onderwijs', èn 'Spaans' spreekt moet ik wel m.Id linken met t.Id en s.Id. Hoe zou jij het doen?

[curry684]

bas.kb schreef op 13 oktober 2004 @ 11:37:
[...]


Voorbeeld: Als ik een student in de studierichting 'Onderwijs', èn 'Spaans' spreekt moet ik wel m.Id linken met t.Id en s.Id. Hoe zou jij het doen?

De verwarring ontstaat uit de naamgeving neem ik aan. Normaliter noem je alleen de primary key 'id' of iets dergelijks, en noem je de foreign key in de andere tabel bijv. 'taal_id' oid. Het lijkt er nu dus op dat je 3 primary keys joint, wat inderdaad onzinnig zou zijn. Mogen we wellicht de tabellayout van de 3 betrokken tabellen eens zien?

[bas.kb]

curry684 schreef op 13 oktober 2004 @ 11:35:
[...]

Als je dat nog op moet zoeken is dat zeker het probleem

Eens moet de eerste keer zijn hè

Zolang je niet aan complexe databases hoeft te werken: leg altijd een index op iedere primary en foreign key, niet eens bij nadenken En in dit geval zou ik er ook een leggen op de 'waarde'-velden, zeker als je daar vaker op zoekt.

Thx

[ Voor 3% gewijzigd door bas.kb op 13-10-2004 11:41 ]

[P_de_B]

bas.kb schreef op 13 oktober 2004 @ 11:37:
[...]


Voorbeeld: Als ik een student in de studierichting 'Onderwijs', èn 'Spaans' spreekt moet ik wel m.Id linken met t.Id en s.Id. Hoe zou jij het doen?

Teken maar eens uit wat je nu doet. Dan zie je het wel denk ik

[bas.kb]

Het is mijn database ontwerp niet, had het hier en daar ook wat logischere namen gegeven. Ik zie net dat Studenten.Id van het type Int (7 karakters) is, en de beide koppeltabellen Int (11 karakters) is. Is dit slecht voor de snelheid?

[Riegstar]

Dat bedoeld P_de_B niet..

[mjax]

Het is je database model WEL. Je maakt nu koppel tabellen, waar precies 1 attribuut gelinked wordt (te weten Waarde). Dan had je dat attribuut net zo goed deel uit kunnen laten maken van de studenten tabel. Uit je datamodel blijkt niet dat je die waardes herbruikt voor meerder studenten anders had je in de koppeltabel naast een ID voor de waarde ook een foreignkey ID voor de student gehad. Nu heb je ogenschijnlijk alleen de foreign key opgenomen.

Maar ik gok dat je eigenlijk de waardes welwilde hergebruiken. Als dat het geval is, moet je de twee koppeltabellen aanmaken (tblStudierichting en tblTalen zijn geen koppeltabellen). In die twee nieuwe koppeltabellen leg je foreingkeys naar de student en de studierichting/taal.

Oftewel

tblStudentStudierichting
student_id int
studierichting_id int

en

tblStudentTaal
student_id int
taal_id int

Dan je query herschijven natuurlijk.

Ik zie nu pas dat Waarde ook een int is dus waarschijnlijk een foreignkey? In dat geval is mijn voorstel dus al geimplementeerd, zij het met wel heel vreemde database veldnamen.

[ Voor 48% gewijzigd door mjax op 13-10-2004 11:59 ]

[bas.kb]

Bedoeld P_de_B dat het beter is om onderstaande query te gebruiken?

code:

SELECT * FROM tblStudenten m 
RIGHT JOIN tblStudierichting s ON m.Id = s.Id 
INNER JOIN tblTalen t ON s.Id = t.Id 
WHERE s.Waarde= 11 
AND t.Waarde= 1 
ORDER BY m.Id DESC 
LIMIT 0, 50

Hmm volgens mij gaat dat idd sneller!!

[bas.kb]

mjax schreef op 13 oktober 2004 @ 11:54:
Het is je database model WEL.

De genoemde tabellen zijn wel koppeltabellen, die verwijzen naar een tabelletje gevuld met enkel studie richtingen, talen.

Die ziet er zegmaar zo uit:

tbltalen2
Id (int)
Waarde (varchar)

Gevuld met
1 Onderwijs
2 Beveiliging
3 enz!

[ACM]

Als er inderdaad geen indexen in je database geplaatst zijn is dat gelijk de reden dat de query zo traag reageert. In dat geval ben je namelijk die 18000 records tegen 30000 records aan het leggen en die combinatie leg je weer tegen 30000 andere records. Dat is dus 18000*30000*30000 = 16200000000000 vergelijkingen.

Je limit kan eventueel de boel wat inperken, maar dan heb je alsnog 50 * 30000 * 30000 = 45000000000 vergelijkingen.

Op het moment dat je indexen gebruikt, moet je database nog steeds met die 18000 records werken (eventueel dus gelimiteerd), maar kan in dat geval vrij snel de records die je nodig hebt uit die twee sets van 30000 halen. Als je de index-lookup niet meerekent, kom je dan op gemiddeld 18.000 * 1 2/3 * 1 2/3 = 50000. En dat scheelt dus wel een beetje...

Aangezien mysql geen twee indices van één tabel kan combineren in een query, kan het overigens nog extra winst geven om een zogenaamde gecombineerde index te maken op meerdere kolommen (id, waarde of waarde, id afhankelijk van welke het meest selectief is) van die studierichting/talen tabellen.

[ACM]

bas.kb schreef op 13 oktober 2004 @ 12:00:
Bedoeld P_de_B dat het beter is om onderstaande query te gebruiken?

code:

SELECT * FROM tblStudenten m 
RIGHT JOIN tblStudierichting s ON m.Id = s.Id 
INNER JOIN tblTalen t ON [b]s[/b].Id = t.Id 
WHERE s.Waarde= 11 
AND t.Waarde= 1 
ORDER BY m.Id DESC 
LIMIT 0, 50

Hmm volgens mij gaat dat idd sneller!!

't Is alleen wel een heel andere query... Want de s.Id en t.Id en m.Id representeren heel andere zaken, dat is dan ook gelijk de belangrijkste reden waarom je ze duidelijke namen zou moeten geven.
[edit]
Ownee, je Id-velden wijzen allemaal (in deze drie tabellen dan) naar de studenten.id, maar niettemin blijft het een query waar maar weinig winst in te halen valt als je geen indices hebt.

[ Voor 17% gewijzigd door ACM op 13-10-2004 12:12 ]

[curry684]

Je moet echt dringend wat doen aan de naamgeving, zo snapt niemand er dadelijk nog iets van

Normaliter koppel je tabellen als volgt:

User
UserId
UserName
UserEmail
...

Role
RoleId
RoleName
...

UserInRole
RoleId
UserId

Als je alles gewoon ad random 'id' en 'waarde' gaat noemen en diezelfde namen in iedere tabel anders gebruikt wordt het echt een teringbende, excusez le mot.

[P_de_B]

ACM schreef op 13 oktober 2004 @ 12:07:
Je limit kan eventueel de boel wat inperken, maar dan heb je alsnog 50 * 30000 * 30000 = 45000000000 vergelijkingen.

offtopic:
Ik zou verwachten dat de LIMIT na de join toegepast wordt, dus dat je nog steeds de grote join uitvoerd, dan een ORDER BY en dan pas de limit, of zit ik er helemaal naast?

[curry684]

P_de_B schreef op 13 oktober 2004 @ 12:14:
[...]

offtopic:
Ik zou verwachten dat de LIMIT na de join toegepast wordt, dus dat je nog steeds de grote join uitvoerd, dan een ORDER BY en dan pas de limit, of zit ik er helemaal naast?

Hij zal wel moeten, want pas na de join en de sort heeft ie het recht om records te truncen, hij kan dat vantevoren idd niet bepalen

[ACM]

P_de_B schreef op 13 oktober 2004 @ 12:14:

offtopic:
Ik zou verwachten dat de LIMIT na de join toegepast wordt, dus dat je nog steeds de grote join uitvoerd, dan een ORDER BY en dan pas de limit, of zit ik er helemaal naast?

Dat hangt van de query af
't Zijn er sowieso meer, omdat de joins natuurlijk die waarde-velden voorschrijven, maar in principe is er op die manier wel degelijk winst te behalen. Want de sorteervolgorde verandert niet door de velden die er afvallen omdat ze niet aan de waarde-conditie voldoen. Alleen welke records er uiteindelijk getoond moeten worden, varieert daardoor.

Of mysql dit ook daadwerkelijk zo limiteert weet ik niet, maar het is technisch mogelijk en technisch ook correct om het in dit geval vroegtijdig af te kappen

[Verwijderd]

curry684 schreef op 13 oktober 2004 @ 11:35:
[...]

Zolang je niet aan complexe databases hoeft te werken: leg altijd een index op iedere primary en foreign key, niet eens bij nadenken En in dit geval zou ik er ook een leggen op de 'waarde'-velden, zeker als je daar vaker op zoekt.

Je legt bijvoorbeeld geen index aan op tabellen waar met een hele hoge frequentie in geschreven wordt (bv click logs, bank transacties).

[gorgi_19]

Verwijderd schreef op 13 oktober 2004 @ 12:42:
[...]


Je legt bijvoorbeeld geen index aan op tabellen waar met een hele hoge frequentie in geschreven wordt (bv click logs, bank transacties).

Afaik legt je geen indexen aan op de kolommen die vaak gewijzigd worden

[ACM]

Verwijderd schreef op 13 oktober 2004 @ 12:42:
Je legt bijvoorbeeld geen index aan op tabellen waar met een hele hoge frequentie in geschreven wordt (bv click logs, bank transacties).

In veel gevallen hebben zelfs tabellen met een hele hoge turnover nog baat bij het toevoegen van indices. Althans, je moet wel goed nadenken waar je die plaatst en of ze ook echt nodig zijn.

Als je bijv kan kiezen tussen updates/inserts die gemiddeld 10% trager zijn en daardoor je load 10% toeneemt, maar je queries die je er op loslaat gemiddeld 500% trager lopen en je load daardoor 30% toeneemt, dan is de keus imho vrij snel gemaakt

[bas.kb]

Index toegevoegd op alle koppeltabellen en nu werkt de zoekfunctie lekker snel!

Bedankt

[Verwijderd]

ACM schreef op 13 oktober 2004 @ 12:49:
[...]

In veel gevallen hebben zelfs tabellen met een hele hoge turnover nog baat bij het toevoegen van indices. Althans, je moet wel goed nadenken waar je die plaatst en of ze ook echt nodig zijn.

Als je bijv kan kiezen tussen updates/inserts die gemiddeld 10% trager zijn en daardoor je load 10% toeneemt, maar je queries die je er op loslaat gemiddeld 500% trager lopen en je load daardoor 30% toeneemt, dan is de keus imho vrij snel gemaakt

Neem nog wel even de frequentie van reads vs writes mee. Als 99.9% writes zijn en 0.1% een read, dan is de keus imho vrij snel gemaakt

[ACM]

Verwijderd schreef op 13 oktober 2004 @ 14:55:
Neem nog wel even de frequentie van reads vs writes mee. Als 99.9% writes zijn en 0.1% een read, dan is de keus imho vrij snel gemaakt

Dat hangt er dus van af wat de invloed op de load is Vandaar dat ik die er even los bijnoemde, daarmee doelde ik namelijk op de totale systeemload (inclusief reads/writes enzo), maar in de meeste gevallen zal met jouw voorbeeld inderdaad de indices voor meer ipv minder load zorgen.

[ Voor 9% gewijzigd door ACM op 13-10-2004 17:50 ]