what is the best way indexing foreign key from multiple table?
-
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.
-
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.
They are different ways and which is 'best' depends on your needs. The first creates a composite index, and puts two fields in that list. The second one creates two separate indexes for those fields. Are you going to be locating a lot of records by searching for t1_id, and when found, looking in that set for t2_id? Or are you going to be locating a lot of records by searching for either field?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
They are different ways and which is 'best' depends on your needs. The first creates a composite index, and puts two fields in that list. The second one creates two separate indexes for those fields. Are you going to be locating a lot of records by searching for t1_id, and when found, looking in that set for t2_id? Or are you going to be locating a lot of records by searching for either field?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
For now its just a concept table, for me to understand when to use the first or the second one. can you explain to me when should I use the composite index or the other one? please. Thanks base on your reply your saying that, if I use the t1_id and t2_id to satisfy a where clause frequently, I should use the composite key other wise I should use the separate index for every field. Am I right? How about joining the three tables? Thanks.
-
For now its just a concept table, for me to understand when to use the first or the second one. can you explain to me when should I use the composite index or the other one? please. Thanks base on your reply your saying that, if I use the t1_id and t2_id to satisfy a where clause frequently, I should use the composite key other wise I should use the separate index for every field. Am I right? How about joining the three tables? Thanks.
Think of it like a phone book: the data has a composite key of
(Surname, Forename). If you're always going to know the surname of the records you're looking for, then the index works. But if you ever want to search for a particular forename, without knowing the surname, then the index is no help. You have to resort to reading through the entire book to find the matching records. If you wanted to do that regularly, you'd create a separate index sorted by forename. That would make it easier to find records with a particular forename. But it would also mean you'd have more work to do when you insert, update, or delete records. Which option you choose will depend on your data, how you're going to be querying it, and how often you're going to be changing it. For example, if you're only going to be usingt1as a lookup, and never searching for records with a particulart1_id, then there's probably not much point having an index on it. It basically comes down to a combination of instinct and performance measurement. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Think of it like a phone book: the data has a composite key of
(Surname, Forename). If you're always going to know the surname of the records you're looking for, then the index works. But if you ever want to search for a particular forename, without knowing the surname, then the index is no help. You have to resort to reading through the entire book to find the matching records. If you wanted to do that regularly, you'd create a separate index sorted by forename. That would make it easier to find records with a particular forename. But it would also mean you'd have more work to do when you insert, update, or delete records. Which option you choose will depend on your data, how you're going to be querying it, and how often you're going to be changing it. For example, if you're only going to be usingt1as a lookup, and never searching for records with a particulart1_id, then there's probably not much point having an index on it. It basically comes down to a combination of instinct and performance measurement. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks appreciated :) .
