Page 1 of 1

primary keys and an index

Posted: Mon May 31, 2004 5:52 pm
by davidklonski
Hello MySQL gurus

Assuming I create the following table:

CREATE TABLE table (
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Column1 INT UNSIGNED NOT NULL,
Column2 VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

Now ID is the primary key of the table which means it has a unique index on it (right?)

will adding the following have any additional effect?
CREATE index_name ON table(ID);

Or is such an index automatically created for all the primary keys in a table?

Also, putting storage issues aside, what would be the disadvantages to creating an index for every column that I use in a join, (in order to speed things up)?

thanks for the help

Posted: Mon May 31, 2004 9:58 pm
by JAM
a) No. The primary key is allready indexed.
b) Speed. Updating and/or Inserting data into a table where every field is indexed will take longer and longer each record you add to the table.