Een Mysql database systeem versnellen / optimaliseren

[Verwijderd]

Ik heb een nieuwssysteempje geschreven in PHP / MYSQL met een Admin deel, waarin nieuwsitems ingevoerd kunnen worden en een User deel dat in de website is verwerkt en dat headlines + links + artikelen kan tonen.

De Database heeft een aantal velden,

nieuws_id
datum
titel
auteur
artikel

nieuws_id is van het int-type en is primary key, datum is een timestamp-veld. Titel en auteur zijn varchar(250) en het artikelveld is een blob veld.

Het werkt allemaal perfect en ook snel, alleen vraag ik me af of en hoe ik deze database zou kunnen optmaliseren als ie gaat groeien.

Hebben jullie misschien ideeën / suggesties?

[Hans]

Lekker tactische database... That's all i have to say...

[Verwijderd]

Als je niks te melden hebt, reageer dan alsjeblieft niet.

[Hans]

Jongen, deze database is te triest voor woorden, en er *valt* niks te optimaliseren (Behalve dan je kennis van relationele databases)

Sorry hoor, maareh....

[Verwijderd]

Sorrie Hans, maar ik vind jouw reactie te triest voor woorden!

Het webdesign forum is altijd een plek geweest waar webdesigners / sitebouwers en fanatieke hobbyisten hun ideeën uitwisselen, vragen stellen + beantwoorden en elkaar op die manier verder helpen.

Geen plaats om elkaar af te zeiken!

[Femme]

Ik heb ook geen verstand van relationale databases.

[Hans]

Om te beginnen:

1. Maak van dat datum veld een datetime veld
2. titel en auteur hebben een veel te groot domein. varchar(50) will do...
3. Maak van artikel een text veld ipv blob. Niet echt nodig om hier een blob van te maken.

Qua indexen valt er hier bar weinig te optimaliseren.

Zo beter? Daarom zei ik hiervoor dat het nergens op slaat, omdat je maar gewoon wat in elkaar geprut hebt. Denk eerst een na wat je allemaal wilt opslaan, en hoe de structuur van je site gaat worden. Krijgt de site users? Waar is de auteur tabel? Hoe ga jij het oplossen als je een artikel wilt opsplitsen in pagina's??

Het valt mij de laatste tijd steeds meer op dat mensen maar gewoon wat gaan zitten hobbien met databases omdat het 'hip' is ofzo, terwijl ze totaal geen kaas hebben gegeten van de meest basic principes van database modelleren en relationele databases. Vandaar mij kritiek.

Ik geef toe, ik had hiervoor effe wat duidelijker kunnen zijn, maar dat moet nu wel gelukt zijn, denk ik

[Verwijderd]

Bedankt Hans,
Dit is opbouwende kritiek. Als het het artikel veld op text zet ipv blob is er dan ook sprake van een maximale lengte van het artikel dat gepost kan worden?

Er hoeft geen aparte user of auteur tabel te komen omdat er maar 2 auteurs zullen zijn die data posten.

Ik heb nog niet veel verstand van relationele databases, maar de vele mogelijkheden van de combinatie Php / Mysql / javascript en html vind ik erg interessant.

[Hans]

De limieten van ieder datatype kan je vinden in de MySQL manual.

De limieten voor de diverse TEXT types zijn:

TEXT
A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters.

MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters.

LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters.


Je kan dus ook nog longtext gebruiken en dan kan je een heel eind wegleuteren (4294967295 characters is wel genoeg denk ik? )

Maar je moet dus rekening houden met de grote van je datatypes (het domein), omdat je database anders achtelijk groot kan worden (nou is dat bij varchar en text niet het geval, maar wel zo netjes.)

[Femme]

Zo'n aparte auteur tabel kan wel erg handig zijn hoor. Daar pleur je bijv. het email adres, password (om in je backend systeempje te kunnen posten), voornaam, achternaam etc. in. Dan linken op auteur ID, veel flexibeler.

Voor de reakties tabel op Tweakers.net zet ik het ICQ nr. e.d. van een user wel hard in de reaktietabel. Dit lijkt me sneller omdat-ie met 1 query alle reakties bij een artikel eruit kan trekken, ipv van voor elke reaktie daarnaast ook aparte queries in de member tabel te doen.

Zoals Hans zegt de lengte van je kolommen altijd zo klein mogelijk houden. Bij een kleine tabel van een paar duizend records zal het niet zoveel uitmaken, maar als je gaat groeien scheelt het zeker. Kleinere tabel = sneller.

Indexes zijn voor high-traffic sites met grote tabellen _erg_ belangrijk. Indexes optimaliseren afhankelijk van de kolommen waarop je op selecteert (tijd, ID), zie mysql manual.

[Burat]

Zo'n aparte auteur tabel kan wel erg handig zijn hoor. Daar pleur je bijv. het email adres, password (om in je backend systeempje te kunnen posten), voornaam, achternaam etc.
in. Dan linken op auteur ID, veel flexibeler.

Hmm als je het NIET doet voldoe je niet eens aan de eerste normal form volgens mij. Je krijgt gigantische update problemen. Als je een naam van een Auteur moet wijzigen moet je dat op ELK veld in de main table doen. Als er ergens een naam fout gespeld staat wordt die niet gewijzigd.. Bout enorm.

Voor de reakties tabel op Tweakers.net zet ik het ICQ nr. e.d. van een user wel hard in de reaktietabel. Dit lijkt me sneller omdat-ie met 1 query alle reakties bij een artikel eruit kan trekken, ipv van voor elke reaktie daarnaast ook aparte queries in de member tabel te doen.

Dat hoeft niet. Je kan dan gewoon een table joinen.

"SELECT * FROM reactie,users WHERE reactie.user_id=users.user_id AND nieuws_id=$nieuws_id;"

Dan krijg je een tabel met reacties op nieuwsnr $nieuws_id met op elke rij een reactie (en alles wat er in de reactietabel staat), een username, icqnr, email adres (en de rest in de user table). En dat alles met 1 query, terwijl het icqnr wel meeverandert als de userinfo verandert!


Verder vind ik persoonlijk dat je eerst moet gaan lezen voordat je iets doet. OK als je win9x wil leren kan je gewoon gaan zitten klooien. Maar als je iets gaat doen waar een bepaalde theorie achter zit, zoals het gebruiken van een relationele database, lees dan eerst die theorie! Als het een eenvoudig iets is mag het best slecht zijn, maar als je eisen stelt als "snel, consistent, klein" dan moet je eerst de achtergrond weten.

Dan had je ook geweten dat een BLOB een Binary Large OBject is - een plaatje ofzo. En dus niet een text.

Verder is het mijns inziens zelfs als er maar 2 auteurs komen interessant een aparte tabel daarvoor te maken. Dan kan je gewoon in de nieuwstabel een kolom "auteur_id" maken. Dan kan je bij het posten van nieuws ook gewoon een listbox maken met de 2 namen.

Verder ben ik het (uiteraard ) helemaal met Hans en Femme eens..

Femme: jij hebt wel verstand van relationele databases, maar alleen vanuit eigen ervaring. Wijs geworden door foutjes, door uitproberen, etc.

[Burat]

Overigens wat is er mis met het datum veld? Als dat een timestamp is, dan is dat als ie op NULL staat de datum waarop de laatste UPDATE plaatsvond. Verder kan je met php makkelijk in een timestamp(10) de huidige tijd invoegen (time()). En weer uitlezen en vervolgens met date("format",$row[datum]) weer neerzetten..

[Arjen]

Ik zou dat ICQ en naam veld toch ook wel appart doen ja, ben wel benieuw of het qua performance wat uit maakt... JOINS zijn wat dat betreft er handig.

Femme: jij hebt wel verstand van relationele databases, maar alleen vanuit eigen ervaring. Wijs geworden door foutjes, door uitproberen, etc.

Hier ben ik het helemaal mee eens. Als ik nu naar m'n eerste database ontwerp terugkijk lag ik me rot. Ik wil niet zeggen dat ik nu een prof ben ofzo, maar ik heb er toch wel aardig wat verstand van. Na een tijdje ga je de MySQL manual eens goed doorlezen, hier en daar een SQL tutorial etc. Leer je ontzettend veel van.

Ben ook van plan een keer een goed boek over datbase design te kopen. Iemand een suggestie?

[Verwijderd]

Okay mensen,

bedankt voor de vele reacties!

[Burat]

Tsja.. ik ken er maar 1, maar die is PERFECT! Dit boek gaat eigenlijk meer over Relationele Databases in het algemeen, de hele theorie en wiskunde erachter (niet extreem moeilijk), alles over mogelijke relaties, theorie van joinen en vele meer van dat soort bewerkingen, normalization (erg belangrijk), technieken om te bekijken of een ontwerp goed is, etc. Daarna komt een praktijk deel met SQL en later ook andere talen, zoals ORACLE enzo. Het is een lesboek dat ik van een vriend heb geleend die 2e jaars informatica is aan de Uni Utrecht.

Info:
Titel: Database Systems - A Practical Approach to Design, Implementation and Management.
Schrijvers: Thomas Connolly, Carolyn Begg, Anne Strachan
Edition: Second
Uitgever: Addison-Wesley
ISBN: 0-201-34287-1

BTW: ik doe nu VWO6, dus het is voor iedereen met een beetje beta-aanleg, goede pc achtergrond (=tweaker) en interesse te begrijpen.

[Arjen]

THNX! Eens kijken of ik m'n baas dat ding kan laten betalen. Kzit zelf ook op 't VWO en intresse heb ik zeker.

[Femme]

Is zo'n join [op de reaktie/members tabel] niet gigantisch traag? In praktisch opzicht heeft 't nauwelijks voordelen.

Volgens mysql 0,1s vs 0,2s.

[Hans]

Burat heeft gelijk (hij heeft gestudeert na een eerder relaas van mij over theorie achter rel. databases )

Als je geen auteurtabel neemt krijg je redundante gegevens, wat zoveel betekend dat gegevens onnodig meerdere keren opgeslagen worden. Dan kom je dus nog niet eens naar de 1e NV (normaalvorm). Als je het goed zou willen doen, zou auteur eigenlijk auteurID moeten worden, en een foreign key relatie moeten zijn met een auteurtabel.

[Hans]

Trouwens, nog ff over die joins. Die table join oplossing is de manier, en joins zijn in per definitie retesnel.

[Verwijderd]

Dat boek van Burat is inderdaad erg goed, maar voor wie wil beginnen is misschien het "SQL Leerboek" ook een idee. Daar heb ik op school (HBO Informatica) SQL uit moeten leren, daarna zijn we pas aan Database Systems begonnen want dat is een stuk zwaarder. Maar ieder zijn smaak, zou ik zeggen.

Even een heel ander punt: Je kan joinen met een WHERE component, maar wat als je meerdere tabellen wil joinen en ook nog eens de WHERE nodig hebt voor een subselectie (bv. alle nieuwsartikelen van 1999). Dan zou ik liever de JOIN component gebruiken. In een eerdere post liep ik te klagen dat ik het niet werkend kreeg onder MySQL, maar het kan wel. Alleen wijkt de syntax af van mijn boek (waarschijnlijk is dat boek gewoon aan het verouderen ).

SELECT * FROM artikel
LEFT JOIN soort ON artikel.soort_id = soort.auteur_id
LEFT JOIN merk ON artikel.merk_id = merk.auteur_id
WHERE >> EXTRA SELECTIE-CRITERIA <<

Iemand die hier bezwaar tegen heeft, en zo ja, waarom?

Tenslotte nog een vraagje naar aanleiding van indexes: Wanneer is een tabel zo groot dat je ze nodig hebt? Zijn daar regels voor? Een artikelbestand van 100 artikelen? En van 1000? 10000? Wanneer? Alvast dank.

[Verwijderd]

En nou vergeet ik nog mijn belangrijkste vraag

Hoe kan je onder MySQL in hemelsnaam integriteitsregels afdwingen? Als ik in de documentatie kijk staat er dat FOREIGN KEYS niet gebruikt worden om explicite koppelingen aan te leggen en bij het aanmaken van records te kijken of een verwijzing naar een andere tabel geldig is.
Ik kan dit via PHP laten controleren dus dat valt nog wel te omzeilen, maar ideaal is anders. Erger nog, als ik een externe db als ascii ga inlezen vanuit MySQL, wat dan?
Voorlopig ben ik er niet echt blij mee.

[Verwijderd]

Heel erg versimpeld zou je een eigen tabel met relaties kunnen definieren in de trend van:

tabel | veldnaam | reftabel | refveldnaam | relatietype


In deze tabel kun je dan tabellen met velden aan mekaar knopen. Voordat je dan ooit een insert of update doet kijk je in deze tabel om te zien of er een relatie voor bestaat, en wat voor relatie (zoals 1 to 1, 1 to many).

Het is natuurlijk in eerste instantie sneller om deze logica in je script te verwerken, maar als je in de toekomst je relaties veranderd of uitbreid zou je al je programmeerwerk moeten nakijken, en is het erg gemakkelijk om er een te vergeten.

Met de structuur als hierboven kun je erg gemakkelijk nieuwe relaties neerzetten. Inserts, updates en deletes worden natuurlijk wel wat langzamer omdat ie eerst moet kijken of er een relatie bestaat, zoja, kijken of er een bijbehorend record in de reftabel staat, etc). Dat is de prijs die je moet betalen voor referential integrity .

Op je eerdere vraag 'wanneer je indexes moet hebben' is volgens mij het antwoord dat je altijd velden die je in je WHERE criteria gebruikt moet indexeren. Bij het ontwerpen van een database moet je hem altijd zo optimaal maken dat je er gigantisch grote hoeveelheden informatie in kwijt kan, ook al zou dit in eerste instantie niet gebeuren. Dus niet in het begin zeggen, 'de tabel is nog zo klein dat ik geen index nodig heb', en een beetje diskspace besparen, maar het gelijk goed doen.

Lucas

[Burat]

FOREIGN KEYS kan je wel aangeven, maar de definitie daarvan is alleen voor compatibiliteit, het doet niets. Je moet de links tussen een primary key in de ene tabel en een foreign key in de andere tabel zelf leggen!

Hans: een relaas overigens dat niet *tegen* mij gericht was maar tegen een ander persoon die het niet nodig vond iets te leren. Op dat moment was ik nog pas op de eerste 200 blz van m'n boek en kon ik nog niet uitleggen wat normalization was enzo. .

Verder blijf ik het irritant vinden dat je niet kan selecteren dat je de threads van de afgelopen x dagen wilt zien..

[Egbert]

Gooi ik er ook nog maar wat vraagjes tegenaan:
-zoeken gaat trager naarmate meer records aanwezig zijn, maar gaat t ook trager als elk record meer/grotere gegevens bevat?
-> is het zinvol velden die niet veel gebruikt worden, maar wel groot zijn, af te splitsen?
-voor zoeksnelheid is het over het algemeen handiger een vaste-recordlengte te hebben? Hoewel je daardoor wel grotere tabellen
krijgt? (en dit ook in combinatie met vorige punt?)
-> is er een soort verhouding (te bepalen) wanneer je voor het een of ander moet kiezen?

Egbert.

[Verwijderd]

Voor zover ik weet:

vraag1: Nee, want als dit veld geindexeerd is staat hij in een andere fysieke file..

vraag2: Ja, ik zou dat zeker doen. In dat geval heb je in mysql ook met een andere fysieke file te maken, dus als die weinig gebruikt wordt, kun je je 'hoofdfile' kleiner en sneller houden.

vraag3: Denk ik niet, omdat die extra ruimte met NULL gevuld wordt en de fysieke file dus weer groter wordt.

vraag4: Logisch nadenken. Hoe kan je de informatie structureel zo efficient mogelijk opslaan. In het geval van velden die weinig gebruikt worden moet je afwegen wat verstandiger is. Is het 'duurder' om nog een query te doen om de bijbehorende informatie op te zoeken, of is het duurder om de informatie in een andere tabel op te slaan.

Heeft iemand hier andere ideeen over?

[Femme]

Ik heb er ook al over zitten denken om bijv. de textvelden in de nieuws tabel te splitten in een aparte tabel, ik vraag me alleen af in hoeverre dat zin heeft. Text en overige velden (Author, Time, Categorie) moet er vrij wel altijd tegelijkertijd uit gepoept worden. Sorteren gaat al op een index, dus dat zou niet sneller moeten zijn met een kleinere tabel zonder textvelden.

[Egbert]

Hmm, volgende keer misschien toch maar éérst de handleiding lezen... alhoewel, dan hadden anderen er niks aan gehad (en nu misschien wel )
Volgens de MySQL reference is het niet handig/nodig velden die groot zijn, en niet vaak gebruikt worden af te splitsen BEHALVE als dat variabele lengte velden zijn. Het meeste tijd bij een select is nl het vinden van het begin van een record. Bij variabele lengte velden, krijgt de hele record een variabele lengte, en is het veel lastiger zomaar een stap verder te doen naar de volgende.
Maar als die variabele lengte velden wél (bijna) even vaak nodig zijn, moet je ze juist wél in de 'hoofd'-tabel laten staan. Voor een select hoeft ie dan maar één record-start-positie te zoeken, ipv twee (in de twee losse tabellen).
Daar komt dan nog bij, dat de disks van tegenwoordig een record in één 'leesslag' lezen, als je data splitst in twee tabellen heeft ie dus minstens dubbel zoveel (twee) 'leesslagen' nodig..

Egbert.

[Femme]

Ik heb in m'n nieuwscript zitten goochelen met het joinen van tabellen, maar echt sneller werd het er niet op:

select news.ID,Header,Text1,Quote,Text2,Cat1,Source,Link,Time,Voornaam,Achternaam,Email,ICQ,Webcam from news,authors where news.ID=$ID and news.Author=authors.ID limit 1

Had tot gevolg dat de mysqld load omhoog ging van 6-9% naar 10-16%.

Nu gebruik ik gewoon weer de botte manier van eerst author ID uit news halen en dan apart query'en voor author gegevens.

[Hans]

Maar dan heb je dus twee queries nodig, en das meer werk, en in principe een grotere belasting voor de database server, maar dan verspreid over een langer tijdsbestek, dus eigenlijk 2x 6-9% en dan is een join dus sneller.

Even OT: vanwaar de LIMIT 1? Beetje nutteloos niet? of je moet je database niet vertrouwen...

[Egbert]

Zou het niet ook helpen om die join op een wat andere manier te doen:
iets als dit:
"select news.ID, Header, Text1, Quote, Text2, Cat1, Source, Link, Time, Voornaam, Achternaam, Email, ICQ, Webcam from news LEFT JOIN authors ON news.Author=authors.ID where news.ID=$ID"

Het verschil is -volgens mij- dat bij deze methode alleen gekeken wordt of er in de authors-tabel 'bijpassende' gegevens staan, en niet andersom. Dus dat ie eerst iets vind in de news-tabel, en dan pas gaat kijken in de author-tabel, en niet in beide richtingen.
(of is dit helemaal onzin?)

Egbert.

Hans-> wat die load betreft: dat lijkt me onzin.. het zijn gemiddelde loads... het 'twee keer zoeken' is daar gewoon in verwerkt.

[Hans]

Maar 't is niet irreeel. Bij twee queries moeten er twee queries geparsed worden, waar volgens mij de meeste processorbelasting in zit by MySQL (or any SQL server). Moet toegeven dat een query bij een join altijd langer is en meer elementen bevat, en het dus ook langer duurt om te parsen.

Ik zal eens op zoek gaan naar exacte informatie over wat nu sneller is. Er zijn ooit wel benchmarks voor uitgevoerd nml.

[Femme]

Die 10-16% is de load van mysqld, gezien vanuit top. Ik weet niet de exacte CPU tijd die in de query nodig heeft, maar de toename van de totale load was wel erg opvallend.

Nu ik 'm terug het gezet naar het script met twee queries is de load weer terug op het oude niveau.

[Arjen]

Mmm 1 query ipv 1 + aantal berichten bij het nieuwsopverzicht. Wat is sneller? Denk toch het eerst, ookal wordt de load hoger. Misschien gaat het memory gebruik van PHP (Apache) wel omlaag en draait het sneller.

Verder is die LEFT JOIN iets sneller dan gewoon WHERE news.authorid=authors.id (~ 1.53 vs. 1.43 sec voor 9104 records)

[Femme]

Je bedoelt voor de frontpage?

Dat werkt niet helemaal want hij dan selecteert-ie geen nieuwspostings waar nog geen reakties op zijn (omdat $Tabel en $TabelID dan niet voorkomen in de reaktiestabel).

[Verwijderd]

In dat soort gevallen (hij neemt niets mee omdat de gerefereerde tabel niets bevat) gebruik je toch voor de zekerheid een outer join? Bestaat die eigenlijk in MySQL?. Hmmmm...

[Verwijderd]

Een 'full outer join' en dergelijke bestaan absoluut in mysql. Alleen bevat je koppeltabel een hoop velden, zodat je na afloop een hoop te filteren hebt. (Oke, als je 'where' gebruikt, krijg je dezelfde grote tabel, maar toch.

Gebruik die alleen als een normale join (SELECT * FROM artikel LEFT JOIN soort ON artikel.soort_id = soort.soort_id) niet werkt...