Is the primary key automatically indexed in MySQL?

Question

Do you need to explicitly create an index, or is it implicit when defining the primary key? Is the answer the same for MyISAM and InnoDB?

Solution 1

The primary key is always indexed. This is the same for MyISAM and InnoDB, and is generally true for all storage engines that at all supports indices.

Solution 2

According to http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html it would appear that this is would be implicit

Solution 3

Even though this was asked in 2009 figured I'd post an actual reference to the MySQL documentation on primary keys. http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance

For MySQL 5.0 reference see: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.

Solution 4

The primary key is implicitly indexed for both MyISAM and InnoDB. You can verify this by using EXPLAIN on a query that makes use of the primary key.

Solution 5

You do not have to explicitly create an index for a primary key... it is done by default.

View more solutions

Comments

[Alex Miller]

I found that link by searching before I asked the question. But it doesn't seem to imply that or anything else much about this question to me.

[tim peterson]

If primary key is always indexed why do people when talking about database architecture/performance always advise SQL newcomers to "make sure their database is properly indexed"?

[Emil H]

@tim: They're telling people to make sure that any other columns used for filtering, grouping or sorting also have indices.

[JustJohn]

Don't forget joins too! Indexed join fields speed things up.

[George Hawkins]

The page linked to in this answer doesn't seem to say anything about whether a primary key is also an index. The pages linked to in the answer from @fyrye are more relevant.