primary keys and an index

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

primary keys and an index

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply