Stay Curious!

Why does InnoDB create two indexes per foreign key?

If you’ve ever created foreign keys on an InnoDB table, you’ll see it automatically creates indexes, if none exists, on the referenced columns in the parent table, and also in the foreign key columns in the child table. This article explains why both are needed.

Why index the parent table?

When a row is inserted or updated in the child table, the parent table must be searched for a row whose referenced values match the values in the foreign key columns. To make this efficient, it’s necessary to have a usable index on those columns in the parent table.

By “usable” I mean an index where the columns are either a leftmost prefix or a full cover of the foreign key columns.

Why index the child table?

Foreign key checking goes both directions. When a row in the parent table is updated or deleted, any rows in the child table that depend on it must be checked to make sure they’re not invalidated (or, in the case of a CASCADE, to find them to take the CASCADE action upon them). Again, the only efficient way to do this is to use an index.

You can see that the checking always uses indexes by examining the output of SHOW INNODB STATUS and looking at the LATEST FOREIGN KEY ERROR section. If there’s an error there, you will always see information about two indexes (though it may not always be obvious, since the code that creates this output has to handle a variety of different errors).

By the way, I seem to remember seeing some comments in the InnoDB source code that say indexes are not created automatically, but I can’t find them now. In any case, if this was true once, it is no longer true.

A useful tool

The <a href="http://www.xaprb.com/blog/2006/07/02/innotop-mysql-innodb-monitor/">innotop</a> tool can format and display the LATEST FOREIGN KEY ERROR output for easy reading. I check our systems for foreign key violations regularly. Here is a sample of innotop’s output for a violation I deliberately manufactured for this article:

Reason: Foreign key constraint fails for table `test/table_2`:

User xaprb from 192.168.0.225, thread 64548 was executing:

insert into table_2(table_1) values(5)

Time            2006-07-27 16:32:10
Child DB        test
Child Table     table_2
Child Index     table_1
Parent DB       test
Parent Table    table_1
Parent Column   table_1
Parent Index    PRIMARY
Constraint      table_2_ibfk_1

Posted on Thu, Jul 27, 2006. Approximately 400 Words.

Databases