MySQL Forums
Forum List  »  Newbie

What is the best way indexing foreign key from multiple table?
Posted by: Gilbert Consellado
Date: February 10, 2017 05:42AM

What is the better way of indexing the foreign key?

Create Table table3(
t3_id int not null auto_increment,
t1_id int not null,
t2_id int not null,
primary key (t3_id),
index IX_index (t1_id, t2_id), // this is my concern
constraint FK_t1 foreign key (t1_id)
reference table1(t1_id),
constraint FK_t2 foreign key (t2_id)
reference table2(t2_id));

or

Create Table table3(
t3_id int not null auto_increment,
t1_id int not null,
t2_id int not null,
primary key (t3_id),
index IX_t1 (t1_id), //this is my concern
index IX_t2 (t2_id), //and this
constraint FK_t1 foreign key (t1_id)
reference table1(t1_id),
constraint FK_t2 foreign key (t2_id)
reference table2(t2_id));

This is for innodb tables, I dont have a broad understanding how the btree works. But as far as I know, the first table will save the indexes on single area while the second is not, (correct me if I am wrong). And if it is what is the pros and cons if I will put it on a single index or multiple index?

Thanks.

Options: ReplyQuote


Subject
Written By
Posted
What is the best way indexing foreign key from multiple table?
February 10, 2017 05:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.