[mysql] Index nodig als synthetische keys worden gebruikt?

[DutchAddick]

Ik gebruik standaard voor al mijn tabellen synthetische keys (user_id, klant_id, etc.), eigenlijk als een soort gewoonte. Daarnaast gebruik ik eigenlijk nooit tabel indexen, vooral omdat mijn sites niet super veel bezoekers hebben, waardoor er geen performance problemen zijn (slecht excuus, I know). Nu ga ik een site bouwen met (hopelijk) veel meer bezoekers en ik vraag me dus af in hoeverre tabel indexen nodig zijn als je altijd gebruik maakt van synthetische keys?

En als je toch gebruik gaat maken van een index, welke column(s) kun je dan hiervoor het beste gebruiken, de column die je waarschijnlijk het vaakst gaat gebruiken in het WHERE gedeelte van queries?

[cariolive23]

Daarnaast gebruik ik eigenlijk nooit tabel indexen

Weet je het zeker? Een primary key of een UNIQUE-constraint zijn ook indexen en deze worden bijzonder vaak gebruikt.

Als uitgangspunt voor het aanmaken van een index kun je de kolommen nemen die in de WHERE of ORDER BY staan en veel unieke waardes bevatten. Met EXPLAIN kun je achterhalen of je goed bezig bent, je kunt ook zien wat de verschillen zijn tussen de verschillende mogelijkheden. Gecombineerde indexen kunnen de boel verder optimaliseren, maar dat moet je goed controleren. Voor je het weet bereik je het tegenovergestelde...

EXPLAIN is onmisbaar bij het optimaliseren, gebruik deze dan ook. Het kan ook handig zijn om een auto-explain in je applicatie aan te maken, dan laat je alle queries automatisch explainen en de resultaten wegschrijven naar een bestandje. Kun je vervolgens op je gemak gaan optimaliseren.

[Voutloos]

WHERE en ORDER BY clauses bevatten meestal de kandidaten. Binnen de where zoek je de kolom welke het beste filtert (kardinaliteit), en order by kijk je naar om filesorts te voorkomen. En er zijn wel meer patronen welke om een index vragen, maar daar is genoeg leesvoer voor te vinden. Speel een keer bij je belangrijkste queries met EXPLAIN.

[Janoz]

Een index kun je het beste vergelijken met bijvoorbeeld de index van een boek (Vandaar dat de namen ook op elkaar lijken). Als je een woord zoekt kun je het hele boek van voor naar achter doorzoeken, maar je kunt ook even in de index kijken en dan gelijk naar het juiste pagina nummer springen. Zo werken indexen in databases ook. Het voordeel is dus dat het zoeken op die kolom een heel stuk sneller gaat, maar aanpassingen in de tabel zijn ietsje pietsje duurder (niet alleen het boek moet worden aangevuld, maar ook de index moet worden aangepast).

In het kort komt het er dus op neer: Voor velden waar vaak op wordt gezocht (bv omdat het de primary key of een foreign key is) is een index aan te raden.

[DutchAddick]

Janoz schreef op dinsdag 30 juni 2009 @ 09:10:
Een index kun je het beste vergelijken met bijvoorbeeld de index van een boek (Vandaar dat de namen ook op elkaar lijken). Als je een woord zoekt kun je het hele boek van voor naar achter doorzoeken, maar je kunt ook even in de index kijken en dan gelijk naar het juiste pagina nummer springen. Zo werken indexen in databases ook. Het voordeel is dus dat het zoeken op die kolom een heel stuk sneller gaat, maar aanpassingen in de tabel zijn ietsje pietsje duurder (niet alleen het boek moet worden aangevuld, maar ook de index moet worden aangepast).

In het kort komt het er dus op neer: Voor velden waar vaak op wordt gezocht (bv omdat het de primary key of een foreign key is) is een index aan te raden.

OK, als ik bv een user_id instel als primary key, is dat dan ook automatisch al een index, of moet dat nog apart?

[CodeCaster]

CAFC1905 schreef op dinsdag 30 juni 2009 @ 10:07:
[...]


OK, als ik bv een user_id instel als primary key, is dat dan ook automatisch al een index, of moet dat nog apart?

Een béétje lezen kan geen kwaad De eerste hit op [google=mysql+primary+key]:

quote: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

[GlowMouse]

Voutloos schreef op dinsdag 30 juni 2009 @ 09:06:
WHERE en ORDER BY clauses bevatten meestal de kandidaten. Binnen de where zoek je de kolom welke het beste filtert (kardinaliteit), en order by kijk je naar om filesorts te voorkomen. En er zijn wel meer patronen welke om een index vragen, maar daar is genoeg leesvoer voor te vinden. Speel een keer bij je belangrijkste queries met EXPLAIN.

En http://dev.mysql.com/doc/...tiple-column-indexes.html heel goed doorlezen. Het is bijvoorbeeld bijna compleet nutteloos om een multi-column index te zetten op (colA,colB) voor een query als SELECT colA,colC FROM tbl WHERE colA<12345 ORDER BY colB.