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?

Answer 1

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

Answer 2

I guess this is the answer

mysql> create table test(id int primary key, s varchar(20));
Query OK, 0 rows affected (0.06 sec)

mysql> show indexes from test \G
*************************** 1. row ***************************
        Table: test
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

Answer 3

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.

Answer 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.

Answer 5

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.

Answer 6

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