MySQL: create indexes, foreign keys & constraints

This page describes how to create MySQL indexes on table fields. We compare default indexes, unique indexes, foreign keys based on default indexes, and foreign keys based on unique indexes and constraints.

Create tables

First create some sample tables and data like this:
MySQL: create tables for joins with random rows

The "create index" command

To create an index:

  mysql> create index index_bar_id on foos(bar_id);
  Query OK, 1000000 rows affected (9.15 sec)
  Records: 1000000  Duplicates: 0  Warnings: 0

The "drop index" command

To drop an index:

  mysql> drop index index_bar_id on foos;
  Query OK, 1000000 rows affected (4.17 sec)
  Records: 1000000  Duplicates: 0  Warnings: 0

The "create unique index" command

To create a unique index:

  mysql> create unique index index_bar_id on foos(bar_id);
  Query OK, 1000000 rows affected (28.83 sec)
  Records: 1000000  Duplicates: 0  Warnings: 0

The "foreign key" command

To create a foreign key on an InnoDB table:

  mysql> alter table foos add foreign key (bar_id) references bars (id);
  Query OK, 1000000 rows affected (9.04 sec)
  Records: 1000000  Duplicates: 0  Warnings: 0

The "foreign key" and unique constraint

To create a foreign key based on a unique constraint, you first create a unique index, then create the key:

  mysql> create unique index index_bar_id on foos(bar_id);
  ...
  mysql> alter table foos add constraint index_bar_id foreign key (bar_id) references bars (id);
  Query OK, 1000000 rows affected (21.97 sec)

What's Next?

blog comments powered by Disqus