Is the index on `parent` necessary here?

Question

Create Table: CREATE TABLE `category` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parent` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `parent_idx` (`parent`),
  CONSTRAINT `category_parent_category_id` FOREIGN KEY (`parent`) REFERENCES `category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

I'm not sure whether a foreign key will imply an index?

EDIT

I don't see the supposed index :

mysql> show index from category;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| category |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| category |          0 | name       |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| category |          1 | parent_idx |            1 | parent      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.02 sec)

Answer 1

In SQL Server: Foreign keys do not automatically create indexes. You will need to explicitly create an index where you want one. This is done because you don't necessarily want to index every FK, as it will add overhead to inserts.

Answer 2

Yes, you are correct.

KEYparent_idx(parent) is redundant index.

MySQL will automactially created an index for FOREIGN KEY contraint.

From the MySQL manual:

InnoDB creates an index for the foreign key, it uses index_name for the index name.

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Answer 3

I'm not sure whether a foreign key will imply an index?

"InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan... Such an index is created on the referencing table automatically if it does not exist." (Source)

Therefore KEY parent_idx (parent) is redundant.

On the other hand note that if you were to add a foreign key constraint using the ALTER TABLE syntax, you would have to explicitly create the required indexes first.