[mysql] volgorde van indexen

[RSD]

Ik heb een tabel vol met links en in deze tabel staat een categorieid en een paginaid.

Nu vraag ik me 2 zaken af:

1 - Als ik de links van een bepaalde categorie in een bepaalde pagina op haal, hoe kan mijn select er dan het beste uit zien:

SELECT * FROM links WHERE categorieid=1 AND paginaid=2

of

SELECT * FROM links WHERE categorieid=1

Zelf denk ik de eerste? Waarom weet ik niet.

2 - Als ik een index plaats kan ik een multupe index plaatsen dus iets als:

ADD INDEX(paginaid,categorieid)

of ik kan twee indexen plaatsen

ADD INDEX(paginaid)
ADD INDEX(categorieid)

Wat is de beste keuze en als ik voor ADD INDEX(paginaid,categorieid) kies, welke volgorde moet ik dan nemen als we aannemen dat een pagina meerdere categorieen kan bevatten.

Dus ADD INDEX(paginaid,categorieid) of ADD INDEX(categorieid,paginaid) en welke WHERE hoort hier dan bij?

WHERE categorieid=1 AND paginaid=2 of WHERE paginaid=1 AND categorieid=2

Ik weet dat er verschil in zit, maar uit de documentatie staat niet echt wat het beste is.

Als ik op mijn verstand afga, denk ik ADD INDEX(paginaid,categorieid) met WHERE paginaid=1 AND categorieid=2

Omdat je eerst in een kleine index zoekt die snel is en dan de grote, maar mijn gevoel zegt dat het omgekeerde ook heel goed kan?

In de documentatie stond een zinnetje en die kon ik niet goed interpreteren, vandaar de verwarring.

[_js_]

MySQL kan niet twee losse indexen combineren in een query, zal dus altijd maar één index gebruiken.

De volgorde van de voorwaarden in WHERE is niet belangrijk. MySQL zoekt zelf de beste index en volgorde op bij de voorwaarden die er zijn.

In een index wordt eerst op de eerste kolom gesorteerd, dan op de tweede, etc.

Een index over kolom A,B kan dus gebruikt worden voor WHERE A=1 AND B=2 of voor WHERE A=1.
WHERE B=2 kan niet gebruik maken van deze index.
WHERE B=2 AND A=1 is wat MySQL betreft hetzelfde als WHERE A=1 AND B=2.

[Verwijderd]

_js_ schreef op zondag 27 mei 2007 @ 00:09:
MySQL kan niet twee losse indexen combineren in een query, zal dus altijd maar één index gebruiken.

Als dat waar is zakt MySQL nog een stukje verder in mijn aanzien. Ieder volwassen RDBMS hoort overweg te kunnen met meerdere indexen binnen 1 query.

[Confusion]

Een index over meerdere kolommen zin als je soms de eerste kolom in de WHERE clause opneemt en soms de eerste en de tweede (of de eerste en de tweede en derde, etc.). Deze documentatie lijkt mij duidelijk; welk zinnetje vind je onduidelijk?

Indexen over meerdere kolommen hebben daarnaast zin als je de data in de tweede (etc.) kolom op wilt halen op basis van de eerste kolom. De rij hoeft dan namelijk niet meer gelezen te worden, omdat de waarde met het lezen van de index al bekend is. Zie MySQL docs

Verwijderd schreef op zondag 27 mei 2007 @ 07:12:
[...]
Als dat waar is zakt MySQL nog een stukje verder in mijn aanzien. Ieder volwassen RDBMS hoort overweg te kunnen met meerdere indexen binnen 1 query.

offtopic:
Oh please. Zo kan ik het ook: iedere volwassen RDBMS hoort ook een 'delete column' mogelijkheid te hebben. Zoek daar maar eens naar in DB2 of Oracle.

Google gebruikt MySQL. 'nuff said. Het is een fatsoenlijk product voor vele toepassingen, waar veel mensen helaas nogal wat ongegronde vooroordelen over hebben.

Voorstanders hebben overigens hetzelfde probleem. In dit dit artikel zijn de twee gerefereerde artikelen allebei even slecht.

[ Voor 123% gewijzigd door Confusion op 27-05-2007 10:30 ]

[Janoz]

@confusion: MySQL wordt veel gebruikt, maar om het nu een fatsoenlijk product te noemen. Er is weinig ongegronds aan aan de vele op en aanmerkingen die op MySQL gemaakt kunnen worden. Het niet kunnen gebruiken van meer dan 1 index vind ik toch wel een redelijk fundamentele misser. Het is 1 van de vele dingen die als motivatie 'dat willen of dat kunnen we niet implementeren'. Het niet hebben van een delete column is tenminste nog verdedigbaar op het gebied van data integriteit en is ook keurig omheen te werken door alles naar een nieuwe tabel over te zetten.

[Voutloos]

Overigens kunnen nieuwe versies van mysql wel index union en index intersect doen.
Maar voordat dat uberhaupt boeiend is moet je eerst weten hoe indexen (en samengestelde indexen) werken, hoe je explain moet interpreteren, etc etc. Oftewel de index / performance basiskennis onder de knie hebben. Goede performance bereik je niet door lukraak op elke kolom een index te slingeren.

[Confusion]

Janoz schreef op zondag 27 mei 2007 @ 12:00:
@confusion: MySQL wordt veel gebruikt, maar om het nu een fatsoenlijk product te noemen.

Alleen Oracle, MS-SQL en DB2 zijn fatsoenlijke producten en alle andere producten zijn per definitie niet fatsoenlijk, omdat ze minder features hebben?

Er is weinig ongegronds aan aan de vele op en aanmerkingen die op MySQL gemaakt kunnen worden.

Net zoals er weinig ongegrond is aan de vele op en aanmerkingen die op <insert product naar keuze> gemaakt kunnen worden. Wat ongegrond is, is het product op basis daarvan afserveren als 'onfatsoenlijk'.

Het niet kunnen gebruiken van meer dan 1 index vind ik toch wel een redelijk fundamentele misser. Het is 1 van de vele dingen die als motivatie 'dat willen of dat kunnen we niet implementeren'.

Wat is er mis met 'dat hebben we nog niet geimplementeerd'? MySQL Inc. is nu eenmaal niet almachtig en heeft tijd nodig om dingen te presteren.

Trouwens, 1 index per tabel per query, met de mogelijkheid een index over meerdere kolommen te gebruiken, dekt voor vele applicaties alle gewenste gebruiksmogelijkheden.

Het niet hebben van een delete column is tenminste nog verdedigbaar op het gebied van data integriteit en is ook keurig omheen te werken door alles naar een nieuwe tabel over te zetten.

Een delete column methode is een macro, die je veel nodig hebt als je een database refactored en die voor Oracle of IBM gemakkelijk te implementeren moet zijn. MySQL weigert het ook in vele gevallen, omdat er nu eenmaal nogal wat gevallen zijn waarin het niet kan als de data integriteit bewaard moet blijven.

De MySQL command line interface is bijvoorbeeld veruit superieur aan die van DB2, waarin het verwijderen van een column een enorme opgave is.

[RSD]

Dat de volgorde van WHERE niet uitmaakt is me duidelijk, maar nu het volgende:

ADD INDEX(paginaid,categorieid) of ADD INDEX(categorieid,paginaid)

Waarin ik alle links van een bepaalde pagina uit een bepaalde categorie van die pagina wil ophalen.

Of zit hier geen verschil in? Dat lijkt me toch wel. MySQL moet toch weten in welke index hij eerst moet zoeken? De kardinaliteit van de indexen verschilt namelijk nogal of ik het eerste of het tweede doe? Wat is het beste?

[Voutloos]

Er zit een verschil in:

_js_ schreef op zondag 27 mei 2007 @ 00:09:
Een index over kolom A,B kan dus gebruikt worden voor WHERE A=1 AND B=2 of voor WHERE A=1.
WHERE B=2 kan niet gebruik maken van deze index.

Welke het beste is hangt dus af van kardinaliteit.

[Confusion]

RSD schreef op dinsdag 29 mei 2007 @ 13:23:
Dat de volgorde van WHERE niet uitmaakt is me duidelijk, maar nu het volgende:

ADD INDEX(paginaid,categorieid) of ADD INDEX(categorieid,paginaid)

Waarin ik alle links van een bepaalde pagina uit een bepaalde categorie van die pagina wil ophalen.

Of zit hier geen verschil in? Dat lijkt me toch wel. MySQL moet toch weten in welke index hij eerst moet zoeken? De kardinaliteit van de indexen verschilt namelijk nogal of ik het eerste of het tweede doe? Wat is het beste?

Er zit voor queries met een WHERE over beide kolommen geen verschil in: welke subindex van de samengestelde index als eerste wordt gebruikt, wordt door de query optimizer bepaald en is niet afhankelijk van de volgorde.

De volgorde is alleen relevant als je bijvoorbeeld ook queries doet die alleen een WHERE clause over paginaid bevatten. In dat geval kan de eerste index wel gebruikt worden, maar de tweede niet.

[Voutloos]

Confusion schreef op dinsdag 29 mei 2007 @ 13:45:
Er zit voor queries met een WHERE over beide kolommen geen verschil in: welke subindex van de samengestelde index als eerste wordt gebruikt, wordt door de query optimizer bepaald en is niet afhankelijk van de volgorde.

En dat is ook een manier om te testen: maak ze allebei en kijk welke van de indexen steeds gebruikt wordt. Verwijder de andere index indien niet nuttig genoeg.

[killercow]

Verwijderd schreef op zondag 27 mei 2007 @ 07:12:
[...]
Als dat waar is zakt MySQL nog een stukje verder in mijn aanzien. Ieder volwassen RDBMS hoort overweg te kunnen met meerdere indexen binnen 1 query.

Naar mijn idee voldoet MySQL prima aan 95% van de behoeften van haar gebruikers.
P.S. iedere volwassen Nederlander hoort te weten dat "meerdere" een aanduiding is voor je overste, je "manager". Verschillende, of "meer dan een" is wel correct.

Punt is dat MySQL prima voldoet, ook zonder allerlei ingewikkelde features, de features die te veel tijd kosten om te implementeren en te weinig opleveren worden verhoudingsgewijs later geïmplementeerd wat mij niet meer dan logisch lijkt.

[bigbeng]

offtopic:
meer·de·re2 (onb.vnw.)
1 verscheidene, meer dan één => verschillende
Bron: http://www.vandale.nl/opzoeken/woordenboek/?zoekwoord=meerdere

edit:

hehe ik was net op tijd

[ Voor 12% gewijzigd door bigbeng op 30-05-2007 10:12 ]

[jvdmeer]

offtopic:
killercow schreef op woensdag 30 mei 2007 @ 09:47:
P.S. iedere volwassen Nederlander hoort te weten dat "meerdere" een aanduiding is voor je overste, je "manager". Verschillende, of "meer dan een" is wel correct.

Niet waar, zie Van Dale, 2e uitleg.

offtopic:
Ben weer te langzaam

[ Voor 4% gewijzigd door jvdmeer op 30-05-2007 10:10 ]

[rickmans]

De volgorde van de where maakt overigens wel iets uit. SQL wordt door MySQL gelezen van rechts naar links. Dus om hier optimaal van te profiteren zou je de elementen die de meeste invloed hebben op je resultset zo rechts mogelijk neer moeten zetten.

[Voutloos]

Heb je daar een bron van? De optimizer parset hoe dan ook alles.

[RSD]

Als ik een query doe en de links wil ophalen, doe ik altijd een WHERE categorieid=x AND pageid=y

Is het nu verstandig om een ADD INDEX(categorieid,pageid) te doen of kan ik beter ADD INDEX(categorieid) doen?

Ik wordt steeds verwarder

[Voutloos]

Test het. Die samengestelde index is ideaal voor deze query. Deze index is dus ook bruikbaar mocht je een keertje een query hebben met alleen categorieid erin.

[RSD]

En als ik hem omdraai, dus ADD INDEX(pageid,categorieid) dan is de WHERE categorieid=x AND pageid=y na genoeg hetzelfde, alleen is het verschil dan dat hij niet optimaal is voor WHERE categorieid=x, maar wel voor pageid=y .. als dat zo is dan vat ik hem ;-)

[Voutloos]

Je vat hem.