1

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?

0

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.

  • hmm I am using mysql 5.7 right now and both script are valid, other than the compatibility on foreign key creation on the older version. What are the other difference between them? – zer09 Feb 11 '17 at 2:17
0

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.

  • It is just a concept table, for me to grasp a better understanding how indexing on foreign key. If it is a many:many mapping table I think it is better to make both foreign keys as a primary key. Because if i will just make one primary key it could be possible to have a duplicate primary key error. Am I right? – zer09 Feb 11 '17 at 1:55
  • ohh are you the author of the ricksrots I already read all of your Rules of Thumbs there. I also posted this question on mysql forum from your link at the bottom. BTW there is an error on your page saying "Can't open file: '....Log.MYI' (errno: 145)...." at the very top. – zer09 Feb 11 '17 at 2:07
  • @zer09 - Thanks! That was a bug that I failed to notice when switching systems. – Rick James Feb 11 '17 at 17:45
  • @zer09 - that took longer than I expected to fix. – Rick James Feb 11 '17 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.