What is the most convenient indexing strategy for two foreign key constraints?

Question

Which of the two following options is more convenient for indexing the relevant foreign keys?

Option A:

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));

Option B:

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));

I'm using InnoDB. I don't have a broad understanding how a btree index works but, as far as I know, the first table will save the indexes on a single area while the second will not (correct me if I am wrong).

What are the pros and cons of defining a single- or a multi-column index?

Answer 1

The create table script 2 is correct.

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

Answer 2

If this is a simply many:many mapping table, I recommend throwing out most of the columns. The AUTO_INCREMENT is a waste, and even slows down processing. Instead you need two 2-column indexes, one of which is the PRIMARY KEY, the other is a plain index. In my opinion, the FKs don't add enough to warrant the extra overhead.

More tips here.