[SQL]Index in koppeltabel

[van.der.schulting]

Ik zat gisteren na te denken en toen begon het me een beetje te duizelen. Volgens mij heb ik het inmiddels helder, maar ik ben niet helemaal zeker.

Ik heb een tabel 'advertisements' en een tabel 'brands'. Deze tabellen zijn gekoppeld via de tabel 'advertisements_brands' (een has and belongs to many relatie dus )

De tabel advertisements_brands heeft twee kolommen: advertisement_id en brand_id.

Ik heb een index gezet op beide kolommen. Mijn redenatie is als volgt. Op deze manier kan ik sneller voor een advertentie de bijbehorende brand vinden en voor een brand kan ik dan sneller de bijbehorende advertenties vinden. Het heeft volgens mij geen enkele zin om een afzonderlijke indezx te zetten lop brand_id en een afzonderlijke index op advertisement_id. Volgens mij is het bij een koppeltabel sowieso alleen zinvol om een index te zetten op BEIDE kolommen en niet op de kolommen afzonderlijk.

Klopt het wat ik zeg of sla ik de plank volledig mis?

[RobIII]

Een index is, in essentie, vergelijkbaar met de werking van een telefoonboek; als je tabel 5 miljoen records bevat en je wil er bepaalde records uit halen die aan een criterium voldoen dan heb je die sneller gevonden als ze "op volgorde" staan dan wanneer ze kris-kras door elkaar staan (lees: zonder index => Full table scan).

Stel je nu eens voor dat je advertisements_brands voornaam_achternaam zou heten en je zet een index op voornaam + achternaam. Dat betekent dat je een telefoonboek hebt waar op voornamen gesorteerd is en binnen dezelfde voornaam zijn de achternamen gesorteerd. Ga nu eens zoeken naar alle Janssens
Je hebt dan wel, voor elke voornaam, relatief snel bepaald of er een achternaam die voldoet aan je criterium voldoet voorkomt en zo ja, hoeveel/welke, maar je moet wel nog elke voornaam afgaan daarvoor. Als je maar een beperkt aantal voornamen (stel je even voor: 4) hebt is dit nog prima te doen omdat je dan "4 subindexen" hoeft af te gaan. In performance zul je daar niet heel snel iets van merken. Echter als de variatie in voornamen groot is (zeg, 25.000) dan zul je al die 25.000 subindexen af moeten gaan (dan laat ik even slimme truukjes achterwege die DB's soms (kunnen) gebruiken).

Nu de andere manier: zet eens 2 indexen; 1 op voornaam en 1 op achternaam. Nu heb je twee "telefoonboeken", in 1 daarvan kun je verdomd efficiënt een voornaam vinden (en alle bijbehorende achternamen) en in de ander kun je verdomd efficiënt een achternaam vinden (en alle bijbehorende voornamen).

Wat zou jij doen?

[ Voor 29% gewijzigd door RobIII op 26-07-2011 11:21 ]

[Voutloos]

Variant 3:
Een index op (A,B) en eentje op (B). Als alleen A bekend is, of A en B wordt de 1e index gebruikt, en als je enkel B hebt de 2e.

Maar uiteindelijk hangt het wel van de queries en kardinaliteit (hetgeen rob over 4 of 25.000 mogelijke waardes zegt) af. Dus bekijk ook eens hoe bepaalde queries uitgevoerd worden.

[RobIII]

Voutloos schreef op dinsdag 26 juli 2011 @ 11:24:
Variant 3:
Een index op (A,B) en eentje op (B). Als alleen A bekend is, of A en B wordt de 1e index gebruikt, en als je enkel B hebt de 2e.

Variant 4:
Een index op (A, B) en eentje op (B, A)
Wat je ook kiest uiteindelijk: als je de basiskennis van indexen zoals ik die (versimpeld) weergaf meeneemt moet je uiteindelijk wel tot een goede oplossing kunnen komen. Hou er rekening mee dat indexen leuk zijn om de performance voor selects te verhogen maar dat een insert/update wel met elke index die je toevoegt duurder wordt want die moet bij elke mutatie natuurlijk (potentieel) bijgewerkt worden.

Er zijn overigens nog verschillende index types; zorg dat je je in die materie ook even een beetje verdiept voordat je er één kiest: een "clustered index" is bijvoorbeeld relatief duur om bij te werken maar wel weer een tikkie sneller (kort door de bocht genomen ) (en je mag er maar 1 op een tabel hebben bijvoorbeeld).

Overigens: het gros van deze info is hier te lezen.

edit:
Met overigens een behoorlijk gelijkend _{maar wel beter gekozen} voorbeeld

[ Voor 13% gewijzigd door RobIII op 26-07-2011 11:38 ]

[van.der.schulting]

RobIII schreef op dinsdag 26 juli 2011 @ 11:09:
Nu de andere manier: zet eens 2 indexen; 1 op voornaam en 1 op achternaam. Nu heb je twee "telefoonboeken", in 1 daarvan kun je verdomd efficiënt een voornaam vinden (en alle bijbehorende achternamen) en in de ander kun je verdomd efficiënt een achternaam vinden (en alle bijbehorende voornamen).

Wat zou jij doen?

Gotcha, ik sloeg de plank dus toch mis. Het is dus veel beter om in een koppeltabel op beide kolommen afzonderlijk een index te zetten. Thanks

[RobIII]

van.der.schulting schreef op dinsdag 26 juli 2011 @ 12:22:
Het is dus veel beter om in een koppeltabel op beide kolommen afzonderlijk een index te zetten. Thanks

Dat zou ik niet te zwart/wit stellen; in jouw geval is dat echter waarschijnlijk een goed idee ja

[van.der.schulting]

RobIII schreef op dinsdag 26 juli 2011 @ 12:29:
[...]

Dat zou ik niet te zwart/wit stellen; in jouw geval is dat echter waarschijnlijk een goed idee ja

Je bedoeld bijvoorbeeld als je een koppeltabel voor 3 tabellen hebt ipv 2 (zoals nu) of een koppeltabel met een extra veld met een waarde.

[RobIII]

van.der.schulting schreef op dinsdag 26 juli 2011 @ 14:12:
[...]

Je bedoeld bijvoorbeeld als je een koppeltabel voor 3 tabellen hebt ipv 2 (zoals nu) of een koppeltabel met een extra veld met een waarde.

Nee ik bedoel dat in jouw geval gezocht kan/moet worden op een van beide FK's. In gevallen waar dat niet zo is (en er dus, bijv., gezocht wordt op 1 van de FK's) is het toevoegen van 2 indices natuurlijk weinig nuttig

[GlowMouse]

RobIII schreef op dinsdag 26 juli 2011 @ 11:09:
Als je maar een beperkt aantal voornamen (stel je even voor: 4) hebt is dit nog prima te doen omdat je dan "4 subindexen" hoeft af te gaan.

Helaas doet MySQL dat niet.

TS heeft al één unique/PK index nodig over beide kolommen, die mis ik in dit topic.

[SPee]

En volgens mij worden van de FK ook indexen gemaakt.

Dus:
1 FK index op kolom 1
1 FK index op kolom 2
1 PK index op kolom 1 en 2

(Als je netjes FK constraint gebruikt)

[wallyberk]

GlowMouse vraagje: hoe weet jij, dat de poster het over MySQL heeft? Ik kan niet uit zijn post afleiden.

Even aannemen dat dit over MySQL (InnoDb) gaat.

Om zaken nog iets compliseerde te maken, als je de index op beide kolomen uniek maakt ( wat het geval is bij een PK), dan kan het zijn dat de index als clustered wordt opgelagen. Het dan onzinnig om een extra index op de eerste kolom van de plaatsen, omdat de tabel al gesorteerd is.
bron: http://dev.mysql.com/doc/...n/innodb-index-types.html

Tevens is het aantal unieke brands ook van belang.
Als je bij 1 brand te veel van het aantal records ophaald (wat er al snel gebeurd als je te weinig unieke brands hebt), kan krijg je als nog een full table scan en is je index dus nutteloos.
Je kan zelf bepalen of te veel records haalt, door het max aantal advertisements bij brands en die tegen de volgende variable te houden: http://dev.mysql.com/doc/...#sysvar_max_seeks_for_key

[GlowMouse]

wallyberk schreef op zaterdag 30 juli 2011 @ 21:47:
GlowMouse vraagje: hoe weet jij, dat de poster het over MySQL heeft? Ik kan niet uit zijn post afleiden.

Dat weet ik niet, neem ik ook niet aan in mijn post, maar het zou een veilige aanname geweest zijn lettende op de pohi van TS.

Even aannemen dat dit over MySQL (InnoDb) gaat.

Om zaken nog iets compliseerde te maken, als je de index op beide kolomen uniek maakt ( wat het geval is bij een PK), dan kan het zijn dat de index als clustered wordt opgelagen. Het dan onzinnig om een extra index op de eerste kolom van de plaatsen, omdat de tabel al gesorteerd is.
bron: http://dev.mysql.com/doc/...n/innodb-index-types.html

Die kans is 1 zonder andere unique index of PK. Maar ook als het geen clustered index is, is het onzinnig om een extra index op de eerste kolom te maken.