[PHP/MYSQL] database optimalisatie

[w3news]

We zijn bezig met het maken van een website, en dat is leuk en aardig, maar loopt nog niet optimaal.

Als ik lokaal het draai lijkt alles vlekkeloos te gaan, en draait het lekker soepel, maar online loopt de site traag, en wordt de database flink belast. (database is net zo groot, wordt alleen door meer bezocht)

Nu staan er ruim 60.000 sites opgeslagen, en dat is natuurlijk niet niks, maar lijkt me dat wel sneller moet kunnen.
Heeft iemand tips hoe de query's beter kan maken voor grote databases?

Nu gaat het ophalen van de 10 populairste sites op de home op de volgende manier:

code:

SELECT user.points, user.username, 
site.link, site.user, site.screenshot, site.score, site.hits, site.description, site.id, site.cat, 
cat.name 
FROM site 
LEFT JOIN cat 
ON site.cat=cat.id 
LEFT JOIN user 
ON user.id = site.user WHERE
site.active='yes' ORDER BY ".$order." DESC LIMIT ".$limit.",10

Mocht je meer informatie nodig hebben, dan hoort ik het graag.

p.s. de website is geschreven in PHP, en maakt gebruik van een mysql database.

[Creepy]

En welke zaken heb je zelf al geprobeerd om het te optimaliseren? Welke indexen heb je op de tabellen zitten? Explain al gebruikt?

[ Voor 8% gewijzigd door Creepy op 18-12-2006 11:18 ]

[PolarBear]

Een aantal dingen.
1. 60.000 records is peanuts voor een database. Dat is dus wel niks.
2. Ik zie in je SQL $order & $limit staan. Let je op SQL injection?
3. Op wat voor machine draai je het online? Je zegt dat het lokaal wel goed werkt.
4. Welke versie van MySQL gebruik je? Is er verschil tussen lokaal en online?

[w3news]

Bij explain staat bij rows bijna 70.000, de 2 anderen staat een 1.
Zal even kijken naar indexen, dat is nog niet gedaan, scheelt dat veel snelheid?

Er wordt trouwens mysql 5.1 wordt gebruikt, en er wordt apache gebruikt, en draait op linux machine.

[ Voor 26% gewijzigd door w3news op 18-12-2006 11:27 ]

[Creepy]

w3news schreef op maandag 18 december 2006 @ 11:24:
Bij explain staat bij rows bijna 70.000, de 2 anderen staat een 1.
Zal even kijken naar indexen, dat is nog niet gedaan, scheelt dat veel snelheid?

Dat kan enorm veel schelen ja. Die 70.000 is een volledige tablescan. Met de juiste indexen kan je hier heel veel tijdwinst mee halen.

[Swaptor]

Ja, een goede indexering is vaak zelfs beter dan optimalisatie van je SQL. (Uitzonderingen daargelaten)

[w3news]

Die index, ik zal even kijken hoe ik dat beste kan doen, maar hij haalt uit 3 tabellen gegevens he.

[whoami]

Naast de indexen (waar je wel het meeste performance winst kunt uit halen) moet je ook eens nagaan of je die LEFT JOIN echt nodig hebt, en of een INNER JOIN al niet voldoende is. Een LEFT JOIN is nl. trager dan een INNER JOIN.
Als een site altijd aan een user en een category gekoppeld is, heb je helemaal geen left join nodig.

site.active='yes'

Waarom is het active veld hier geen boolean ?

[ Voor 10% gewijzigd door whoami op 18-12-2006 11:36 ]

[w3news]

zal inderdaad kijken naar die join, want er is altijd gekoppeld ja, en die active kan inderdaad ook boolean ipv enum, scheelt dat ook tijd?

[Yo-han]

Kijk ook even naar je query cache. Zo'n query als de top tien ophalen wordt vaak gebruikt maar veranderd vrijwel niet, is iig vaak hetzelfde. Door je query cache wat meer mb toe te kennen kan je ook hier mee enige tijdwinst boeken.

[w3news]

Er staat alleen een FULLTEXT index bij het tabel, vertraagt dit het misschien ook?
(Dit wordt gebruikt voor het zoeken)

[Janoz]

Een extra index vertraagd alleen bij het wijzigen van de tabel. Bij het uitlezen van de tabel kan het helpen om de boel te versnellen, of het wordt compleet genegeerd.

[w3news]

en hoe kan ik de index dan het beste doen hier?

[djc]

Ik gok dat je indices wil op site.cat en op site.user...

[w3news]

ja denk het wel dat dat verstandig is.

[Yo-han]

gebruik explain om uit te vinden of de indexen die je maakt ook daadwerkelijk gebruikt worden

[Grijze Vos]

whoami schreef op maandag 18 december 2006 @ 11:36:

Waarom is het active veld hier geen boolean ?

MySQL kent geen booleans.

[w3news]

je hebt gelijk, ik was blijkbaar nog niet echt wakker.
Maar kan iemand uitleg geven over die indexes? of heeft een link naar een goede uitleg hierover?

Moet ik over tabel van de sites index plaatsen over bepaalde velden ide ik dan oproep, en ook de andere tabellen die ik gebruik?
Of moet dat in 1 index?

En wat is precies de werking?
Als ik een index maak, dan moet sneller gaan, maar waarom? wat doet het?

[ Voor 94% gewijzigd door w3news op 18-12-2006 13:16 ]

[Janoz]

Tja, hoe makkelijk wil je het krijgen? Een index is net als de index in een boek. Een gesorteerde lijst waar je makkelijk doorheenzoekt met daarbij een verwijzing naar waar hij in de echte data staat (de pagina van het boek in geval van een index in een boek). Wanneer je een veld hebt waar vaak op wordt gezocht en je wilt dat dit zoeken sneller gaat dan zet je op dat veld een index. Meer is er niet over de basis van indexen te vertellen eigenlijk.

[w3news]

ok bedankt voor de uitleg.

[Verwijderd]

Grijze Vos schreef op maandag 18 december 2006 @ 12:43:
[...]

MySQL kent geen booleans.

tinyint waar je of 0 of 1 in stopt voldoet imho

[w3news]

ja das waar, dat scheelt weer ja, bedankt voor de tip!

Maar wat moet ik dan in de index plaatsen, een bepaald veld, aantal, de grote, of al de velden die ik dan aanroep?

En bij LIMIT beperk je je query tot aantal gevonden resultaten.
Maar eigenlijk zoekt hij nog steeds alle records door.
Is er betere methode hiervoor?

[ Voor 81% gewijzigd door w3news op 18-12-2006 13:46 ]

[YopY]

En bij LIMIT beperk je je query tot aantal gevonden resultaten.
Maar eigenlijk zoekt hij nog steeds alle records door.
Is er betere methode hiervoor?

Eigenlijk zoekt hij niet alle records door - hij zoekt net zolang totdat hij het aantal records gevonden heeft die je aangegeven hebt in je LIMIT, of totdat hij alles doorgezocht heeft.

In PHPMyAdmin kun je een Index aanmaken, in het Structure-scherm van een tabel (volgens mij ergens onder). Ik weet niet precies meer hoe het gaat (heb zelfs nog nooit een index gemaakt), maar daar kun je op een knoppie drukken voor het maken van een Index. Maak de index op de meest naar gezochte velden, bijvoorbeeld diegenen waar op gejoind word.

[Gurbe de n00b]

Verwijderd schreef op maandag 18 december 2006 @ 13:23:
[...]


tinyint waar je of 0 of 1 in stopt voldoet imho

Of een enum veld

[ Voor 0% gewijzigd door Gurbe de n00b op 18-12-2006 14:22 . Reden: schoonheidsfoutje ]

[whoami]

Eigenlijk zoekt hij niet alle records door - hij zoekt net zolang totdat hij het aantal records gevonden heeft die je aangegeven hebt in je LIMIT, of totdat hij alles doorgezocht heeft.

Als je een ORDER BY clausule gebruikt, zal hij wel eerst alles moeten ophalen / sorteren en dan pas de rows returnen. Anders kan je DBMS nooit weten of hij wel de correcte n aantal rows gereturned heeft.

[w3news]

Ja ik weet aar je dat aan kan maken daar in phpmyadmin.
Mijn vraag was meer over de uitvoering er van.
Maar heb wat zitten stoeien, en wordt al sneller zo te zien.

[w3news]

Gurbe de n00b schreef op maandag 18 december 2006 @ 14:22:
[...]


Of een enum veld

heb ik dat niet al dan?

[w3news]

whoami schreef op maandag 18 december 2006 @ 14:24:
[...]
Als je een ORDER BY clausule gebruikt, zal hij wel eerst alles moeten ophalen / sorteren en dan pas de rows returnen. Anders kan je DBMS nooit weten of hij wel de correcte n aantal rows gereturned heeft.

Oftewel, als ik geen sortering er bij zet, dan loopt niet alles langs en is het stuk sneller.
Maar wil natuurlijk wel gesorteerd hebben, want anders klopt het niet.

[Voutloos]

w3news schreef op maandag 18 december 2006 @ 13:24:
Maar wat moet ik dan in de index plaatsen, een bepaald veld, aantal, de grote, of al de velden die ik dan aanroep?

Lees eens rustig de index gerelateerde artikelen door in de mysql manual (7.4. Optimizing Database Structure). In de rest van hoofdstuk 7 staan wellicht nog wat interessante zaken.

edit:
please, haal ff rustig adem en lees en probeer het een en ander. Vergeet tevens niet dat er ook een edit-knop is. Tevens hoeft order by niet traag te zijn, want misschien kan je een index regelen in de goede volgorde.

[ Voor 25% gewijzigd door Voutloos op 18-12-2006 14:33 ]

[w3news]

Vreemde is nu dat nu de site ene keer lekker loopt, en andere keer erg traag weer.

[ Voor 81% gewijzigd door w3news op 18-12-2006 16:09 ]

[Verwijderd]

Hrmm ik zou me toch even afvragen wat de load op de mySQL server van je provider is. Want opzichzelf is het aantal records wat je hebt niet zo gek spannend. Ik ga er overigens vanuit dat je de screenshots als referentie naar een file hebt opgeslagen?

Als je exact dezelfde query uitgevoerd in phpMyAdmin, een paar keer achter elkaar uitvoert en elke keer komt het resultaat in een andere tijd binnen vermoed ik zomaar dat je SQL server beetje over uren draait?

(btw als ik je site ff snel bekijken denk ik dat je bij success wel een probleem met je traffic gaat krijgen. Ondanks dat je het welicht gzip'ed verstuurd zou ik je toch advieseren om je javascriptjes en stylesheets niet in je html te zetten maar in losse files. Dat cached een stuk prettiger namelijk )

[ Voor 1% gewijzigd door Verwijderd op 18-12-2006 16:56 . Reden: nettere zinnetjes gemaakt ]

[eghie]

Op primaire key hoeft geen index, aangezien primaire keys zelf al indexes zijn. Indexes zet je dus op velden waar je vaak op zoekt. Ook met JOINS koppelen, zoek je op die velden.

In die query die jij als voorbeeld geeft zou ik de index op de volgende velden zetten:
site.cat, cat.id, user.id, site.user, site.active

site.active zou ik als TINY INT omzetten en dan gewoon 0 of 1 gebruiken. Zoekt ook sneller.

FULL TEXT indexes zijn traag. Op welke velden heb je die? Ze zijn alleen maar handig om lappen text mee door te zoeken. Zoals bij GoT hier de reacties en de titels.

Koppel die tabellen ook met relaties aan elkaar. Dus niet alleen tijdens joinen. Hierdoor kun je een stuk betere documentatie maken en kan de database de boel beter optimaliseren.

Maak ook gebruik van VIEWS. Dit zijn tijdelijke tabellen die gecached worden bij aanroep. Dit maakt het zoeken al een stuk sneller. Ik weet niet hoe je de order regelt, maar speel hier eens mee. Van de voorbeeld query zou ik het zo doen:

SQL:

CREATE VIEW sitelist AS SELECT user.points, user.username, 
site.link, site.user, site.screenshot, site.score, site.hits, site.description, site.id, site.cat, 
cat.name 
FROM site 
LEFT JOIN cat 
ON site.cat=cat.id 
LEFT JOIN user 
ON user.id = site.user WHERE
site.active=1 ORDER BY default_order

Die kun je dan zo aanroepen:

SQL:

SELECT * FROM sitelist ORDER BY andere_order LIMIT begin,10

Als die order hetzelfde is als de default order, zet hem dan niet in de query.

[w3news]

Heel erg bedankt voor je reactie, ik zal kijken wat ik er mee kan.

FULLTEXT heb ik voor link, titel, beschrijving en zoekwoorden in tabel sites, en gebruik ik voor de zoekfunctie.

Als ik het doe via phpmyadmin dan is die laatste query bijna 8 seconden bezig, dus erg lang.

[ Voor 66% gewijzigd door w3news op 18-12-2006 20:06 ]

[eghie]

Zoekwoorden zou ik in een aparte koppel tabel stoppen. Met ongeveer zo'n indeling
sites_tags
site_id | tag_id

en dan een tabel tags
id | tag

gecombineerde primaire sleuten op site_id en tag_id, normale primaire sleuten op id. en een index op tag.

[w3news]

eghie schreef op maandag 18 december 2006 @ 19:17:
Koppel die tabellen ook met relaties aan elkaar. Dus niet alleen tijdens joinen. Hierdoor kun je een stuk betere documentatie maken en kan de database de boel beter optimaliseren.

En hoe maak je die relaties?

[whoami]

Tja.... Het is niet de bedoeling van GoT en PRG om mensen hier bij het handje te gaan houden, en dit topic lijkt daar nu toch al een tijdje op.
Ik bedoel: er zijn hier al veel goede tips gegeven, en als je een beetje moeite wil doen, dan kan je zelf al een en ander gevonden hebben, mocht je eens zoeken op de steekwoorden die je hier oa vind. (Zoals foreign key constraints, relatie, etc...).
Het is niet de bedoeling dat we hier alles gaan voorkauwen, dus daarom doe ik het topic nu uiteindelijk toch dicht. Tipje: snor eens een SQL tutorial / manual / syntax reference op, en kijk eens naar de CREATE TABLE, ALTER TABLE, CREATE INDEX, statements, en zoek eens op hoe je in MySQL mbhv SQL een Foreign KEY constraint kunt maken.