MySQL KEY question

Question

CREATE TABLE `django_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content_type_id` int(11) NOT NULL,
  `object_pk` longtext NOT NULL,
  `site_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(50) NOT NULL,
  `user_email` varchar(75) NOT NULL,
  `user_url` varchar(200) NOT NULL,
  `comment` longtext NOT NULL,
  `submit_date` datetime NOT NULL,
  `ip_address` char(15) DEFAULT NULL,
  `is_public` tinyint(1) NOT NULL,
  `is_removed` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `django_comments_content_type_id` (`content_type_id`),
  KEY `django_comments_site_id` (`site_id`),
  KEY `django_comments_user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

I am in the middle of migrating a database so that it can be used with a Django application we're building. The above is generated by the built-in Django comments application. I believe I understand most of what's going on here except for the KEY parts (I understand the purpose of PRIMARY KEY).

Answer 1

KEYs are indices here.

Answer 2

The MySQL documentation for create table lists:

| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
  [index_option] ...

So a KEY is just another word for index.

Answer 3

A KEY entry specifies a column or columns to be indexed. Unlike the PRIMARY KEY, these are not guaranteed to be unique without additional modifiers.

Answer 4

The KEYs create indexes for these columns. Indexes are sorted and speed up searching. However, indexes need storage space and maintainace each time a row is inserted/deleted. For that reason you only index columns who are searched on a lot, or columns who need specific constraints. Note that regular indexes don't enforce the values to be unique, unlike the PRIMARY KEY.

In your table the indexed columns are (probably) used in joins with other tables. When joining tables, the DBMS has to lookup values in the other tables and this goes a lot faster if the columns are indexed.

(NOTE: since the table uses MyISAM, you won't have a "real" foreign-key ensuring integrity. For that you need to use InnoDB and setup relations)

Answer 5

KEY is synonym for index there

see http://dev.mysql.com/doc/refman/5.1/en/create-table.html