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?

Hsu

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

Counsellor

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"? – Isaacisaacs

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

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

Answer 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

Nies

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. – Hsu

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. – Guttapercha

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

Reinhardt

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.

Hooked

Answer 5

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)

Heartwood

good way to check – Ways

Answer 6

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

Aeneous

Answer 7

Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by". You might be working on a more complex database, so it's good to remember a few simple rules.

• Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated.

• Indexes speed up where clauses and order by. Remember to think about HOW your data is going to be used when building your tables. There are a few other things to remember. If your table is very small, i.e., only a few employees, it's worse to use an index than to leave it out and just let it do a table scan.

• Indexes really only come in handy with tables that have a lot of rows.

• Another thing to remember, that is a con in the situation of our employee’s database, is that if the column is a variable length, indexes (as well as most of MySQL) perform much less efficiently.

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

Glazed

Answer 8

The primary key is always automatically indexed and unique. So, beware not to create redundant indexes.

For instance, if you created a table as such

CREATE TABLE mytable (foo INT NOT NULL PRIMARY KEY, bar INT NOT NULL, baz INT NOT NULL,
  UNIQUE(foo), INDEX(foo)) ENGINE=InnoDB;

because you want to index the primary key and enforce an uniqueness constraint on it, you'd actually end up creating three indexes on foo!

Excaudate

Answer 9

Yes, One can think of a primary key column as any other indexed column, with the constraints of primary key brings with it.

In most of the use cases we need both primary key, and indexed column/columns in a table, because our queries to table may filter rows based on column/columns which is not primary key, in that case we usually index those column/columns as well.

Rayford