Primary and Foreign Key Indices

Question

I have a database created using the GUI tool, and I noticed that it seems to be an inconsistent use of the KEY definitions (aka INDEX ):

 CREATE TABLE `foo_bar` ( `foo_id` int(10) unsigned NOT NULL, `bar_id` int(10) unsigned NOT NULL, PRIMARY KEY (`foo_id`, `bar_id`), KEY `foo_bar_fk2` (`bar_id`), -- <== ??? CONSTRAINT `foo_bar_fk1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`foo_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `foo_bar_fk2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`bar_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci COMMENT='Links between Foo and Bar'; 

I have the following questions about indexes :

• Do I need to explicitly define indexes for primary and foreign keys?
• If this is not the case, are you actually getting two indexes (and less performance)?
• Differences in InnoDB and MyISAM (foreign keys)?

Answer 1

I did some experiments on what you told me, and I thought that I would share it as with the answer.

First I create some test tables:

 CREATE TABLE foo ( foo_id int(10) unsigned NOT NULL, PRIMARY KEY (foo_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; CREATE TABLE bar ( bar_id int(10) unsigned NOT NULL, PRIMARY KEY (bar_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; CREATE TABLE foo_bar ( foo_id int(10) unsigned NOT NULL, bar_id int(10) unsigned NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci; 

No indexes yet:

 mysql> SHOW INDEXES FROM foo_bar; Empty set (0.00 sec) 

Adding a primary key generates an index:

 mysql> ALTER TABLE foo_bar -> ADD PRIMARY KEY (`foo_id`, `bar_id`); Query OK, 0 rows affected (0.70 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEXES FROM foo_bar; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | foo_bar | 0 | PRIMARY | 1 | foo_id | A | 0 | NULL | NULL | | BTREE | | | foo_bar | 0 | PRIMARY | 2 | bar_id | A | 0 | NULL | NULL | | BTREE | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.02 sec) 

If I add the foreign key to foo_id , it reuses the primary key index, since this column is the first in the index:

 mysql> ALTER TABLE foo_bar -> ADD CONSTRAINT `foo_bar_fk1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`foo_id`) ON DELETE CASCADE ON UPDATE CASCADE; Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEXES FROM foo_bar; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | foo_bar | 0 | PRIMARY | 1 | foo_id | A | 0 | NULL | NULL | | BTREE | | | foo_bar | 0 | PRIMARY | 2 | bar_id | A | 0 | NULL | NULL | | BTREE | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) 

If I add a foreign key to bar_id , it will create an index, because no existing index can be reused:

 mysql> ALTER TABLE foo_bar -> ADD CONSTRAINT `foo_bar_fk2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`bar_id`) ON DELETE CASCADE ON UPDATE CASCADE; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEXES FROM foo_bar; +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | foo_bar | 0 | PRIMARY | 1 | foo_id | A | 0 | NULL | NULL | | BTREE | | | foo_bar | 0 | PRIMARY | 2 | bar_id | A | 0 | NULL | NULL | | BTREE | | | foo_bar | 1 | foo_bar_fk2 | 1 | bar_id | A | 0 | NULL | NULL | | BTREE | | +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.02 sec) 

One of our foreign keys uses a primary key index. This means that we cannot delete such an index!

 mysql> ALTER TABLE foo_bar -> DROP PRIMARY KEY; ERROR 1025 (HY000): Error on rename of '.\test\#sql-568_c7d' to '.\test\foo_bar' (errno: 150) 

If we do not create an index for the foreign key or throw the key itself:

 mysql> ALTER TABLE foo_bar -> DROP FOREIGN KEY `foo_bar_fk1`; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE foo_bar -> DROP PRIMARY KEY; Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0 

---

The conclusion is that MySQL automatically creates indexes when they are needed for functionality (but only if they are strictly necessary).

Answer 2

Foreign key indices are used to create referential constraints .

They allow you to define cascading delete statements, so deleting a row from the parent table will delete rows from the child tables. It can also be used to ensure that if you try to create a row in a child table, it can be associated with a parent (i.e., Childrow.ParentId )

Edit: Ah, sorry, I misunderstood. InnoDB automatically creates indexes for foreign keys. See does-mysql-index-foreign-key-columns-automatically

Answer 3

1) primary keys are automatically indexed in mysql. 2) See above, no need to do this 3) MyISAM does not support restrictions. (I think you stated it implicitly, so it is more for others to read it). Some graphical tools, such as the MySQL Workbench, do not generate a warning when trying to use foreign keys with MyISAM, but obviously do not create them.

Btw: what GUI are you using (on which OS)?

Good read: d ata warehousing whitepaper (contains a comparison of MyISAM and InnoDB tables)