Indexes on primary and foreign keys

Question

I have a database created with a GUI tool and I've noticed what appears to be an inconsistent use of KEY (aka INDEX) definitions:

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:

1. Is it necessary to explicitly define indexes for primary and foreign keys?
2. If it's not, do you actually get two indexes (and less performance)?
3. Is it different in InnoDB and MyISAM (foreign keys apart)?

Answer 1

The foreign key indexes are used to create referential constraints.

They allow you to define the cascade delete clauses, such that deleting a row from the parent table will delete rows from child tables. It can also be used to ensure that if you try to create a row in the child table, it can be linked to a parent (ie the Childrow.ParentId is valid)

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

Answer 2

1) primary keys are automatically indexed in mysql. 2) See above, not necessary to do it 3) MyISAM does not support constraints. (I think you stated that implicitly, so this is more for others for read over it). Some graphical tools like MySQL Workbench do not cause alerts when you try to use foreign keys with MyISAM, but obviously do not create them.

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

A good read: data warehousing whitepaper (contains a comparison of MyISAM and InnoDB tables)

Answer 3

I've been doing some experiments on what you told me and I'd thought I'd share it as 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;

So far, no indexes exists:

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 a foreign key on foo_id it reuses the primary key index since that column is the first one 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 on bar_id, it creates 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 is using the primary key index. That means that we cannot remove such 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)

Unless we create an index for the foreign key or we drop 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 creates indexes automatically when they're required for a functionality (but only if they are strictly necessary).