[MySQL] performance probleem met query

[RSD]

Ik heb de volgende database:

p (pagina)
- id
- naam

c (categorie)
- id
- naam
- pid

l (links)
- id
- naam
- cid

Nu wil ik een pagina overzicht tevoorschijn toveren met alle categorieen (ook de lege) met darain de links.

Eerder had ik een while loop van de categorieen met daarin een query voor de links voor die categorie. Als er 10 categorieen zijn, worden er dus 11 queries uitgevoerd. Dit wil ik nu in 1 query hebben als het kan. Immers de database bevat momenteel 300.000 links en het wordt allemaal erg traag.

Ik heb al het een en ander geprobeerd, zoals:

MySQL:

SELECT * FROM c,l WHERE c.pid='4093' AND (l.cid=c.id OR c.id<>'0')

echter haalt hij nu alle links op lijkt wel. Als ik het volgende doe:

MySQL:

SELECT * FROM c,l WHERE c.pid='4093' AND (l.cid=c.id)

haalt hij alleen die categorieen op waarin links staan en niet de categorieen zonder links.

Ik dacht dat de eerste wel goed zou gaan, echter is dta niet het geval.

[EdwinG]

Dit lijkt me een prima voorbeeld voor een JOIN (LEFT JOIN)
Een voorbeeld voor MySql:

SQL:

SELECT c.naam AS catnaam, l.naam AS linknaam, l.id AS linkid, c.id AS catid, l.cid
FROM c
LEFT JOIN l ON l.cid = c.id
WHERE c.pid =0;

De reden dat ik AS gebruik is het voorkomen van dubbele kolomnamen.

[RSD]

Hmm bedankt, het werkt wel, alleen ik merk niet echt dat het veel sneller is. Ik vraag me dus ook af hoe ik snellere queries kan uitvoeren op de een tabel met meer dan 300.000 records. Kan ik de tabel dan beter opsplitsen in meerdere tabellen of moet ik een server erbij halen die als database server dient? Het voordeel is dat ik de pagina's allemaal opsla in een bestand, het opvragen van die pagina's is vrij snel. Echter het bewerken van de pagina's gaat erg langzaam en dan met name bij het ophalen van het totaal overzicht.

[Gonadan]

Zijn je id's Primary Keys en je cid's en pid's Foreign Keys?

Dat verhoogt de performance namelijk aanzienlijk

[RSD]

De id's zijn idd primary keys. Ik heb nooit geen foreign keys ingesteld. Is dat verstandig en kan ik dat nu nog doen?

HGoe kan ik dat bijvoorbeeld in phpMyAdmin doen?

[ Voor 21% gewijzigd door RSD op 29-03-2006 09:22 ]

[Tukk]

RSD schreef op woensdag 29 maart 2006 @ 09:20:
De id's zijn idd primary keys. Ik heb nooit geen foreign keys ingesteld. Is dat verstandig en kan ik dat nu nog doen?

HGoe kan ik dat bijvoorbeeld in phpMyAdmin doen?

Foreignkeys kunnen weer vertragend werken indien je veel inserts hebt op de tabellen.
Kijk goed naar wat je verwacht hoe je database gebruikt gaat worden en pas daar de keys op aan.

[RSD]

Het wordt beiden redelijk vaak gedaan. Eerder was hij echt supersnel, maar nu is hij zo traag. Het is gewoon niet leuk meer om ermee te werken. Ik zat al te dneken om de tabel op te delen in 6 tabellen met elk 50.000 erin, maar dat is ook weer zo een gedoe, dan moet ik echt alles aanpassen inc mijn script.

[ Voor 78% gewijzigd door RSD op 29-03-2006 09:31 ]

[Tukk]

RSD schreef op woensdag 29 maart 2006 @ 09:30:
Het wordt beiden redelijk vaak gedaan. Eerder was hij echt supersnel, maar nu is hij zo traag. Het is gewoon niet leuk meer om ermee te werken. Ik zat al te dneken om de tabel op te delen in 6 tabellen met elk 50.000 erin, maar dat is ook weer zo een gedoe, dan moet ik echt alles aanpassen inc mijn script.

Dat zou ik zeker niet doen
Dat heeft geen enkele nut, indien je keys en indecis kloppen heeft alleen maar een negatief effect,
je moet namelijk dan weer uitzoeken in welke tabel je moet zoeken..

Heb je wel een index op c.pid, mogelijk in combinatie met c.id?

Zoekt uit wat traag gaat, 300.000 is niet extreem veel voor een beetje database of je moet oude hardware in gebruik hebben. (of een pc die meer heeft te doen alleen jouw database).

[ Voor 12% gewijzigd door Tukk op 29-03-2006 09:36 ]

[RSD]

Ik heb voor 1 site een dual xeon 2,8ghz met 2 processors en 1 gig geheugen en scsi harde schijf op 15k rpm. Er draait 1 site op de server.

Als ik een tabel aanmaal, dan gebruik ik eigenlijk alleen primary key icm auto increment en unique. De andere id's koppel ik niet of heb ik ook geen index op denk ik.

[ Voor 39% gewijzigd door RSD op 29-03-2006 09:40 ]

[Tukk]

Je benadert de tabel c via twee kolommen in 1 query.
Daarbij benader je de tabel l via een kolom die niet je primary key is.

Voor beiden moet hij dus de gehele tabel door, want je kan de query niet vertellen waar te zoeken, want je hebt geen index aangelegd. Probeer het eens om voor beide tabellen een nieuwe index te maken.

Als je uit c de pid altijd ophaalt in combinatie met c.id stel ik de volgende index voor:

code:

index:
c.id
c.pid

anders alleen een index op pid.
Hetzelfde truukje ook op l, probeer daarna de query's nogmaals.

Wat ook kan helpen is kijken waar de query's moeite mee hebben, ik neem aan dat je in MySql ook wel explain-plan o.i.d. kan laten zien?

[RSD]

Ik zoek eigenlijk in de tabel c naar alle records die een bepaalde pid hebben. Daarnaast moet de c.id van die categorie gelijk zijn aan de l.cid ... moet ik dan een index zetten op c.id en c.pid en een index op l.id en l.cid?

Als ik een index zet op een kolom die al primary key is dan zegt hij dta het inet kan, omdta het al een primary key is.

Ik heb wat indexen geplaatst en hij is meteen echt 10x sneller!

[ Voor 76% gewijzigd door RSD op 29-03-2006 10:12 ]

[Tukk]

geen dank

Kijk nu nog eens goed naar je queries en probeer te beredeneren waar je indecis kan gebruiken, dan voorkom je problemen in de toekomst.

[RSD]

Echt hartsikke bedankt!

Ik wist niet dat dat zoveel uitmaakte. Het lijkt wel alsof hij 100x sneller is. Eerder moest ik soms wel 10 seconden wachten... nu in een flits!

Ik vraag me nu alleen af op welke kolommen ik ene index moet zetten. Moet ik dan voornamelijk naar het WHERE gedeelte kijken?

[ Voor 28% gewijzigd door RSD op 29-03-2006 10:33 ]

[Tukk]

RSD schreef op woensdag 29 maart 2006 @ 10:32:
Echt hartsikke bedankt!

Ik wist niet dat dat zoveel uitmaakte. Het lijkt wel alsof hij 100x sneller is. Eerder moest ik soms wel 10 seconden wachten... nu in een flits!

Ik vraag me nu alleen af op welke kolommen ik ene index moet zetten. Moet ik dan voornamelijk naar het WHERE gedeelte kijken?

Ja, het gaat puur om de kolommen die je gebruikt in de where-clause, daar zoekt hij mee in de tabellen. Daarna gaat hij pas de tabel zelf in om de data op te halen, maar die heeft dan al.

[whoami]

RSD schreef op woensdag 29 maart 2006 @ 10:32:
Echt hartsikke bedankt!

Ik wist niet dat dat zoveel uitmaakte. Het lijkt wel alsof hij 100x sneller is. Eerder moest ik soms wel 10 seconden wachten... nu in een flits!

Ik vraag me nu alleen af op welke kolommen ik ene index moet zetten. Moet ik dan voornamelijk naar het WHERE gedeelte kijken?

Waar je op joined, waar je op zoekt, eventueel waar je op sorteert.
Echter, wees wel 'voorzichtig', en denk goed na. Zorg ervoor dat je geen indexen legt die je toch nooit gebruikt. Ga ook na of je composite indexen kan maken, en indien je dat doet, let dan ook op de volgorde van de columns in je index.
Als je een samengestelde index hebt op de columns (A, , en je doet een query en je zoekt binnen die query enkel op column A, dan kan die samengestelde index gebruikt worden.
Doe je echter een query, en je zoekt enkel op column B, dan kan die samengestelde index niet gebruikt worden.

[RSD]

Is een tabel met meerdere indexen een composite index?

[Michali]

Nee, 1 index op meerdere velden is een composite index.

Weet je trouwens dat je in een query aliasen kunt maken van tabellen? Je kunt je tabellen dus de volledige naam geven en in de query hernoemen naar 1 enkele letter, voor het gemak. Dat is bij het beheren van je DB wat makkelijker.

[ Voor 70% gewijzigd door Michali op 29-03-2006 11:21 ]

[Tukk]

RSD schreef op woensdag 29 maart 2006 @ 11:16:
Is een tabel met meerdere indexen een composite index?

Nee het is een index over meerdere tabellen, zoals ik die ook eerder melde.

Indien je regelmatig een tabel benadert met behulp van twee dezelfde kolommen heeft het nut om
deze twee kolommen in dezelfde index te plaasten (let op de volgorde is niet onbelangrjk).

Misschien is het nuttig om even te lezen wat er hier allemaal over te lezen valt op het Internet, je probleem is nu verholpen, om echt verder te komen moet je zelf lezen/uitzoeken/uitproberen, daar leer je het meeste van.

[ Voor 5% gewijzigd door Tukk op 29-03-2006 11:20 ]

[RSD]

Ik ga hier zeker meer over lezen. Ik vind lezen op internet niet zo heel fijn. Zijn er ook goede boeken over mysql, die dit allemaal bespreken? Eigenlijk ben ik maar een hobbyistje en heb het me allemaal zelf aangeleerd. Het probleem hiermee is dat je soms fundamentele zaken over het hoofd ziet of er geen weet van hebt.