[MySQL]multiple kolom index op kolommen met een foreign key

[freakin_ruben]

Ik heb een tabel 'permissions' die aangeeft welke groep voor welke module/submodule welke rechten heeft.
Deze ziet er als volgt uit:

code:

+-----------------------+
|     PERMISSIONS       |
+--------+--------------+
| pid    | int(8)       | <- primary key
| mid    | mediumint(5) |
| smid   | mediumint(5) |
| gid    | mediumint(5) |
| uid    | int(11)      |
| rights | mediumint(5) |
+--------+--------------+

- mid is een foreign key naar mid van de module table
- smid is een foreign key naar de smid van submodule table
- gid is een foreign key naar het gid van de group table
- uid zal zelden gebruikt worden maar is een foreign key naar de user table

Nou moet je natuurlijk op alle 4 de kollomen een index maken maar nou zullen de rechten bijna altijd geselecteerd worden op de combinatie van:
- het gid van de gebruiker
- de module
- (eventueel smid van de submodule).

Dus leek het mij handiger om een multiple kolom index te maken aangezien ik alle rechten voor bijv alleen een group_id alleen zal opvragen bij het wijzigen (zal niet vaak gebeuren) ervan. Voor de rest zal het altijd in combinatie met een mid gebeuren.

Alleen kan MySQL schijnbaar geen foreign keys creeeren als de betreffende kolom geen compleet eigen index heeft ipv een gedeelde. Is hier een andere oplossing voor of moet ik genoegen nemen met 4 losse indexes.

Ik krijg dus geen specifieke foutmelding, alleen:
"#1005 - Can't create table '.\database\permissions.frm' (errno: 150)"

Dit is de query die ik wil gebruiken (voor het geval het wel kan maar ik iets fout doe):

SQL:

create table permissions
(
   pid                            int(8)                         not null auto_increment,
   mid                            mediumint(5)                   not null,
   smid                           mediumint(5)                   not null,
   gid                            mediumint(5)                   not null,
   uid                            int(11),     
   rights                         mediumint(5)                   not null,
   primary key (pid),
   index groupmod_ind (gid, mid, smid),
   index uid_ind (uid),
   constraint `is allowed to (group)` foreign key (gid)
         references groups (gid)
         on delete cascade on update cascade,
   
   constraint `is allowed to (user)` foreign key (uid)
         references users (uid)
         on delete cascade on update cascade,
   
   constraint `in2` foreign key (smid)
         references submodules (smid)
         on delete cascade on update cascade,
   
   constraint `in1` foreign key (mid)
         references modules (mid)
         on delete cascade on update cascade
)
type = innodb
auto_increment = 1;

Verder vroeg ik me af of het mischien slimmer is om (indien het bovenstaande dus wel mogelijk is) de kolom pid weg te laten en in plaats daarvan de primary key over de kolommen uid, mid, smid te zetten. De combinatie van die 3 moet immers ook altijd uniek zijn (we willen geen dubbele rechten).
Is dat in dit geval even snel met opzoeken of is bijv bij het wijzigen het opgeven van pid sneller (als het dus al mogelijk is)...

[justmental]

Die losse indexes heeft ie nodig vanwege deletes op de master, dan moet gecontroleerd worden of er nog childs zijn bij de master.
Jouw gecombineerde index is hiervoor niet bruikbaar omdat de kolommen voor mid en smid op de 2e en 3e plaats staan en dus niet direct te gebruiken zijn voor zo'n controle.