sql- indexen

[HenkS]

hallo,

ik werk met asp en php deze vraag geldt dus voor sql-server en mysql

maar wanneer gebruik je nu precies een enkele index, en wanneer maak je geclusterde indexen aan?

[Verwijderd]

De term geklusterde index zegt me eigenlijk niks, maar als ik die optie kreeg dan zou ik ervoor kiezen als mijn sleutel uit een aantal foreign keys zou bestaan.

[Onno]

[hier stond geblaat]

[whoami]

Je kan maar 1 'clustered index' per table leggen in SQL Server.

Een Clustered index kan je goed gebruiken als je een 'range' van gegevens wilt ophalen terwijl als je slechts 1 record ophaalt, een selectieve query doet dus, je beter een non-clustered index gebruikt.

[CubicQ]

En in MySQL (in ieder geval bij InnoDB) is de primary key per definitie een clustered index. (http://www.mysql.com/doc/T/a/Table_and_index.html)

Wat ik heb gelezen zonet bepaald een clustered index de volgorde waarin de table fysiek wordt opgeslagen, dus dat is dan de reden waarom 'range-selects' daar vooral voordeel bij hebben.

[whoami]

Op maandag 26 november 2001 21:47 schreef CubicQ het volgende:
En in MySQL (in ieder geval bij InnoDB) is de primary key per definitie een clustered index. (http://www.mysql.com/doc/T/a/Table_and_index.html)

Wat ik heb gelezen zonet bepaald een clustered index de volgorde waarin de table fysiek wordt opgeslagen, dus dat is dan de reden waarom 'range-selects' daar vooral voordeel bij hebben.

Inderdaad, het heeft te maken met de manier waarop de onderste leaf van het B-Tree bestand wordt opgeslagen, maar ik heb geen zin om die hele engelse reutemeteut nu te gaan lezen en bestuderen.

[Annie]

Op maandag 26 november 2001 21:39 schreef whoami het volgende:
... terwijl als je slechts 1 record ophaalt, een selectieve query doet dus, je beter een non-clustered index gebruikt.

Op maandag 26 november 2001 21:48 schreef whoami het volgende:

... maar ik heb geen zin om die hele engelse reutemeteut nu te gaan lezen en bestuderen.

Misschien is het toch verstandig als je dat even gaat doen, want wat je in je eerdere post zet is maar ten dele waar.
Ook voor single-row queries is een clustered index uitermate geschikt.

De reden waarom je maar 1 enkele clustered index aan kan leggen is natuurlijk omdat deze de fysieke ordening van je table beinvloed (en dan wordt het een beetje moeilijk wegschrijven als je er meerdere hebt). Een clustered index kan natuurlijk wel over meerdere kolommen aangelegd worden (als je dat zou willen).

Op maandag 26 november 2001 20:12 schreef HenkS het volgende:
maar wanneer gebruik je nu precies een enkele index, en wanneer maak je geclusterde indexen aan?

Bedoel je hier nou het verschil tussen een clustered index en een nonclustered index.
Of bedoel je het verschil tussen een index over 1 kolom of eentje over meerdere kolommen?

[HenkS]

dus als ik het goed begrijp heb je geen keus??

want een primary key heb je bijna altijd, dus dan heb je automatisch een clustered index.... dus kun je alleen nog maar single indexen aanmaken voor andere velden, of zie ik dat nu verkeerd?

[dusty]

Op dinsdag 27 november 2001 08:59 schreef HenkS het volgende:
dus als ik het goed begrijp heb je geen keus??

want een primary key heb je bijna altijd, dus dan heb je automatisch een clustered index.... dus kun je alleen nog maar single indexen aanmaken voor andere velden, of zie ik dat nu verkeerd?

clustered index != multiple column index.

[HenkS]

ohhhhh.......

dus in feite moet ik multiple column indexen in verband brengen met single indexen...

ok dan ga ik ff door met mijn vraag:

wanneer gebruik je dan multiple en wanneer single column indexen

(thanks dusty)

[Greyfox]

Op dinsdag 27 november 2001 08:59 schreef HenkS het volgende:
dus als ik het goed begrijp heb je geen keus??

want een primary key heb je bijna altijd, dus dan heb je automatisch een clustered index.... dus kun je alleen nog maar single indexen aanmaken voor andere velden, of zie ik dat nu verkeerd?

Clustered en non-clustered indexen zijn typen indexen.
Dat staat helemaal los van een index over 1 of over meer columns (composite index)

[Greyfox]

Op dinsdag 27 november 2001 09:06 schreef HenkS het volgende:
wanneer gebruik je dan multiple en wanneer single column indexen

Je gebruikt een composite index (over meerdere columns) als je denkt dat 1 column niet unique genoeg is om veel voordeel uit de index te halen.

[dusty]

Op dinsdag 27 november 2001 09:06 schreef HenkS het volgende:
wanneer gebruik je dan multiple en wanneer single column indexen

Single: Ik zoek informatie die bij een user past adhv een userid... index op USERID.

Multiple: Ik zoek een userid adhv een username en wachtwoord. index op ( username, password )

In principe gebruik je de index als je er vaak op zoekt. (anders is het alleen maar extra overhead)..

Simpele regel is dat je Multiple Columns Index gebruikt als je altijd zoekt in een bepaalde tabel naar een combinatie van twee of meerdere kolommen. ( dus zoals de username + password voorbeeld..)

[HenkS]

ok en als je dan bv zoiets hebt:

je hebt een tabel met userinfo, dus ook NAW gegevens, maar er staat ook wachtwoord in dat veld.

als mensen moeten inloggen, moet je dus naam en wachtwoord hebben, dan zou je zeggen multiple index...

maar ergens anders op de site wil je bv de NAW gegevens hebben, dan wil je dus naam, adres, woonplaats hebben bijvoorbeeld, en niet meer wachtwoord..

hoe zou je deze tabel dan opzetten qua indexen?

[Onno]

Op dinsdag 27 november 2001 09:06 schreef HenkS het volgende:
wanneer gebruik je dan multiple en wanneer single column indexens

Wanneer dat je queries maar versnelt. Want dat is het hele doel van indices.

code:

select * from tabel where a=? and b=?

Bij zo'n query zou een index over a en b handig kunnen zijn. (hangt een beetje af van de situatie, als a of b (nagenoeg) uniek is, heeft het weinig nut bijvoorbeeld)

code:

select * from tabel order by a,b

Ook bij zo'n query zou een index over a en b je snelheidswinst kunnen opleveren.

Op dinsdag 27 november 2001 09:09 schreef dusty het volgende:
Multiple: Ik zoek een userid adhv een username en wachtwoord. index op ( username, password )

Uhh... ik hoop dat dit gewoon een heel slecht gekozen voorbeeld is, want dit doe je toch niet echt he?

Op dinsdag 27 november 2001 09:12 schreef HenkS het volgende:
als mensen moeten inloggen, moet je dus naam en wachtwoord hebben, dan zou je zeggen multiple index...

Nee. Het gaat er bij een index niet om wat je select, maar waar je op zoekt of sorteert. In dit geval zoek je naar usernaam (of -id), een enkele index op die kolom is dus genoeg.

maar ergens anders op de site wil je bv de NAW gegevens hebben, dan wil je dus naam, adres, woonplaats hebben bijvoorbeeld, en niet meer wachtwoord..

En diezelfde index is hier nog steeds de enige nodige.

[dusty]

Op dinsdag 27 november 2001 09:14 schreef Onno het volgende:
Uhh... ik hoop dat dit gewoon een heel slecht gekozen voorbeeld is, want dit doe je toch niet echt he?

Het is een voorbeeld waar mensen hier zich snel iets bij kunnen voorstellen, daarom heb ik het gekozen, Zelf doe ik het niet omdat mensen maar EEN keer inloggen per sessie, en die query komt dus relatief niet vaak voor.

Nee. Het gaat er bij een index niet om wat je select, maar waar je op zoekt of sorteert. In dit geval zoek je naar usernaam (of -id), een enkele index op die kolom is dus genoeg.

Ehh.. ik zei ook je zoekt op username en wachtwoord? (altijd namelijk, gebruiker vult namelijk dan altijd een username met wachtwoord in...)

En diezelfde index is hier nog steeds de enige nodige.

Ehh, Tabel met 1.000.000 records, en men zoekt alleen op adres, en dat is relatief 50% het geval.
Wat zou jij dan willen doen met alleen een index op usernaam?

[Onno]

Op dinsdag 27 november 2001 09:40 schreef dusty het volgende:
Het is een voorbeeld waar mensen hier zich snel iets bij kunnen voorstellen, daarom heb ik het gekozen, Zelf doe ik het niet omdat mensen maar EEN keer inloggen per sessie, en die query komt dus relatief niet vaak voor.

Ehh.. ik zei ook je zoekt op username en wachtwoord? (altijd namelijk, gebruiker vult namelijk dan altijd een username met wachtwoord in...)

Als je op twee kolommen zoekt waarbij de ene uniek is, is het *onzinnig* om de index uit te breiden naar de tweede. Geen enkele snelheidswinst.

en men zoekt alleen op adres,

Er staat nergens dat hij dat doet. Er staat dat hij het adres wil *opvragen*. Dat is heeeeel wat anders dan er op zoeken.

[HenkS]

Nee. Het gaat er bij een index niet om wat je select, maar waar je op zoekt of sorteert. In dit geval zoek je naar usernaam (of -id), een enkele index op die kolom is dus genoeg.

je zoekt dan toch of de combinatie van die user en dat wachtwoord voorkomen??? dan heb je toch multiple index nodig? als je een hele grote db hebt met superveel users, dan gaat het met een multiple index toch veel sneller....


oh ja ik zou ook nog graag antwoord hebben op mijn vraag mbt tot die NAW gegevens!, of kan ik daar eigenlijk zelf antwoord op gegeven, want in dat geval zoek je dus op username, of op id, en niet op de rest van de velden, want die krijg je terug, maar zoek je niet op, dus is in dit geval single index genoeg???

edit:

laat zooi over username en ww maar zitten, had vorige topic van Onno nog niet gelezen, en daar zit wel wat in ja

maar je hebt toch wel eens dat je soms op 1 veld wilt zoeken van een bepaalde tabel, en soms op 2 velden? wat doe je in dit geval qua index?

offtopic:
zou je me je aan je icq willen toevoegen onno
36224063 (erg dat ik dit uit mijn hoofd weet zeg)

[dusty]

Op dinsdag 27 november 2001 09:45 schreef Onno het volgende:
Als je op twee kolommen zoekt waarbij de ene uniek is, is het *onzinnig* om de index uit te breiden naar de tweede. Geen enkele snelheidswinst.

Zoals ik al zei, Ik had gekozen voor dat voorbeeld omdat mensen hier over het algemeen allemaal wel een keertje een Login systeem hebben gemaakt, en zich er dus iets bij kunnen voorstellen.

Er staat nergens dat hij dat doet. Er staat dat hij het adres wil *opvragen*. Dat is heeeeel wat anders dan er op zoeken.

Klopt, had zijn vraag verkeerd gelezen.

[Onno]

Op dinsdag 27 november 2001 09:54 schreef dusty het volgende:
Zoals ik al zei, Ik had gekozen voor dat voorbeeld omdat mensen hier over het algemeen allemaal wel een keertje een Login systeem hebben gemaakt, en zich er dus iets bij kunnen voorstellen.

Maar dat doet niets af aan het feit dat een index over beide kolommen in dat geval *niet* nuttig is, en het dus een belabberd voorbeeld is. (dit is waar je zelf al voor waarschuwde: alleen maar extra overhead)

Alleen een bekende situatie geven is niet genoeg, 't moet ook nog van toepassing zijn.

[Creepy]

En natuurlijk niet vergeten dat indexen dan wel helpen qua snelheid tijdens het opvragen van gegevens, maar ook vertraging geven tijdens het toevoegen of verwijderen van gegevens. Want elke keer als de data verandert, moet de index ook worden bijgewerkt.

Dus als je een tabel hebt waar veel in wordt bewerkt, kijk dan ff heel goed naar je indexen! Hoe minder indexen, hoe sneller het is om gegevens toe te voegen, te verwijderen of aan te passen (als het aan te passen veld in de index zit).

[dusty]

Op dinsdag 27 november 2001 09:59 schreef Onno het volgende:
Maar dat doet niets af aan het feit dat een index over beide kolommen in dat geval *niet* nuttig is, en het dus een belabberd voorbeeld is. (dit is waar je zelf al voor waarschuwde: alleen maar extra overhead)

Yup, al is het alleen maar belabberd omdat je dus normaal een keer per bezoeker maar die username/wachtwoord hoeft te controleren

[Annie]

Op dinsdag 27 november 2001 08:59 schreef HenkS het volgende:
dus als ik het goed begrijp heb je geen keus??

want een primary key heb je bijna altijd, dus dan heb je automatisch een clustered index.... dus kun je alleen nog maar single indexen aanmaken voor andere velden, of zie ik dat nu verkeerd?

Om nog even hier op terug te komen. Natuurlijk heb je wel een keus. Als jij wil dat de clustered index op een andere kolom komt te liggen dan kan dat.

Voor het aanleggen van indices zijn wel wat richtlijnen waar je op moet/kan letten (een aantal daarvan zijn al genoemd in dit topic), maar de praktijk blijft altijd de beste leraar. Een index die op papier voor verbetering moet zorgen en logisch lijkt kan in de praktijk best fout uitpakken. Daarom is het van belang dat je voor en na de aanleg van indices altijd het gedrag van de database in kaart brengt, zodat je kan zien wat het netto-resultaat is.
Zo kan een index op een tabel waar veel in ge-update wordt juist in sommige gevallen wel veel snelheidswinst opleveren (alhoewel vaak per definitie wordt aangenomen dat dat niet zo is).

Het optimaliseren van een database houdt natuurlijk niet op bij het aanleggen van een indexje op kolom X. Je zal vooraf goed moeten kijken naar de structuur van de database en naar de applicatie(s) die daar gebruik van maken. En je zal goed moeten meten of eea wel zo ideaal is als jij bedacht had vanachter je buro.
En als alles lekker draait moet je natuurlijk af en toe de zaken weer evalueren en eventueel de indices weer anders leggen of opnieuw rebuilden.

/disclaimer:
Een DBA is een specialist op dit gebied en een compleet vakgebied kan je natuurlijk niet in een aantal zinnetjes samenvatten.

[HenkS]

ok dan, ik heb weer een vraag:

stel je hebt een tabel:
user_id, naam, wachtwoord, adres, postcode, etc...
naam is niet unique, maar de combi van naam en wachtwoord wel

dan zou volgens jullie, de index zo moeten:
primary key om id, maar dan nonclustered
en dan nog een index op naam+wachtwoord en dit dan wel clustered.

Als je dan met inloggen werkt, vind hij de naam+wachtwoord heel snel omdat hij hierop gesorteerd is (geclustered)

dit lijkt me logisch, MAAR stel je hebt nog een tabel waarin later bv boeken staan (bibliotheek ofzo) en dan heb je nog een koppeltabel, omdat een boek door meer mensen gelezen kan worden, en een mens meer boeken kan lezen.....

dan krijg je in die koppeltabel zoiets:
lees_id, user_id, boek_id

dan wil je een overzicht genereren wat heel vaak wordt opgevraagd, dus dat je wilt opvragen wie wel boek allemaal heeft gelezen, dan krijg je een query waarin je id's gaat vergelijken, maar je hebt nu de clustered key niet op dat id zitten, maar op de naam....

wat is in dit geval dan de beste oplossing, want door deze keuze gaat zoals ik het zie, het inloggen heel snel, maar het genereren van lijsten niet meer.....

edit:

voordat er gemekkerd gaat worden: ja ik weet dat je een boek maar aan 1 iemand tegenlijk kan uitlenen, maar gaat over wie het allemaal gelezen heeft

[dusty]

Hoe vaak zoek je gegeven 1 op...
En hoe vaak zoek je gegeven 2 op..

Relatief zit er HEEL veel verschil in. En daardoor wordt het al duidelijk welke beter is

[CubicQ]

Is het eigenlijk niet mogelijk om een soort RAID-1 te maken met een database, alles 2x opslaan (alleen dan met twee verschillende clustered indices), dan heb je welliswaar een enorme (factor 2 ofzo ) penalty bij insert/update/delete, maar ik kan me zo voorstellen dat dat bij select queries soms vrij veel uit kan maken.

[Annie]

Op woensdag 28 november 2001 20:22 schreef CubicQ het volgende:
Is het eigenlijk niet mogelijk om een soort RAID-1 te maken met een database, alles 2x opslaan (alleen dan met twee verschillende clustered indices), dan heb je welliswaar een enorme (factor 2 ofzo ) penalty bij insert/update/delete, maar ik kan me zo voorstellen dat dat bij select queries soms vrij veel uit kan maken.

Natuurlijk kan dat. Als jij dat wil, moet je dat gewoon maken.
- zelf bouwen in je applicatie,
- met triggers alles dubbel uitvoeren,
- extracten uit de database overzetten naar een Data Warehouse,
- of ...

En het komt in de praktijk ook voor. Stel je maar een bank voor waar in een database continue transacties (goh, hoe toepasselijk ) worden gedaan, daar is het dus van belang dat de inserts/updates/deletes zo snel mogelijk gaan.
Nu willen ze ook wekelijks mooie rapporten uitdraaien van al die miljoenen transacties voor business analyses. Op dat moment wil je dus niet een database waar een query uren aan het stampen is om er wat data uit te krijgen.

Meer info? Zoek maar eens op OLTP of OLAP.