[MySQL] Index op kolommen uit verschillende tabellen?

[JeRa]

Ik zit me af te vragen waarom er in MySQL geen multiple column indices over verschillende tabellen bestaan. Voorbeeldje:

Ik heb een user tabel met daar in een UserID en een Username, en een koppeltabel met daarin onder andere ItemID en UserID (met PRIMARY KEY op ItemID, UserID). Stel, ik wil sorteren op ItemID en vervolgens op UserID. Dit kan prima met de PRIMARY index. Als ik echter eerst wil sorteren op ItemID en vervolgens op Username (na een JOIN natuurlijk), dan moet MySQL gaan filesorten.

Dat zou opgelost kunnen worden door een index aan te maken op (koppeltabel.ItemID, usertabel.Username). Is er een bepaalde reden waarom ik zo'n index niet aan kan maken, of bestaan ze wellicht wél?

Borizz schreef op donderdag 21 september 2006 @ 21:58:
Zover ik weet kent MySQL wel multiple column indices, maar dit werkt natuurlijk niet over meerdere tabellen .

Sorry, drie woordjes vergeten.

edit: Misschien dat dit in MySQL 5 wel mogelijk is op een view? Maar dat zou ik zo niet weten.

Lijkt me erg sterk dat dit kan.

[ Voor 24% gewijzigd door JeRa op 21-09-2006 22:02 ]

[Borizz]

Zover ik weet kent MySQL wel multiple column indices, maar dit werkt natuurlijk niet over meerdere tabellen .

edit: Misschien dat dit in MySQL 5 wel mogelijk is op een view? Maar dat zou ik zo niet weten.
edit2: is dus niet mogelijk (even opgezocht in de handleiding).
En ook multiple column indices is niet mogelijk (zie de CREATE INDEX syntax in de handleiding).
Je zou de resultaten in een (tijdelijke) tabel kunnen inserten (als er vaak dezelfde data opgevraagd wordt) en daar de juiste indices op kunnen definieren.

[ Voor 77% gewijzigd door Borizz op 21-09-2006 22:11 ]

[JeRa]

Borizz schreef op donderdag 21 september 2006 @ 21:58:
Je zou de resultaten in een (tijdelijke) tabel kunnen inserten (als er vaak dezelfde data opgevraagd wordt) en daar de juiste indices op kunnen definieren.

De data is erg dynamisch en telt ongeveer evenveel reads als writes, dus performance-wise niet echt geweldig om elke keer een complete nieuwe tabel weg te schrijven

[Verwijderd]

JeRa schreef op donderdag 21 september 2006 @ 21:54:
Ik heb een user tabel met daar in een UserID en een Username, en een koppeltabel met daarin onder andere ItemID en UserID (met PRIMARY KEY op ItemID, UserID). Stel, ik wil sorteren op ItemID en vervolgens op UserID. Dit kan prima met de PRIMARY index. Als ik echter eerst wil sorteren op ItemID en vervolgens op Username (na een JOIN natuurlijk), dan moet MySQL gaan filesorten.

Je loopt hier tegen een beperking van koppeltabellen en synthetische primary keys aan (alle PK's zijn auto increment / identity /whatever velden die inhoudelijk niks zeggen).

Wanneer in je user tabel gewoon de Username de PK was, en niet die UserID, was jouw probleem een non-issue.

Wanneer je niet naar natural keys (Username i.p.v. UserID) wilt of kunt, zou ik in je koppeltabel lekker die Username als extra kolom opnemen. Hardstikke redundant, maar 't sorteert en indexeert wel zo prettig.

[P_de_B]

Of MySQL gaat ook het revolutionaire concept van gewoon meerdere indexen per tabel gebruiken invoeren.

Er zijn ook databases die tegenwoordig indexed views kunnen gebruiken, wellicht dat dat ook in een toekomstige versie van MySQL komt. Voorlopig heb je denk ik te maken met een beperking in MySQL.

[frickY]

Of je stapt over op Posgresql, al ze zo een veeleisende gebruiker bent

Ik ben zelf een gelukkige MySQL gebruiker zolang het alles kan wat ik nodig heb. Mochten mijn wensen gecompliceerder worden lijkt me een overstap naar een andere DB echter logisch?

[JeRa]

frickY schreef op vrijdag 22 september 2006 @ 08:51:
Of je stapt over op Posgresql, al ze zo een veeleisende gebruiker bent

Ik noem het nou niet bepaald veeleisend het is eerder dat ik met MySQL tegen problemen aanloop tijdens het normaliseren van mijn database. Redundantie biedt een oplossing maar het is alles behalve een 'mooie' oplossing

[jochemd]

JeRa schreef op vrijdag 22 september 2006 @ 09:03:

Ik noem het nou niet bepaald veeleisend het is eerder dat ik met MySQL tegen problemen aanloop tijdens het normaliseren van mijn database.

Jouw probleem is helemaal niet dat er geen indexen op kolommen uit verschillende tabellen bestaan. Jouw probleem is dat je query te langzaam is. Indexen op kolommen uit verschillende tabellen zou eventueel in theorie een oplossing voor dat probleem kunnen zijn. (Maar dan nog zijn er redenen waarom je die specifieke oplossing niet zou willen, denk bijvoorbeeld aan gebruikers met permissies op de ene tabel maar niet op de andere.)

Als we dit nou eens gewoon gaan benderen als elk ander performance probleem, wat zijn dan:
- je tabeldefinities
- je configuraties
- je explain output
etc.

[JeRa]

jochemd schreef op vrijdag 22 september 2006 @ 13:57:
[...]

Jouw probleem is helemaal niet dat er geen indexen op kolommen uit verschillende tabellen bestaan. Jouw probleem is dat je query te langzaam is. Indexen op kolommen uit verschillende tabellen zou eventueel in theorie een oplossing voor dat probleem kunnen zijn. (Maar dan nog zijn er redenen waarom je die specifieke oplossing niet zou willen, denk bijvoorbeeld aan gebruikers met permissies op de ene tabel maar niet op de andere.)

Als we dit nou eens gewoon gaan benderen als elk ander performance probleem, wat zijn dan:
- je tabeldefinities
- je configuraties
- je explain output
etc.

Je hebt het helemaal verkeerd begrepen. Indices bieden performancewise iets wat je beter in eerste instantie niet kunt gaan oplossen door betere hardware e.d.; ga even van de theoretische situatie uit dat er twee tabellen met vele miljoenen rows zijn en dat indices zeker benodigd zijn. In die situatie voldoen de huidige mogelijkheden tot indexeren niet en is in mijn geval een cross table index benodigd.

Het op een performanceprobleem gooien is veel te makkelijk aangezien ik nu juist een feature voorstel die ervoor zorgt met eenzelfde hardwareconfiguratie betere performance voor mijn queries kan leveren in een zo ver mogelijk genormaliseerde database

[jochemd]

JeRa schreef op vrijdag 22 september 2006 @ 15:00:
[...]

Je hebt het helemaal verkeerd begrepen. Indices bieden performancewise iets wat je beter in eerste instantie niet kunt gaan oplossen door betere hardware e.d.; ga even van de theoretische situatie uit dat er twee tabellen met vele miljoenen rows zijn en dat indices zeker benodigd zijn. In die situatie voldoen de huidige mogelijkheden tot indexeren niet en is in mijn geval een cross table index benodigd.

Nee, daar ga ik niet zonder meer van uit. Dat er schema modificaties nodig zijn zal je moeten onderbouwen door de andere opties (zoals een betere configuratie) uit te sluiten met bewijzen. En als je die stap gezet hebt zit er nog een extra stap tussen waarin je duidelijk maakt waarom andere opties, zoals bijvoorbeeld een gematerialiseerde en geindexeerde view op de join van de tabellen waar het je om gaat, ook niet voldoen.

Je begint nog steeds met een oplossing in plaats van een probleem.

[JeRa]

jochemd schreef op vrijdag 22 september 2006 @ 19:26:
[...]

Nee, daar ga ik niet zonder meer van uit. Dat er schema modificaties nodig zijn zal je moeten onderbouwen door de andere opties (zoals een betere configuratie) uit te sluiten met bewijzen.

Ehm, ik wil het wel gaan bewijzen hoor, maar ik hoop dat iedereen die met een DBMS heeft gewerkt wel snapt dat indices ervoor zorgen dat je met dezelfde configuratie meer performance kunt krijgen. En dat is wat ik nu wil uitzoeken, en niet de mogelijkheid tot uitbreiden van de hardwareconfiguratie want dat is imho een zwakke(re) stap

En als je die stap gezet hebt zit er nog een extra stap tussen waarin je duidelijk maakt waarom andere opties, zoals bijvoorbeeld een gematerialiseerde en geindexeerde view op de join van de tabellen waar het je om gaat, ook niet voldoen.

Een geïndexeerde join op een view voldoet best, dat is ongeveer hetzelfde wat ik hierboven beschreef met het verschil dat die indices ook partieel kunnen zijn. Maar aangezien dit niet in MySQL zit vroeg ik me af of ze geen simpele tussenweg bedacht hadden

[jochemd]

JeRa schreef op zaterdag 23 september 2006 @ 12:53:
[...]

Ehm, ik wil het wel gaan bewijzen hoor, maar ik hoop dat iedereen die met een DBMS heeft gewerkt wel snapt dat indices ervoor zorgen dat je met dezelfde configuratie meer performance kunt krijgen.

Dat kan ja, dat hoeft niet. En soms kan je met een verandering van de configuratie ook een betere performance krijgen.

En dat is wat ik nu wil uitzoeken, en niet de mogelijkheid tot uitbreiden van de hardwareconfiguratie want dat is imho een zwakke(re) stap

Ik heb het over configuratie, niet over hardwareconfiguratie.

Een geïndexeerde join op een view voldoet best, dat is ongeveer hetzelfde wat ik hierboven beschreef met het verschil dat die indices ook partieel kunnen zijn. Maar aangezien dit niet in MySQL zit vroeg ik me af of ze geen simpele tussenweg bedacht hadden

Voor geavanceerde indexen is MySQL gewoon niet het juiste product. In bijvoorbeeld PostgreSQL zou je dit al kunnen doen door een immutable functie te schrijven die op basis van een userid een username teruggeeft. Vervolgens maak je een functionele index over (itemid, functie(userid)) dan heb je een exact gesorteerde index. Wat overigens weer een voorbeeld is van een oplossing voor je probleem die anders is dan de oplossing waar je mee begon. En het lijkt me toch iets waarschijnlijker dat MySQL ooit functionele indexen gaat ondersteunen dan cross-table indexen.