Join Table Index
So here's another design question;
Should there be a defined index for a join table, if that index would have to be the entirity of the join table?
I have two axes of a matrix as the keys of the join table. This forms a many-to-many relationship between the two.
fk1,fk2 and data are not unique. (fk1,fk2) is not unique. Neither is (fk1,data) or (fk2,data). So the only index possible is (fk1,fk2,data), which is unique, but seems rather moot when the order of the data in the result query is not a factor and would result in the index being the entire table.
Am I missing anything here, oh great gurus of the databases?
You should add an auto_increment PK in this case. It'll make your individual records indexable.
id int unsigned not null auto_increment primary_key,
fk1 int unsigned not null references table1(id),
fk2 int unsigned not null references table2(id),
data varchar(50) null