Re: What is the best way indexing foreign key from multiple table?

[Phillip Ward]

Think about how these indexes might be used.

If you were reading through Table3 and "looking up" rows in Table1, you don't need any index on t1_id (in Table3); you've already got the value in the "current" record. Same for reading Table3 and lookikng up in Table2 (by t2_id).

However, if you have the primary key from Table1 and want to find rows in Table3 based [only] on that, then you need an index on t1_id (or a compound index that has t1_id as its first column).
If you have the primary key from Table2 and want to find rows in Table3 based [only] on that, then you need an index on t2_id (or an index that starts with t2_id).

I would recommend starting with a separate index on each field.

It might be that you later add another index that includes both fields in the order they are most often used - but that's only after [query] performance is reviewed and found to be poor.

Regards, Phill W.