8

I have a table in mySQL where the 'id' column is the PRIMARY KEY:

CREATE TABLE `USERS` (
  `ID` mediumint(9) NOT NULL auto_increment,
  .....
  PRIMARY KEY  (`ID`),
  KEY `id_index` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=267 DEFAULT CHARSET=latin1;

I've also added an index as follows:

CREATE INDEX id_index ON USERS (id);

Did I need to do this? Or is the primary key automatically indexed?

The end aim is here is to speed up queries which join on the id column of table USERS.

Thanks

Tenakha
  • 183
  • 1
  • 2
  • 7
  • Possible duplicate of [Indexes on primary and foreign keys](http://stackoverflow.com/questions/3529161/indexes-on-primary-and-foreign-keys). I did some tests about that. – Álvaro González Feb 13 '13 at 10:39

3 Answers3

18

No, you don't need do this.

Primary key is automatically indexed. What you need is to index column that is foreign key in other table.

veljasije
  • 6,722
  • 12
  • 48
  • 79
  • 3
    MySQL will create indexes automatically [even for foreign keys](http://stackoverflow.com/questions/3529161/indexes-on-primary-and-foreign-keys). – Álvaro González Feb 13 '13 at 10:38
4

You don't need to add additional index to PK.

Using Primary Keys

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
3

By default Primary key is working like INDEX KEY. you need not to create this to index key.

Nilesh Gupta
  • 367
  • 1
  • 2